How do I keep a formula from changing when I advance the data?

%3CLINGO-SUB%20id%3D%22lingo-sub-3516677%22%20slang%3D%22en-US%22%3EHow%20do%20I%20keep%20a%20formula%20from%20changing%20when%20I%20advance%20the%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3516677%22%20slang%3D%22en-US%22%3E%3CP%3EI%20keep%20daily%20financial%20data%20in%20a%20horizontal%20table.%20Every%20day%20I%20insert%20a%20column%20on%20the%20left%20and%20add%20that%20day's%20prices.%20I%20want%20to%20have%20a%20spreadsheet%20that%20will%20calculate%20the%20average%20of%20the%20last%26nbsp%3B%205%20days.%20But%20if%20I%20make%20a%20formula%20AVERAGE(E5%3AI5)%2C%20when%20I%20insert%20the%20new%20data%20the%20formula%20will%20automatically%20change%20to%20AVERAGE(F5%3AJ5).%20I%20want%20to%20keep%20it%20averaging%20E5%3AI5.%20Even%20if%20I%20do%20AVERAGE(%24E%245%3A%24I%245)%20it%20doesn't%20help.%20I%20tried%20using%20a%20range%20but%20that%20automatically%20adjusts%2C%20too.%20Is%20there%20anything%20I%20can%20do%20to%20keep%20averaging%20the%20same%205%20columns%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3516677%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-3517135%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20keep%20a%20formula%20from%20changing%20when%20I%20advance%20the%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3517135%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F578347%22%20target%3D%22_blank%22%3E%40Marshall1954%3C%2FA%3E%26nbsp%3BLet's%20say%20you%20want%20to%20returns%20the%20last%205-day%20average%20in%20D5%2C%20try%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DAVERAGE(OFFSET(D5%2C0%2C1%2C1%2C5))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3517435%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20keep%20a%20formula%20from%20changing%20when%20I%20advance%20the%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3517435%22%20slang%3D%22en-US%22%3EGood%20thinking%20and%20nice%20formula%20for%20finding%20the%20average%20but%20no%2C%20that%20changes%20too%20-%20the%20D5%20just%20changes%20to%20E5%20when%20I%20input%20the%20new%20data.%20Even%20if%20I%20add%20the%20%24%20sign%20in%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3517773%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20keep%20a%20formula%20from%20changing%20when%20I%20advance%20the%20data%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3517773%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F578347%22%20target%3D%22_blank%22%3E%40Marshall1954%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends%20where%20do%20you%20insert%20new%20column%2C%20after%20the%20column%20D%20or%20not.%20You%20may%20anchor%20to%20column%20A%26nbsp%3B%20slightly%20modifying%20the%20formula%20-%20if%20only%20new%20column%20is%20not%20before%20A.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I keep daily financial data in a horizontal table. Every day I insert a column on the left and add that day's prices. I want to have a spreadsheet that will calculate the average of the last  5 days. But if I make a formula AVERAGE(E5:I5), when I insert the new data the formula will automatically change to AVERAGE(F5:J5). I want to keep it averaging E5:I5. Even if I do AVERAGE($E$5:$I$5) it doesn't help. I tried using a range but that automatically adjusts, too. Is there anything I can do to keep averaging the same 5 columns? 

3 Replies

@Marshall1954 Let's say you want to returns the last 5-day average in D5, try this:

=AVERAGE(OFFSET(D5,0,1,1,5))

 

Good thinking and nice formula for finding the average but no, that changes too - the D5 just changes to E5 when I input the new data. Even if I add the $ sign in it.

@Marshall1954 

It depends where do you insert new column, after the column D or not. You may anchor to column A  slightly modifying the formula - if only new column is not before A.