Increment by 1 every 12 cell values

[naina]

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?
Thanks for your help
Naina

 

[pecoflyer]

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)

 

[Hui]

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]

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]

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