Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering years and months
HI I'm hoping that someone can help with a formula. In column B , I have a formula which numbers to a maximum of 300. Each number represents a monthly payment. In column A I'd like to signal the start of each new year with 'Year 1" , "Year 2" , "Year 3" etc as the numbers in column B reach 12 , 24 , 36 , 48 and so on. If the cells in column B are zero in quantity , then so should be the cells in A. So : A B Year1 1 2 3 4 5 6 7 8 9 10 11 12 Year 2 13 14 15 16 17 18 19 20 21 22 23 24 Year 3 25 Hope someone can help. Best Wishes |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering years and months
Try in cell A1
=IF(MOD(B1,12)=1,"year "&1+INT(B1/12),"") On 25-10-2009 Colin Hayes wrote: HI I'm hoping that someone can help with a formula. In column B , I have a formula which numbers to a maximum of 300. Each number represents a monthly payment. In column A I'd like to signal the start of each new year with 'Year 1" , "Year 2" , "Year 3" etc as the numbers in column B reach 12 , 24 , 36 , 48 and so on. If the cells in column B are zero in quantity , then so should be the cells in A. So : A B Year1 1 2 3 4 5 6 7 8 9 10 11 12 Year 2 13 14 15 16 17 18 19 20 21 22 23 24 Year 3 25 Hope someone can help. Best Wishes -- Kind regards Lars Klintholm |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering years and months
Colin Hayes wrote:
HI I'm hoping that someone can help with a formula. In column B , I have a formula which numbers to a maximum of 300. Each number represents a monthly payment. In column A I'd like to signal the start of each new year with 'Year 1" , "Year 2" , "Year 3" etc as the numbers in column B reach 12 , 24 , 36 , 48 and so on. If the cells in column B are zero in quantity , then so should be the cells in A. So : A B Year1 1 2 3 4 5 6 7 8 9 10 11 12 Year 2 13 Assuming you are starting in row 1, A1 is =IF(MOD(B1,12)=1,"Year "&1+INT(B1/12),"") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering years and months
In article , Lars Klintholm
writes Try in cell A1 =IF(MOD(B1,12)=1,"year "&1+INT(B1/12),"") Hi OK thanks for that It's working fine where the formula in Column B has returned a number. Where the formula is not returning a number (and the cell is therefore blank) , the formula above gives #Value! errors. Is there a way around this? Effectively the formula in A needs to show blank where the equivalent cell in B is blank. Thanks again On 25-10-2009 Colin Hayes wrote: HI I'm hoping that someone can help with a formula. In column B , I have a formula which numbers to a maximum of 300. Each number represents a monthly payment. In column A I'd like to signal the start of each new year with 'Year 1" , "Year 2" , "Year 3" etc as the numbers in column B reach 12 , 24 , 36 , 48 and so on. If the cells in column B are zero in quantity , then so should be the cells in A. So : A B Year1 1 2 3 4 5 6 7 8 9 10 11 12 Year 2 13 14 15 16 17 18 19 20 21 22 23 24 Year 3 25 Hope someone can help. Best Wishes |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering years and months
On 25-10-2009 Colin Hayes wrote:
Try in cell A1 =IF(MOD(B1,12)=1,"year "&1+INT(B1/12),"") Hi OK thanks for that It's working fine where the formula in Column B has returned a number. Where the formula is not returning a number (and the cell is therefore blank) , the formula above gives #Value! errors. Is there a way around this? Effectively the formula in A needs to show blank where the equivalent cell in B is blank. Try this one: =IF(ISNUMBER(B1),IF(MOD(B1,12)=1,"year "&1+INT(B1/12),""),"") -- Kind regards Lars Klintholm |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbering years and months
In article , Lars Klintholm
writes Try this one: =IF(ISNUMBER(B1),IF(MOD(B1,12)=1,"year "&1+INT(B1/12),""),"") -- Kind regards Lars Klintholm Hi Lars Yes , that's perfect. Exactly the solution. Thanks Lars. Impressive...! Best Wishes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count months between years | Excel Worksheet Functions | |||
SUM Days (to 30), Months (to 12) and Years | Excel Worksheet Functions | |||
converting months to years and months??? | Excel Discussion (Misc queries) | |||
Converting months to years | Excel Worksheet Functions | |||
years and months | Excel Worksheet Functions |