# Assign cell value based upon number range

#### Jimbo_jones

##### New Member
Hi all,

My apologies, this should be a very easy one to resolve but unfortunately my mind is not working properly today!

Basically I have a column which contains a range of numbers between 1 and 100 (some of these may contain decimals).

What I would like to do is to assign a value in the adjacent column which relates to a specified range:

i.e. in it's simplest terms, if cell value A1 is BETWEEN 1 and 10 then assign a 1 to cell A2. If value is between 11 and 20 then assign a 2, and so on...

Can I use some kind of 'or' statement to resolve this or would something like range lookup be more suitable?

Thanks
James

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
James

In B1 (copied down): =CEILING(A1/10,1)
If your relationship between columns A and B is different, a different type of formula may be required. If this is not what you want, post back with more details.
Mr Excel.xls
ABCD
111
225.33
3324
4202
524.083
6354
7152
8
Assign Value

Or

=ROUNDUP(A1/10,0)

Richard

Hi, James,

if this example is "real" (close to the facts)
you can use CEILING
else some lookup will work
A     B
1 10    1
2 15,15 2
3 20    2
4 41    5
5 5,86  1

sheet1

[Table-It] version 05 by Erik Van Geit
Code:
``````RANGE FORMULA (1st cell)
B1:B5 =CEILING(A1/10,1)

[Table-It] version 05 by Erik Van Geit``````
kind regards,
Erik

Wouldn't:

=INT((A1-1)/10)+1

do it?

Thank you for your speedy replies. All very valid suggestions!

My actual scenario wasn't quite as simplistic as the one above, but you've managed to help me solve it successfully.

Many thanks again,
James

Replies
2
Views
157
Replies
5
Views
328
Replies
4
Views
401
Replies
2
Views
235
Replies
6
Views
276

1,219,574
Messages
6,149,084
Members
450,855
Latest member
onecodevee01

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