automatic age range formula
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! |
automatic age range formula
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! |
automatic age range formula
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! |
automatic age range formula
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! |
automatic age range formula
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! |
automatic age range formula
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! |
automatic age range formula
=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! |
automatic age range formula
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! |
All times are GMT +1. The time now is 08:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com