multilple nested if statements in one cell

R

Rick

Hi:
I have a problem that needs to be resovled. This statement give me 0%:

=IF(OR($B5="", $F5="Medicare", $G5="", $G5="A", $G5="P", $G5="R",
SUM($I5-$J5)=0),"","F1")
IF($G5="M","T2","F2")
IF(OR($F5="BMP", $F5="CBC", $F5="RVP", $F5="UA"),1,"F3")
IF(OR(YEAR($B5)=$AU$3, AND($A5>=Activity!$AR$563, $H5="D"),
AND($A5>=Activity!$AI$563,
$H5="S")),0.8,"")
IF(OR(AND(YEAR($B5)=$AU$3, RollUp!$AU$3="Y", $H5="D"),
AND(YEAR($B5)=$AU$3, RollUp!$AV$3="Y", $H5="S"),
AND(Activity!$AU$563>"", $A5>=Activity!$AX$563, $H5="D"),
AND(Activity!$AL$563>"", $A5>=Activity!$AO$563, $H5="S")),1,"F5")
IF(OR(AND($A5<Activity!$AU$563, $H5="D"), AND($A5<Activity!$AL$563,
$H5="S")),"",0.8)

And this statement for some reason works, I think:

=IF(OR($G5="", $G5="A",
$G5="P", $G5="R", $P5=0),"","F1")
IF($H5="D","T2","F2")
IF($G5="M","T3","F3")
IF(YEAR($B5)=Activity!$BA$3,0.8,"F4")
IF(RollUp!$AR$7="","","F5")
IF($A5<RollUp!$AR$7,"",0.8)

IF(YEAR($B5)=Activity!$BA$3,1,"F7")
IF(RollUp!$AP$7="","","F8")
IF($A5<RollUp!$AP$7,"","F9")
IF(RollUp!$AQ$7="",0.8,1)

IF($G5="M","T11","F11")
IF(YEAR($B5)=Activity!$BA$3,0.8,"F12")
IF(RollUp!$AR$8="","","F13")
IF($A5<RollUp!$AR$8,"",0.8)

IF(YEAR($B5)=Activity!$BA$3,1,"F15")
IF(RollUp!$AP$8="","","F16")
IF($A5<RollUp!$AP$7,"","F17")
IF(RollUp!$AQ$8="",0.8,1)

As you can see the second statement has 18 lines. and multiple complete
nested if's.
 
T

Tom Ogilvy

=IF(OR($B5="", $F5="Medicare", $G5="", $G5="A", $G5="P",
$G5="R",SUM($I5-$J5)=0),"","F1")

is a complete formula. Everything after it has no meaning. Perhaps you
need to put up a valid formula if you want help.
 
B

Blue Hornet

Tom said:
=IF(OR($B5="", $F5="Medicare", $G5="", $G5="A", $G5="P",
$G5="R",SUM($I5-$J5)=0),"","F1")

is a complete formula. Everything after it has no meaning. Perhaps you
need to put up a valid formula if you want help.



One thing that can help to keep the formula manageable (readable) is to
enter some of the multiple choices for a single cell in an array, such
as:

=IF( OR( $F5 = "Medicare", $G5 = {"","A","P","R"}, SUM( $I5 - $J5) =
0),"","F1")

(for example)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top