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

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

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),"")

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

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

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