automatic age range formula

[Guest]

In cell A1, I have a formula giving me a number 31.
In cell B1, I need to enter text saying "30-35"

This formula needs to change based on the content of cell A1. For example,
if A1 said 20, then B1 should read "15-20", if A1 said 67, then B1 should
read ">65" and so on.

I know this is possible but I've forgotten the formula. Thanks all!

 

[Guest]

try entering the following formula in B1

=ROUNDDOWN(A1,-1)&"-"&ROUNDDOWN(A1,-1)+5

HTH

 

[Peo Sjoblom]

You can use VLOOKUP, first you need to use the upper boundary for each value


0
6
11
16
21
26
31
36


and in the adjacent column you put the values

0-5
6-10
11-15
and so on

Make sure you format the column with the 0-5 etc as text or you will get
some dates, then a formula might look like

=IF(A1="","",VLOOKUP(A1,{0,"0-5";6,"6-10";11,"11-15";16,"16-20";21,"21-25";26,"26-30";31,"31-35";36,"35-40"},2))

where A1 is the cell where you type the value, note that you can't have

15-20 you need to use 16-20, 21-25 etc,


--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

Try that with 29


--


Regards,


Peo Sjoblom

 

[Guest]

You could use a LOOKUP function. Something like:

=LOOKUP(A1,{0,15,21,26,31,36,66},{"0-14","15-20","21-25","26-30","31-35","36-65",">65"})

Change the data to match your actual age ranges.

HTH,
Elkar

 

[Guest]

oops - sorry guys was in a hurry when i did that!

 

[David Biddulph]

=IF(A1>65,">65",FLOOR(A1-1^-10,5)&"-"&CEILING(A1,5))

 

[David Biddulph]

Correction:
=IF(A1>65,">65",FLOOR(A1-1E-10,5)&"-"&CEILING(A1,5))