Excel Nested If Displays Muliple "False" Conditions 1

[killer23]

All -

I am attmepting to sort out a nested formula that will display only one result - i.e. 1st, 2nd, 3rd, Fail. "D2" referes to age while "P2" refers to the raw score of my event. When I run the follwing formula I get something like "FalseFalseFalse1"; when I only want the "1" to be displayed.

=IF(AND(D2>46),IF(P2>150,"1",IF(P2>100,"2",IF(P2>65,"3",IF(P2<64,"Fail","0")))))&IF(AND(D2>40),IF(P2>175,"1",IF(P2>125,"2",IF(P2>88,"3",IF(P2<87,"Fail","0")))))&IF(AND(D2>=27),IF(P2>200,"1",IF(P2>150,"2",IF(P2>110,"3",IF(P2<109,"Fail","0")))))&IF(AND(D2<=26),IF(P2>225,"1",IF(P2>200,"2",IF(P2>135,"3",IF(P2<134,"Fail","0")))))

Thanks in advance for your help.

 

[bluedragon2]

For the first part of the formula before the first &, you can have:

=IF(D2>46,IF(P2>150,"1",IF(P2>100,"2",IF(P2>65,"3","Fail"))),"0")

But, the way you have the formula set up with the &'s, you are going to get multiple 1's.2's,3's,0's, and Fails. Is that what you need, if not, what is the breakdown of tour criteria.

[Blue]Blue[/Blue] [Dragon]

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

 

[killer23]

The breakdown should be based on age then score; ie. if over 46 years then criteria one, if between 40 - 45 yrs then criteria two and so on.

 

[bluedragon2]

=IF(D2>=46,IF(P2>=150,"1",IF(P2>=100,"2",IF(P2>=65,"3","Fail"))),IF(D2>=45,IF(P2>=175,"1",IF(P2>=125,"2",IF(P2>=88,"3","Fail"))),IF(D2>=27,IF(P2>=200,"1",IF(P2>=150,"2",IF(P2>=110,"3","Fail"))),IF(P2>=225,"1",IF(P2>=200,"2",IF(P2>=135,"3","Fail"))))))

But, you could have a little lookup table and pull your numbers based on it.

[Blue]Blue[/Blue] [Dragon]

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

 

[killer23]

That did the trick - thanks Blue Dragon for the solution.

 

[AnotherHiggins]

killer23,

just an FYI: The customary way to show thanks here at Tek-Tips is to "award a star" to helpful post(s). You can click on the link under any of Blue's posts that says
[tab] Thank bluedragon2
[tab]for this valuable post!

That also lets future visitors looking for a solution to a similar problem know that this thread contained a helpful post.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.