Problem with nested IF and OR formula?

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

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 action 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")))),"Missing Value Goes Here"))

or


=IF(C21<$C$62,
"Absent",IF(OR(B23="Absent",C21<$C$62),"Missing Value Goes 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"))))))

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"))

Pete_UK wrote:

>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

Thanks for feeding back.

Pete