Problems with IF, COUNTIF, arguments, etc.

Hello:

I am trying to determine that if a range of cells are marked with an "X", then cells containing "Pass" (A4), Pass with Opportunity for Improvement (A6)or "Fail" (A8) will be marked with an "X". I have 3 columns that are selectable for Pass, Fail, and Non-Applicable. There are a total of thirteen questions that this evaluation form asks, and the evaluator must check one box. In order to be considered a Pass, there must be more 12 or more "X"'s marked in the Pass column. A Pass with Opportunity for Improvement would be equal to 11 marked in the Pass column, and a Failure would be 10 or less showing up in the Fail column. If there is a check in the non-applicable section, this has to be figured into thye pass fail as well. A Pass would be 85% or higher, and failure anything less than this. I will attach the file for review. I would very much appreciate help on this problem first, and then there is one other element to the sheet that plays in at the end. Thank you very much.

I am unsure how to attach the file in the proper format, ie .zip!! It is an Excel 2007 file. HELP!!!

save your sheet in 2003 format and then select the file, right click and > send to compressed folder.

You can then attach it - use the manage attachments button on the reply page.

Ed

Here is the File. Thanks EdMac.

Bemidji

I have made some amendments to the layout - let me know if you can live with them.

I like what you did here (formula & functionality), this is close to the end result I desire. However, this form will be used to do work center evaluations, and then the data will be entered into the computer, and electronic copies distributed.

Can we keep the "X" concept, or at least evaluate it both ways??

Thank you.

Any other Thoughts, comments???

I think EdMac has some great ideas, please look at both attachments and see what you can figure out. Any help would be greatly appreciated!!

Is Excel capable of doing what I am asking???

Hi,

If you want to use 'X' you can - the disadvantage is that if you have a system where people can enter something in two boxes they will and that may mess up the results.

Perhaps if you could say exactly what the result is that you want and why the suggestion doesn't meet this it would help

Ed

Thanks for your reply EdMac.

I have four columns on the worksheet (Pass, Fail, N/A, & Repeat Find). When conducting an evaluation, this form will be printed out and manually filled in. There are 13 questions that should have an "X" filled in for the response (Pass, Fail, N/A). If a Fail is recorded, and the failure is a Repeat Find (on a previous evaluation), the Repeat Find Cell would be recorded with an "X" as well.

In the "summation" cells (ie Pass, Pass w/ OFI, & Fail) A4, A6, A8, I want to be able to sum the responses with certain conditions. Let's forget the A6 cell for now (perhaps we can come back to that).

A4 would display an "X" if the number of "X"'s recorded in column is greater than or equal to 12, otherwise left blank. A8 (Fail Cell) would display an "X" if the number of "X"'s recorded in the Fail column (Column B) is greater than or equal to 2, otherwise left blank. If an N/A column is marked with an "X", then this would need to be fisgured into the formula as well. For instance, if there were 10 "X" in the Pass column, 2 "X" in the Fail column, and 1 "X" in the N/A column, then the Pass cell should show an "X" (Cell A4). If there were 6 X's in the Pass column, 1 X in the Fail column, and 6 N/A's, the Pass Cell (A4) should show an X). Passing is 92.3% (ex. 12/13).

If Column A "X" = 13 = Pass

If Column A "X" = 12 = Pass

If Column A "X" = 11 = Fail

If N/A is selected, then this muddy's up things, then perhaps the percentage would have to come into play???

13/13 = 100% = Pass

12/13 = 92.3% = Pass

11/13 = 84.6% = Fail

10/13 = 76.9% = Fail etc. etc. etc.


Let's say 11 "X" in column A, 1 "X" in column B, and 1 "X" in column C

11/12 = 91.66% (percentage would then be based on 12 total questions, and no longer 13 because 1 is not applicable).

9 "X" in column A, 1 "X" in column B, 3 "X" in column C

9/10 = 90.0% = Fail (total applicable = 10, and is less than 92.3%)

Hopefully this explains it well enough. Pleaes contact me with any questions. Thank you so much...

Hello EdMac:

Sorry about the inconsistencies...I should have reviewed closer prior to posting...

QUOTE:

If there were 6 X's in the Pass column, 1 X in the Fail column, and 6 N/A's, the Pass Cell (A4) should show an X). Passing is 92.3% (ex. 12/13).

This should actually be 6 Passes, 1 Fail, Total of 7 (6/7) or 85.7%, result Fail.

I agree, 2 or more Fails definitely would fail (in all circumstances).

Let's plan on 92.3% or less a failure. If I need to adjust later, I will have to change the contents of the formula slightly. Thanks again.

Bemidji,

Have a look at the attached - I have done some conditional formatting to reduce the possibility of input error.

Get back to me if there is anything else you need.

Ed

I think you nailed it. Thank you so much. I will dive into the spreadsheet in greater detail, but at first glance and use, I think what you put together is perfect. Thank you so much for your help.

thanks for that - feel free to repost if you need anything else.

Ed