debugging for formula with many IFs and ORs...

[Armitage2k]

Hi,

I have following conditions for a formula:

This survey analyizes 2 questions and deduces a recognition score which is calculated as following
If E8 is "-" or "do not remember", do nothing

else, if E6 is either GOLD or PLTN
then count range E8:E9 for "YES" and if counted amount is more than "1", return value "2"
if counted amount is less than "1", return "0"

Else, if E6 is "CLUB"
then check if E8 is equal to "Yes" then return 1
if cell E8 equal to "No" return return "0"

My formula is this:

Please Login or Register  to view this content.

now the problem is that if the cell value in E6 is "CLUB" and "E8" is "Yes", the formula return "False" which actually should be returned as "1".

I believe that I am missing an IF definition somewhere but just cant figure it out... any help would be much appreciated.

Thanks,
A2k
Recognition2.xlsx

[Fotis1991]

Try in E10 and copy across.

=IF(AND(E6="Club",E8="yes"),1,IF(E8="No",0,IF(OR(E8="-",E8="Do not remember"),"",IF(OR(E6="PLTN",E6="GOLD"),IF(COUNTIF(E8:E9,"Yes")>=1,2),IF(C6="CLUB",COUNTIF(E8,"Yes"),0)))))

[benishiryo]

hi A2k, your formula seems messy & i dont quite understand. but just to solve your problem, you mention if E6 is "Club" but put in C6:

Please Login or Register  to view this content.

Edit: if i understood correctly, then maybe even:
=IF(OR(E8="-",E8="Do not remember"),"",IF(AND(OR(E6="PLTN",E6="GOLD"),COUNTIF(E8:E9,"Yes")),2,IF(AND(E6="CLUB",E8="Yes"),1,0)))

[icestationzbra]

slightly shorter:

Please Login or Register  to view this content.

1100

[Armitage2k]

Hey guys, thanks for the help.

I tried all formulas and so far and I can only confirm that Benishiryo's edited formula and Fotis1991 contribution are doing the trick

Please Login or Register  to view this content.

as for icestationzbra, I do like your short and compact formula alot, but it doesnt work if E6 is PLTN and the counted YES in E8:E9 are only 1. I adjusted corrected the formula to

Please Login or Register  to view this content.

and it works now.

Thanks alot for all the suggestions guys, much appreciate it!
A2k