New Contributor

# 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

# Re: replace the first values in a sequence with zeros

@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.

# Re: replace the first values in a sequence with zeros

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)