IF Formula - What am i doing wrong?

[DoosanRuss]

Hi All
(Excel 2003 user (XP))

Any help gratefully received, I've written the following formula to select answers from a selection of checkboxes, based on cells B56,57 & 58

=IF(AND(B56,B57,B58),"OPT1",
IF(AND(NOT(B56),B57,B58),"OPT2",
IF(AND(NOT(B56),NOT(B57),B58),"OPT3",
IF(AND(NOT(B56),B57,NOT(B58)),"OPT2",
IF(AND(B56,NOT(B57),NOT(B58)),"OPT1",
IF(AND(B56,NOT(B57),B58),"OPT1",

I want to add:
IF(AND(B56,B57,NOT(B58)),"OPT1",
after the IF(AND(B56,NOT(B57),B58),"OPT1", comma
but for some reason (B58) is not being recognised a cell reference with the 'NOT' in front of it.

TICK PRICING OPTIONS"))))))

Thansk Russ

 

[Gerald Higgins]

Based on what you've said, I THINK you can achieve the same results with this formula

=IF(B56,"Opt 1",IF(B57,"Opt 2","Opt 3"))

 

[lenze]

Hi and welcome to the board!!!
You can simplify your formula somewhat

=IF(OR(AND(B56,B57,B58),AND(B56,NOT(B57),B58),AND(B56,B57,NOT(B58))),"OPT1",IF(OR(AND(NOT(B56),B57,B58),AND(NOT(B56),B57,NOT(B56))),"OPT2","OPT3"))

lenze

 

[DoosanRuss]

thanks Lenze

Looks good, however for OPT1 i have 4 possible scenarios, i've just tried to add the missing option AND(B56,NOT(B57),NOT(B58)) into the first OR argument and it doesnt recognise it, can i only have 3 arguments under OR?

 

[Gerald Higgins]

DoosanRuss - did you look at my formula ? Does it work for you ?

 

[lenze]

Did you try Gerald's formula??
It looks like it will work.
It will, however(as will mine) show "OPT3" when all three are unchecked. You can fix with another if

=IF(B56,"Opt 1",IF(B57,"Opt 2",IF(B58,"Opt 3","")))

lenze

 

[DoosanRuss]

it does, just tried it this second, Sorry Gerald, i must of missed it in my haste, more fool me!
fingers crossed i won't be asking anymore questions

Thanks for all your help

Always learning (if even very slowly)

Russ

 

[DoosanRuss]

And cheers Lenze

I've added in a some Text to read WARNING that'll change with Conditional formatting when all 3 are unchecked.

maybe i went into today thinking it was harder than actually needed to be

Thanks for showing me the light!