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!

try entering the following formula in B1

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

HTH

"tjb" wrote:

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!

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!

Try that with 29


--


Regards,


Peo Sjoblom




"pablo bellissimo" wrote in
message ...
try entering the following formula in B1

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

HTH

"tjb" wrote:

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!

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


"tjb" wrote:

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!

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

"Elkar" wrote:

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


"tjb" wrote:

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!

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

"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!

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=IF(A165,"65",FLOOR(A1-1^-10,5)&"-"&CEILING(A1,5))
--
David Biddulph

"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!