cumulative sum formula repeat every nth row help needed

[bmaness]

Good evening forum

new to the forum with intermediate excel skills.

Just hoping someone can help with a formula to work with excel 2016

i want to essentially repeat a cumulative sum formula every 78 rows. ie cumulative sum rows 1-78 and then start over the next 78 lines.


Is this possible?

Thanks in advance

attachment example posted

[MarvinP]

Hi bmaness and welcome to the forum,

Something like:

Please Login or Register  to view this content.

might be what you want in cell L79. See the attached.
Only show Sum on Mod 79.xlsx

[bmaness]

thank you

not sure i follow.

as shown in example i need a cumulative sum on every row as shown (example lines J2-J79)

i have 100k lines of data. i need the formula to repeat and cumulative sum every line but starting over every 78 lines (as shown here daily in column A)

hope this makes sense.

[MarvinP]

Hi,

We just have different ideas on what "A Cumulative Sum" is. Can you explain? I thought you wanted the sum of the yellow column down to the 78th row and 78 more rows want the sum of the last 78 yellow values. I guess "Cumulative Sum" means something different to you?

[bmaness]

so as shown in example:

J2 =SUM($I$2:I2)/SUM($G$2:G2) or just the first row

J3 =SUM($I$2:I3)/SUM($G$2:G3) sums first and second

J4 sums lines 1-3 and so on

so the goal is to have this formula repeat starting at line 80-157 (yhe next 78 rows

and so on

repeats every day as shown in column A

hope this helps

[MarvinP]

OK - I got it after looking a second time. You need to use Indirect and build a text formula like:

Please Login or Register  to view this content.

See the column I called VWAP2 in the attached.
Cumulative Sum a second try.xlsx

One of the smart Guru's could most likely use an Offset formula that would be easier than the above, but I think mine works for your answer.

[bmaness]

THANK YOU!!!!

i consider you "One of the smart Guru's " i spent hours hand changing the data every 78 rows. so much more time efficient.

i have a follow up question about charting a line on a OHLC chart that has stumped me too

if your interested i can give you a example from the data set.

[Bo_Ry]

Please try at J2

=SUM(INDEX(I$2:I2,FLOOR(ROWS(I$2:I2)-1,78)+1):I2)/SUM(INDEX(G$2:G2,FLOOR(ROWS(G$2:G2)-1,78)+1):G2)

[bmaness]

THANK YOU!!
saved me literally 40-50 hours of time

i have a follow up question about charting a line on top of a OHLC chart that has stumped me too

if your interested i can give you a example from the data set.

[AliGW]

Please open a new thread for this new and separate query.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.