Home

How to run a 24-cell block formula

%3CLINGO-SUB%20id%3D%22lingo-sub-784016%22%20slang%3D%22en-US%22%3EHow%20to%20run%20a%2024-cell%20block%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EGood%20morning%2C%20I%20have%20a%20small%20problem%20and%20I%20wanted%20to%20know%20if%20anyone%20can%20support%20me.%20What%20I%20need%20is%20for%20my%20Excel%20formula%20to%20take%2024%20cells%20to%20get%20an%20average%20and%20to%20drag%20it%20take%20the%20next%2024%20cells%2C%20that%20is%2C%20if%20I%20take%20from%20cells%201%20to%2024%20when%20dragging%20my%20formula%20take%20it%20from%20cell%2025%20to%2048.%20I%20would%20appreciate%20it%20Much%20your%20support.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-784016%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784269%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20run%20a%2024-cell%20block%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784269%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385785%22%20target%3D%22_blank%22%3E%40irvine93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20use%20below%20formula%2C%20it%20will%20give%20average%20of%20numbers%20with%20the%20gap%20of%2024%20rows%2C%20for%20example%201st%20it%20will%20give%20average%20of%201%20-%2024%20row%2C%20when%20you%20drag%20it%20will%20give%20average%20from%2025%20to%2048%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAVERAGE(OFFSET(%24C%245%3A%24C%24244%2C((ROW(C5)-ROW(%24C%245))*24)%2C0%2C1%2C1)%3AOFFSET(%24C%245%3A%24C%24244%2C((ROW(C5)-ROW(%24C%245))*24)%2C0%2C24%2C1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20file%20is%20also%20attached%20for%20your%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20will%20hep%20you%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-784387%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20run%20a%2024-cell%20block%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784387%22%20slang%3D%22en-US%22%3EAssuming%20your%20values%20are%20in%20Column%20A%2C%20a%20shorter%20non-volatile%20formula%2C%20starting%20in%20B1%2C%20is%3A%3CBR%20%2F%3E%3DAVERAGE(INDEX(A%3AA%2CROW()*24-24%2B1)%3A%3CBR%20%2F%3EINDEX(A%3AA%2CROW()*24))%3C%2FLINGO-BODY%3E
irvine93
Occasional Visitor

Good morning, I have a small problem and I wanted to know if anyone can support me. What I need is for my Excel formula to take 24 cells to get an average and to drag it take the next 24 cells, that is, if I take from cells 1 to 24 when dragging my formula take it from cell 25 to 48. I would appreciate it Much your support.

2 Replies

Hi @irvine93 

 

Please use below formula, it will give average of numbers with the gap of 24 rows, for example 1st it will give average of 1 - 24 row, when you drag it will give average from 25 to 48 and so on.

 

=AVERAGE(OFFSET($C$5:$C$244,((ROW(C5)-ROW($C$5))*24),0,1,1):OFFSET($C$5:$C$244,((ROW(C5)-ROW($C$5))*24),0,24,1))

 

Excel file is also attached for your reference.

 

Hope it will hep you

Thanks

Tauqeer 

Assuming your values are in Column A, a shorter non-volatile formula, starting in B1, is:
=AVERAGE(INDEX(A:A,ROW()*24-24+1):
INDEX(A:A,ROW()*24))
Related Conversations