Nested if

[bjd via OfficeKB.com]

I have put together an if statement:

=IF(AND(U8="08",M8/AB8<$O$5),"Problem",IF(AND(B8="Base",M8/AB8<$O$4),
"Problem",IF(AND(B8="RDC",M8/AB8<$O$4),"Problem",IF(AND(B8="Branch",M8/AB8<$O
$3),"Problem","OK"))))

the statement works but not as intended. The only
issue is that I want the first condition as sort of
an overall condition, that if met, none of the other
conditions need to be cycled through. If not
met, then cycle through the other functions.

The logic is to look at the value in U8 and if it
equals "08" and it is less than 31 (m8/ab8), display
"Problem". If not equal to "08", then apply the other
IF(And functions, that look at conditions in a different
column.

The issue is that I have not figured a way
keep it from cycling through the other conditions. I've
tried some "OR" conditions but I can't get it to work.

Any suggestions would be appreciated.

 

[Bob Phillips]

I guess you mean

=IF(U8="08",IF(M8/AB8<$O$5,"Problem",IF(AND(B8="Base",M8/AB8<$O$4),"Problem"
,IF(AND(B8="RDC",M8/AB8<$O$4),"Problem",IF(AND(B8="Branch",M8/AB8<$O$3),"Pro
blem","OK")))),"")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[CLR]

I didn't check the whole formula, but the front end that you're concerned
about seems to work as you wish it. The problem you're experiencing may be
because your formula requires "08" (a TEXT value) and you may be trying to
get it to work with a number 8............so it just blows by to the next
section.

Vaya con Dios
Chuck, CABGx3

 

[bjd via OfficeKB.com]

Bob,

Thanks for the response. The formula that you sent returns everything as a
problem. If I use the other feedback, where it was suggested that I change
the text 08 to a number, then everything returns as blank, as a result of ""
at the end. I stumped on this and what I may have to do is to add another
column to do the validation that I want to do...not the best answer but a
quick one.

Anyway, thanks for taking the time to look at this.

Brian

 

[bjd via OfficeKB.com]

Chuck,

I did change this but the same problem is occuring. Most of the data that I
am screening, the values do not pass the first set of functions, U8=8 and
M8/AB8<$O$5. However, the formula is still using the latter functions and
returning that the values are OK, even though they are not.

Anyway, thanks for your review.

Brian