= if when dealing with 10 different parameters

[Tobyriley]

I cant seem to figure this out.....I am trying to get cell d2 to calculate the following: (Cell b2 is a temperature entry) If cell b2 is between 0-5, then i want d2 to populate the number in cell j2, if b2 is in between 5-10, then I want d2 to populate the number in j3. This goes on for 10 different possibilities from j2 thru j12.

this by itself works: =IF(AND(B2>=0,B2<5),J2,0)

this doesn't work: =IF(AND(B2>=0,B2<5),J2,0) =IF(AND(B2>=5,B2<10),J3,0)

I am sure the solution is simple. However I am just brain cramping trying to figure this out

[BadlySpelledBuoy]

You can nest the IFs like this

Please Login or Register  to view this content.

But I suspect a lookup would be a better idea rather than nesting 10 IFs.
Exactly which kind of lookup you use will depend on your data. Maybe post a sample workbook if you'd like help with that.

BSB

[Pete_UK]

Rather than have multiple nested IF statements, you can use this in D2:

=INDEX($J$2:$J$12,MATCH(B2,{0,5,10,15,20,25,30,35,40,45,50}))

Hope this helps.

Pete

[Phuocam]

Try ...

=IF(AND(B2>=0,B2<5),J2,IF(AND(B2>=5,B2<10),J3,0))

[Tobyriley]

Winner winner chicken dinner! Thank you for your help Pete it works perfectly!!