IF question - Idiot needs help

Steve Swift

Board Regular
Joined
Jan 18, 2004
Messages
187
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

New Member
Joined
Jul 13, 2005
Messages
35
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

Board Regular
Joined
Jan 18, 2004
Messages
187
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

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
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

New Member
Joined
Jul 13, 2005
Messages
35
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

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
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
 

Forum statistics

Threads
1,078,246
Messages
5,339,068
Members
399,276
Latest member
Donjayok

Some videos you may like

This Week's Hot Topics

Top