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





"tjb" wrote in message
...
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!