# IF question - Idiot needs help

#### Steve Swift

##### Board Regular
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

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### asgourlay

##### New Member
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
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
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
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
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

##### Board Regular
Thanks very much to you both,

Truly appreciated,

Steve

Replies
5
Views
108
Replies
1
Views
139
Replies
7
Views
309
Replies
3
Views
1K
Replies
8
Views
579

1,196,007
Messages
6,012,825
Members
441,731
Latest member
jonceramic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back