• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Attachments

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

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

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