Formula Help

Barb

Hello,

Can someone please help me with this formula. I don't know what I'm doing wrong. I have 3 auto fail items and it seems that only the PCI one works. If either Solved, Protected, or PCI are scored No, the end score needs to be 0%. Everything is either scored, Yes, No, or Not Applicable. 

I have 8 columns:

A, B, C, D, E, Solved, Protected, PCI

I'm using:

=IF([Solved]1 = "No", 0) + IF([Protected]1 = "No", 0) + IF([PCI]1 = "No", 0), (((COUNTIF([A]1:[PCI]1, "Yes") * 1 + (COUNTIF([A]1:[PCI]1, "Not Applicable") * 1))) / 8))

 

Thank you,

Barb

J. Craig Williams

Barb,

 

Your formula is imbalanced (has non matching counts of "(" and ")"

 

I assume your formula is actually located in a 9th column (otherwise the reference to [A]1:[PCI]1 would lead to a circular reference.

 

Breaking it down:

 

=IF([Solved]1 = "No", 0)

results in 0 when Solved is "No" but gives a blank when it is not "No".

Same for the next two parts

 

+ IF([Protected]1 = "No", 0)

+ IF([PCI]1 = "No", 0)

 

And then that comma looks out of place

 

, (((COUNTIF([A]1:[PCI]1, "Yes") * 1 + (COUNTIF([A]1:[PCI]1, "Not Applicable") * 1))) / 8))

 

and the last two ")" are what is causing the imbalance.

 

.....

So, assuming A,B,C,D,E hold values,

if ANY of the other three are "No" then 0

otherwise, count the "Yes" and "Not Applicables" - equally weighted.

 

Let's start with the first part, this 

 

=IF(OR(Solved1 = "No", Protected1 = "No", PCI1 = "No"), 0, "do something")

 

will check for any "No" in those three columns and return 0 if it finds one (or more)

Otherwise it will print "do something"

 

Replace the "do something" with what you want like 

 

5 - COUNTIF(A1:E1,"No")

 

will assume that any non-No is a valid input (including blanks) and since there are 5 columns just subtract the number we don't want to count.

 

I'm really not clear one what you are trying to do but maybe this will help you figure it out.

 

Craig

 

Barb

Craig you saved my day! I tried the below and it worked.

 

=IF(OR([Solved]1 = "No", [Protected]1 = "No", [PCI]1 = "No"), 0, (((COUNTIF([A]1:[PCI]1, "Yes") * 1 + (COUNTIF([A]1:[PCI]1, "Not Applicable") * 1))) / 8))

 

Appreciate your quick response!

Barb

J. Craig Williams

I do that a lot, mostly at night dressed as a superhero(tm).

Sometimes here too. 

Glad I can help.

 

Craig