Assign cell value based upon number range

Jimbo_jones

New Member
Joined
Apr 5, 2005
Messages
13
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?

Any advice greatly appreciated.
Thanks
James
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Jimbo_jones

New Member
Joined
Apr 5, 2005
Messages
13
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
 

Forum statistics

Threads
1,136,354
Messages
5,675,292
Members
419,559
Latest member
BraytonM

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top