Mar 01 2019 01:35 PM
I am trying to create an auto error message to flag up an incomplete entry. The formula I've written seems logical to me but Excel wont accept it and gives me an error message.
=IF(OR(Part1!B5>0,Part1!J19>0,Part1!B6>0)),IF(AND(Part1!B5>0,Part1!J19>0,Part1!B6>0)),Part1!B5,"ERROR"," "
if anyone could point out my error id be grateful.
Mar 01 2019 03:54 PM - edited Mar 01 2019 03:55 PM
SolutionNested IF looks like
=IF(Cond1, IF(Cond2, A, B), C)
Your formula is
=IF(Cond1), IF(Cond2), A, B, C
Other words, that's not a formula at all.
Mar 01 2019 09:28 PM
Mar 02 2019 12:51 AM
As I understood the logic is
if <at least one entered> then if <all entered> then B5 else "ERROR" else <empty string>
which is directly translated into nested IF
Mar 02 2019 01:22 AM
Thanks Sergei I re wrote it as you said and its working
=IF(OR(Part1!B5>0,Part1!J19>0,Part1!B6>0),IF(AND(Part1!B5>0,Part1!J19>0,Part1!B6>0),Part1!B5,"ERROR")," ")
I'll try to remember to this in future
Mar 02 2019 03:00 AM
You are welcome, glad to help. In general it's always better to start not from formula but from formulating the logic in plain language. If only you didn't do the similar few dozens of times.
Mar 01 2019 03:54 PM - edited Mar 01 2019 03:55 PM
SolutionNested IF looks like
=IF(Cond1, IF(Cond2, A, B), C)
Your formula is
=IF(Cond1), IF(Cond2), A, B, C
Other words, that's not a formula at all.