• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Too many IF statements?

P

phillygirl777

Member
Hi everyone! I am using Excel 2007. I have two working formulas:


=IF($T5="",VLOOKUP($C5,'Plan Data'!$C:$AJ,21+MONTH(Y$3),FALSE),(IF(AND($U5=2012,$V5>MONTH(Y$3)),0,(IF(AND($H5="US",MONTH(Y$3)<4),+$M5*Y$2/$AJ$2,IF(AND($H5="US",MONTH(Y$3)>=4),+$P5*Y$2/$AJ$2,IF(AND($H5<>"US",MONTH(Y$3)<4),+$M5/12,IF(AND($H5<>"US",MONTH(Y$3)>=4),+$P5/12))))))))


=IF(AND($H5="US",MONTH(Y$3)<4),+$M5*Y$2/$AJ$2*AQ5,IF(AND($H5="US",MONTH(Y$3)>=4),+$P5*Y$2/$AJ$2*AQ5,IF(AND($H5<>"US",MONTH(Y$3)<4),+$M5/12*AQ5,IF(AND($H5<>"US",MONTH(Y$3)>=4),+$P5/12*AQ5))))


Now I need to put them together and make them work. The first one should work when the following is true:

IF(Y$4="Forecast")


The second one should work in this case:

IF(Y$4="Actual")


There is no ELSE for Forecast and Actual because this field is pre-populated by an error-proof formula that doesn't allow for anything else.


Any suggestions??


Thanks!
 
Hi ,


You say that Y4 is populated by an error-proof formula which allows for only 2 values , "Forecast" and "Actual" ; this is ideal for use as an IF ... ELSE , where the IF condition can be "Forecast" and the Else condition will obviously be "Actual". You can also have it the other way around.


The formula will be :


=IF($Y4="Forecast",IF($T5="",VLOOKUP($C5,$C:$AJ,21+MONTH(Y$3),FALSE),(IF(AND($U5=2012,$V5>MONTH(Y$3)),0,(IF(AND($H5="US",MONTH(Y$3)<4),+$M5*Y$2/$AJ$2,IF(AND($H5="US",MONTH(Y$3)>=4),+$P5*Y$2/$AJ$2,IF(AND($H5<>"US",MONTH(Y$3)<4),+$M5/12,IF(AND($H5<>"US",MONTH(Y$3)>=4),+$P5/12)))))))),IF(AND($H5="US",MONTH(Y$3)<4),+$M5*Y$2/$AJ$2*AQ5,IF(AND($H5="US",MONTH(Y$3)>=4),+$P5*Y$2/$AJ$2*AQ5,IF(AND($H5<>"US",MONTH(Y$3)<4),+$M5/12*AQ5,IF(AND($H5<>"US",MONTH(Y$3)>=4),+$P5/12*AQ5)))))


Narayan
 
Rearranging your formulas so that rather than a series of IF's, they become parallel path (is H5 = "US"? If so, take one path, else take other path)


=IF(Y$4="Forecast",IF($T5="", VLOOKUP($C5,'Plan Data'!$C:$AJ,21+MONTH(Y$3),FALSE), IF(AND($U5=2012,$V5>MONTH(Y$3)), 0, IF($H5="US", IF(MONTH(Y$3)<4, $M5*Y$2/$AJ$2, $P5*Y$2/$AJ$2), IF(MONTH(Y$3)<4, $M5/12, $P5/12)))), IF($H5="US", IF(MONTH(Y$3)<4, $M5*Y$2/$AJ$2*AQ5, $P5*Y$2/$AJ$2*AQ5), IF(MONTH(Y$3)<4, $M5/12*AQ5, $P5/12*AQ5)))
 
Narayan,


Thanks for the input, but your suggestion gives me a Circular formula warning.


Luke,


Your formula works wonderfully! Thanks a lot!


Thank you both for your suggestions. Your help is greatly appreciated.
 
You must log in or register to reply here.
Back
Top