Forum Discussion
replace the first values in a sequence with zeros
Hello,
I'm trying to create a sequence with a variable length of values that, as I drag it to the right, only the latest values are included, and the first values are replaced with zeros.
In other words, I only want the last 30 values in the sequence, but I need the sequence to get one value longer as it's dragged into the next cell.
I'm finding this hard to explain, so I've attached my spreadsheet. Basically, I need the zeros in the lowest rows to be built into the formula above.
Thanks,
Derek
3 Replies
DerekRolston In C4, try this one:
=IF(C$1>$B4,"",AND($B4>=C$1,$B4<=C$1+$C$2-1)*C$1)Copy it down and across.
Thank you, that works perfectly in the example I provided.
However, ideally, I'd like these values as a series (or sequence/range?) instead of individual cell values. The reason for this is I need to be able to pick out every Nth value from the series using in this equation:
=SUMPRODUCT(--(MOD(COLUMN($C1:C1)-COLUMN(C1),C3)=0),$C1:C1)
where C3 = the Nth value
and my original equation (except with zeros for the first values)
=IF(COLUMN(C1)-COLUMN($C1)+1>$C$2,OFFSET(C1,0,-$C$2+1),$C1):C1
would replace (COLUMN($C1:1)
(updated spreadsheet attached)
Each column is a month and I need this for a 10-year projection, so it would be possible to just have a data tab with your equation dynamically filling individual cells then reference them in the equation above, however, I'm hoping to have everything I need to be built into a single equation on a single tab.
Thank you for your help with this!
DerekRolston I see your formula in row 5 (individual cell values) and see what it does. But I don't see the link to matrix below (rows 7:46). Sorry!
