Nested IF Statements

[asoto6]

Need help with this formula. I would like to add another condition to this formula (see below). The condition is: =IF(F6<=2.0049,25, that is if a certain number is greater than or equal to 2.0049, allocate 25 points, if not then continue with formula below.

=IF('CPM 3 Yr. Compare'!M8<='CPM 3 Yr. Compare'!L8,(LOOKUP('Cons Comments 2007 vs 2008'!K9,{-1000,-30.1,-30,-20.1,-20,-10.1,-10,0,0.1,5,5.1,10,10.1,15,15.1,20,20.1,25,25.1,1000},{25,25,24,24,23,23,20,20,0,0,0,0,0,0,0,0,0,0,0})),IF('CPM 3 Yr. Compare'!M8>'CPM 3 Yr. Compare'!L8,(LOOKUP('Cons Comments 2007 vs 2008'!K9,{-1000,-30.1,-30,-20.1,-20,-10.1,-10,0,0.1,5,5.1,10,10.1,15,15.1,20,20.1,25,25.1,1000},{25,25,24,24,23,23,20,20,0,0,0,0,0,0,0,0,0,0,0}))))

 

[bluedragon2]

=IF(F6<=2.0049,25,'CPM 3 Yr. Compare'!M8<='CPM 3 Yr. Compare'!L8,(LOOKUP('Cons Comments 2007 vs 2008'!K9,{-1000,-30.1,-30,-20.1,-20,-10.1,-10,0,0.1,5,5.1,10,10.1,15,15.1,20,20.1,25,25.1,1000},{25,25,24,24,23,23,20,20,0,0,0,0,0,0,0,0,0,0,0})),IF('CPM 3 Yr. Compare'!M8>'CPM 3 Yr. Compare'!L8,(LOOKUP('Cons Comments 2007 vs 2008'!K9,{-1000,-30.1,-30,-20.1,-20,-10.1,-10,0,0.1,5,5.1,10,10.1,15,15.1,20,20.1,25,25.1,1000},{25,25,24,24,23,23,20,20,0,0,0,0,0,0,0,0,0,0,0})))))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[asoto6]

bluedragon2:
Thanks for the tip, but it does not quite work. I see/understand your logic (I had tried this before) but yet it does not return with correct result.

 

[yogia]

Hi asoto6:

Assuming that your current IF formula is working correctly add to it the following part shown in red colored font ...

=[red]IF(F6<=2.0049,25,[black]your IF formula here[/black])[/red]


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[asoto6]

Yogia:
Thank you, but it won't do it. I have tried. Any other thoughts?

 

[asoto6]

Thank you all for your help. I finally figured it out!
This is my final product/formula:

=IF((F7<=2.0049),25,IF(('CPM 3 Yr. Compare'!M6<='CPM 3 Yr. Compare'!L6),(LOOKUP('Cons Comments 2007 vs 2008'!K7,{-1000,-30.1,-30,-20.1,-20,-10.1,-10,0,0.1,5,5.1,10,10.1,15,15.1,20,20.1,25,25.1,1000},{25,25,24,24,23,23,20,20,0,0,0,0,0,0,0,0,0,0,0})),IF(('CPM 3 Yr. Compare'!M6>'CPM 3 Yr. Compare'!L6),(LOOKUP('Cons Comments 2007 vs 2008'!K7,{-1000,-30.1,-30,-20.1,-20,-10.1,-10,0,0.1,5,5.1,10,10.1,15,15.1,20,20.1,25,25.1,1000},{25,25,24,24,23,23,20,20,0,0,0,0,0,0,0,0,0,0,0})),"")))

 

[bluedragon2]

Yeah forgot to copy the other IF

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[yogia]

Yogia:
Thank you, but it won't do it. I have tried. Any other thoughts?

Hi asoto6:

You did not say how the formulation I suggested ...

=IF(F6<=2.0049,25,your IF formula here)

would not do.

In your solution you posted that you said does work, is not different from my proposed solution except for some reason you have decided to change the reference to cell F7 instead of F6.

In any event, the key thing is that you have got things working and that is what matters.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com