# Increment by 1 every 12 cell values

#### naina

##### New Member
Hello there-

attached is the spreadsheet with example:

example1: Oct-21 is TRUE (Cell L2). What I want to write formula is starting from Oct 21 I want to do an increment of 1 in groups of 12
What I want is in row 4 ( Oct21-Sep22 value=1, Oct 22-Sep 23 value=2, and so on

example 2: if Mar 21=TRUE then I want Mar21-Feb 22 value=1, Mar22-Feb22 value=2 and so on...

I ca d this suing ROW() but only if the data was transposed. How can I accomplish this?
Naina

#### Attachments

• 9.5 KB Views: 9

#### pecoflyer

##### Active Member
Hi
solution attached for your example 2 starting in col C ( a bit cumbersome I'm afraid) and dragged right
You'll have to adapt ranges as needed
=IF(COLUMN()<COLUMN(INDEX(\$A8:\$AH8
,MATCH(TRUE;\$A9:\$AH9,0))),"";CEILING(COLUMN()-COLUMN(INDEX(\$A8:\$AH8,MATCH(TRUE,\$A9:\$AH9,0)))+1,12)/12)

#### Attachments

• 10.3 KB Views: 2

#### Hui

##### Excel Ninja
Staff member
using EX2 as an example
in C11 =IF(MOD(COLUMN()-2-MATCH(TRUE,\$C9:\$AZ9,0),12)=0,MAX(\$A11:B11)+1,B11)
copy across

• naina

#### naina

##### New Member
Hi
solution attached for your example 2 starting in col C ( a bit cumbersome I'm afraid) and dragged right
You'll have to adapt ranges as needed
=IF(COLUMN()<COLUMN(INDEX(\$A8:\$AH8
,MATCH(TRUE;\$A9:\$AH9,0))),"";CEILING(COLUMN()-COLUMN(INDEX(\$A8:\$AH8,MATCH(TRUE,\$A9:\$AH9,0)))+1,12)/12)

Thank you so much for taking out time and helping with this. this works like a charm.
Best regards
Naina

#### p45cal

##### Well-Known Member
Anther to test:
in C4 copied across:
=IFERROR(DATEDIF(INDEX(\$C\$1:\$AZ\$1,MATCH(TRUE,\$C\$2:\$AZ\$2,0)),C1,"Y")+1,"")

and similar for C11:
=IFERROR(DATEDIF(INDEX(\$C\$8:\$AZ\$8,MATCH(TRUE,\$C\$9:\$AZ\$9,0)),C8,"Y")+1,"")