replace the first values in a sequence with zeros

%3CLINGO-SUB%20id%3D%22lingo-sub-2116685%22%20slang%3D%22en-US%22%3Ereplace%20the%20first%20values%20in%20a%20sequence%20with%20zeros%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116685%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20a%20sequence%20with%20a%20variable%20length%20of%20values%20that%2C%20as%20I%20drag%20it%20to%20the%20right%2C%20only%20the%20latest%20values%20are%20included%2C%20and%20the%20first%20values%20are%20replaced%20with%20zeros.%3C%2FP%3E%3CP%3EIn%20other%20words%2C%20I%20only%20want%20the%20last%2030%20values%20in%20the%20sequence%2C%20but%20I%20need%20the%20sequence%20to%20get%20one%20value%20longer%20as%20it's%20dragged%20into%20the%20next%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20finding%20this%20hard%20to%20explain%2C%20so%20I've%20attached%20my%20spreadsheet.%20Basically%2C%20I%20need%20the%20zeros%20in%20the%20lowest%20rows%20to%20be%20built%20into%20the%20formula%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EDerek%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2116685%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117017%22%20slang%3D%22en-US%22%3ERe%3A%20replace%20the%20first%20values%20in%20a%20sequence%20with%20zeros%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117017%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F958026%22%20target%3D%22_blank%22%3E%40DerekRolston%3C%2FA%3E%26nbsp%3BI%20see%20your%20formula%20in%20row%205%20(individual%20cell%20values)%20and%20see%20what%20it%20does.%20But%20I%20don't%20see%20the%20link%20to%20matrix%20below%20(rows%207%3A46).%20Sorry!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2117002%22%20slang%3D%22en-US%22%3ERe%3A%20replace%20the%20first%20values%20in%20a%20sequence%20with%20zeros%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117002%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20that%20works%20perfectly%20in%20the%20example%20I%20provided.%3C%2FP%3E%3CP%3EHowever%2C%20ideally%2C%20I'd%20like%20these%20values%20as%20a%20series%20(or%20sequence%2Frange%3F)%20instead%20of%20individual%20cell%20values.%20The%20reason%20for%20this%20is%20I%20need%20to%20be%20able%20to%20pick%20out%20every%20Nth%20value%20from%20the%20series%20using%20in%20this%20equation%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(--(MOD(COLUMN(%24C1%3AC1)-COLUMN(C1)%2CC3)%3D0)%2C%24C1%3AC1)%3C%2FP%3E%3CP%3Ewhere%20C3%20%3D%20the%20Nth%20value%3C%2FP%3E%3CP%3Eand%20my%20original%20equation%20(except%20with%20zeros%20for%20the%20first%20values)%3C%2FP%3E%3CP%3E%3DIF(COLUMN(C1)-COLUMN(%24C1)%2B1%26gt%3B%24C%242%2COFFSET(C1%2C0%2C-%24C%242%2B1)%2C%24C1)%3AC1%3C%2FP%3E%3CP%3Ewould%20replace%20(COLUMN(%24C1%3A1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(updated%20spreadsheet%20attached)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20column%20is%20a%20month%20and%20I%20need%20this%20for%20a%2010-year%20projection%2C%20so%20it%20would%20be%20possible%20to%20just%20have%20a%20data%20tab%20with%20your%20equation%20dynamically%20filling%20individual%20cells%20then%20reference%20them%20in%20the%20equation%20above%2C%20however%2C%20I'm%20hoping%20to%20have%20everything%20I%20need%20to%20be%20built%20into%20a%20single%20equation%20on%20a%20single%20tab.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%20with%20this!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116791%22%20slang%3D%22en-US%22%3ERe%3A%20replace%20the%20first%20values%20in%20a%20sequence%20with%20zeros%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116791%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F958026%22%20target%3D%22_blank%22%3E%40DerekRolston%3C%2FA%3E%26nbsp%3BIn%20C4%2C%20try%20this%20one%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(C%241%26gt%3B%24B4%2C%22%22%2CAND(%24B4%26gt%3B%3DC%241%2C%24B4%26lt%3B%3DC%241%2B%24C%242-1)*C%241)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ECopy%20it%20down%20and%20across.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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. 

@Riny_van_Eekelen 

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!