Assign cell value based upon number range

[Jimbo_jones]

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

 

[Peter_SSs]

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
	A	B	C	D
1	1	1
2	25.3	3
3	32	4
4	20	2
5	24.08	3
6	35	4
7	15	2
8
Assign Value

 

[Richard Schollar]

Or

=ROUNDUP(A1/10,0)

Richard

 

[erik.van.geit]

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

RANGE FORMULA (1st cell)
B1:B5 =CEILING(A1/10,1)

[Table-It] version 05 by Erik Van Geit

kind regards,
Erik

 

[Andrew Poulsom]

Wouldn't:

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

do it?

 

[Jimbo_jones]

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