SOLVED
Home

strugling with a nested formula

whitemetal2100
New Contributor

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.

5 Replies
Solution

Nested 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.

 

Perhaps, this formula returns your desired result:
=IF(AND(Part1!B5>0,Part1!B6>0,Part1!J19>0),
Part1!B5,
"ERROR")

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

 

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 

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies