help with if then function for ranges

[janastead]

L5 needs to display 80 if k5 equals .1 to 4, 60 if k5 equals 4.1 to 8, 40 if it equals 8.1 to 12, 30 if 12.1 to 16, 20 if 16 +. i am new to excel, and i have excel 2000. it is driving me insane please help

[DGagnon]

try this

=if(and(K5>=.1,K5<=4),80,if(and(K5>=4.1,K5<=8),60,if(and(K5>=8.1,K5<=12),40,if(and(K5>=12.1,K5<=16),30,20))))

or

=if(K5>16,20,if(k5>12.1,30,if(K5>8.1,40,if(k5>4.1,60,if(K5>.1,80,"Too Small")))))

[Special-K]

in L5

=LOOKUP(K5,{0.1,4.1,8.1,12.1,16.1},{80,60,40,30,20})

[Special-K]

A shorter IF function can be obtained by testing for highest numbers first, ie

=IF(K5>16.1,20,IF(K5>12.1,30,IF(K5>8.1,40,IF(K5>4,1,60,80))))

[davesexcel]

Give this a go
=IF(K5<=4,80,IF(AND(K5>4,K5<=8),60,IF(AND(K5>8,K5<=12),40,IF(AND(K5>12,K5<=16),30,20))))

[janastead]

grrrrrrrrrrrrr...........frustrating!!!

[Special-K]

Did you use the LOOKUP solution?

[janastead]

this one only returned 80

[janastead]

this one gave an error message for too many arguments

[janastead]

this only returns 80 as well ( i really apreciate the help tho!

[davesexcel]

this one gave an error message for too many arguments

Who are you responding to?

[janastead]

i used lookup and it yeilded 80 only as well... any other ideas?

[DGagnon]

could you share a sample book?

[janastead]

excel error.JPG

here u go

[DGagnon]

try this out

=IF(K5>16.1,20,IF(K5>12.1,30,IF(K5>8.1,40,IF(K5>4.1,60,80))))

[janastead]

excel error 2.JPG

heres another try. it is showing 20 but should be showing 80. it only changes to 80 if it is above 10

[DGagnon]

looking at you data again, you are working with %'s not whole numbers, 4% is not equal to 4, but .04

try this.

=IF(K5>.161,20,IF(K5>.121,30,IF(K5>.081,40,IF(K5>.041,60,80))))

[janastead]

excel error 3.JPG

another try that ohnly yeilds 80

[janastead]

Registered User
--------------------------------------------------------------------------------
Join Date: 02-23-2012Location: Ontario, Canada MS-Off Ver: Excel 2003, 2007Posts: 648Infractions:0/1 (2)




Re: help with if then function for ranges



looking at you data again, you are working with %'s not whole numbers, 4% is not equal to 4, but .04

try this.

=IF(K5>.161,20,IF(K5>.121,30,IF(K5>.081,40,IF(K5>.041,60,80))))

THANK YOU SO VERY MUCH! THAT WAS THE PROBLEM :
))