Problem with nested IF and OR formula?

[Peter Frank]

Hi,

I'm having a problem with an Excel formula containing several IFs and
ORs.

The formula looks like this (the cell references are not important
here, you can simplify it if you like, it is about the structure of
this whole expression in which there must be an error that causes it
not to yield the results that I want):

In one line:
=IF(C21<$C$62,"Absent",IF(OR(B23="Absent",C21<$C$62),IF(OR(C21/B21<2,ABS(-1/(C21/B21))<2),"*",IF(OR(C21/B21<3,ABS(-1/(C21/B21))<3),"**",IF(OR(C21/B21<4,ABS(-1/(C21/B21))<4),"***",IF(OR(C21/B21>=4,ABS(-1/(C21/B21))>=4),"****","X"))))))

In a somewhat more structured format:
=IF(C21<$C$62,
"Absent",
IF(OR(B23="Absent",C21<$C$62),
IF(OR(C21/B21<2,ABS(-1/(C21/B21))<2),
"*",
IF(OR(C21/B21<3,ABS(-1/(C21/B21))<3),
"**",
IF(OR(C21/B21<4,ABS(-1/(C21/B21))<4),
"***",
IF(OR(C21/B21>=4,ABS(-1/(C21/B21))>=4),
"****",
"X"))))))


What I would like this formula to do is this:

If the first condition (here: C21<$C$62) is true, then I want "Absent"
to be displayed. This works.

If the subsequent OR condition (here: OR(B23="Absent",C21<$C$62) ) is
true, then I want 1, 2, 3, or 4 asterisks to be displayed depending on
the other four OR conditions. This also works.

If the condition OR(B23="Absent",C21<$C$62) is false, I want X to be
displayed (actually, X is another expression but for simplicity's sake
I just call it X here). However, this does not work. Instead, I get
FALSE instead of X.

I can't see the mistake. Could anyone help me make the formula do want
I want it to?

Regards,
Peter

 

[mudraker]

Frank

I am no formula expert but 2 things that stand out to me are

IF(OR(B23="Absent",C21<$C$6 2)
should probally be
IF(OR(B23="Absent",C21<$C$62)

and also with this part of the formula you list only one result action
IF(OR(B23="Absent",C21<$C$6 2), do another if statement, no actio
listed)


Try This

=IF(C21<$C$62,
"Absent",IF(OR(B23="Absent",C21<$C$62),IF(OR(C21/B21<2,ABS(-1/(C21/B21))<2),"*",IF(OR(C21/B21<3,ABS(-1/(C21/B21))<3),"**",IF(OR(C21/B21<4,ABS(-1/(C21/B21))<4),"***",IF(OR(C21/B21>=4,ABS(-1/(C21/B21))>=4),"****","X")))),"Missin
Value Goes Here"))

or


=IF(C21<$C$62,
"Absent",IF(OR(B23="Absent",C21<$C$62),"Missing Value Goe
Here",IF(OR(C21/B21<2,ABS(-1/(C21/B21))<2),"*",IF(OR(C21/B21<3,ABS(-1/(C21/B21))<3),"**",IF(OR(C21/B21<4,ABS(-1/(C21/B21))<4),"***",IF(OR(C21/B21>=4,ABS(-1/(C21/B21))>=4),"****","X")))))

 

[Pete_UK]

I think you have your teminating brackets in the wrong place for your
logic - it should be:

=IF(C21<$C$62,"Absent",
IF(OR(B23="Absent",C21<$C$62),
IF(OR(C21/B21<2,ABS(-1/(C21/B21))<2),"*",
IF(OR(C21/B21<3,ABS(-1/(C21/B21))<3),"**",
IF(OR(C21/B21<4,ABS(-1/(C21/B21))<4),"***",
IF(OR(C21/B21>=4,ABS(-1/(C21/B21))>=4),"****")))),
"X"))

 

[Peter Frank]

I think you have your teminating brackets in the wrong place for your
logic - it should be:

=IF(C21<$C$62,"Absent",
IF(OR(B23="Absent",C21<$C$62),
IF(OR(C21/B21<2,ABS(-1/(C21/B21))<2),"*",
IF(OR(C21/B21<3,ABS(-1/(C21/B21))<3),"**",
IF(OR(C21/B21<4,ABS(-1/(C21/B21))<4),"***",
IF(OR(C21/B21>=4,ABS(-1/(C21/B21))>=4),"****")))),
"X"))

Yep, that was it. Now the formula works correctly. Thanks.

Peter

 

[Pete_UK]

Thanks for feeding back.

Pete