IF question - Idiot needs help

[Steve Swift]

Hi,

Can someone help with this what I am sure is an easy problem?

I have cell A2 in which the user will type a number, as a result of the size of the number I need B2 to display a different number. I guess its an IF in B2 nested but can't figure it out. This is what I need

IF A2 is equal to or greater than zero and less than or equal to 999 "20"
IF A2 is equal to or greater than 1000 and less than or equal to 4999 "18"
IF A2 is equal to or greater than 5000 and less than or equal to 9999 "15"
IF A2 is equal to or greater than 10000 "12"

Hope that makes sense and that someone can help.

Thanks
Steve

 

[asgourlay]

Nested IF

This should help

Though if there are any non whole numbers or negatives it could be a problem ie 999.99 would not work, though you could tweak this.


=IF(AND($A2>=0,$A2<=999),20,IF(AND($A2>=1000,$A2<=4999),18,IF(AND($A2>=5000,$A2<=9999),15,IF($A2>=10000,12))))

R

 

[Steve Swift]

Wow,

Thanks for that incredibly speedy reply. Much appreciated.

One quick question, how can I make it so that 0 is in B2 if 0 is in A2.

Thanks again

Steve

 

[DominicB]

Good afternoon Steve Swift

This will work OK with decimal numbers as well as integers, however you have not specified what will happen with negative numebrs, in this case 0 will be returned, if you want to show something different, replace the last zero before the brackets with that digit.

=IF(A2>=10000,12,IF(A2>=5000,15,IF(A2>=1000,18,IF(A2>=0,20,0))))

HTH

DominicB

 

[asgourlay]

Bigger IF

Try this then

R

=IF($A2=0,0,IF(AND($A7>=0,$A7<=999),20,IF(AND($A7>=1000,$A7<=4999),18,IF(AND($A7>=5000,$A7<=9999),15,IF($A7>=10000,12)))))

 

[DominicB]

Good evening (again) Steve Swift

=IF(A2>=10000,12,IF(A2>=5000,15,IF(A2>=1000,18,IF(A2>0,20,0))))

will return zero if A2 is 0 or less.

HTH

DominicB

 

[Steve Swift]

Thanks very much to you both,

Truly appreciated,

Steve