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$6 2),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 (he C21<$C$62) is true, then I want "Absent"
to be displayed. This works.

If the subsequent OR condition (he 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

Problem with nested IF and OR formula?
 

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(C 21/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),"Missin g 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"))))))


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=523337

Problem with nested IF and OR formula?
 

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

Problem with nested IF and OR formula?
 

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

Problem with nested IF and OR formula?
 

Thanks for feeding back.

Pete