Hi everyone.
I have a number spreadsheet with 20 columns /100 rows of numbers. I need to get the sum of the highest 4 CONSECUTIVE numbers of each columns. I have found two formula to get the sum:
=SUMPRODUCT(MAX(D3:D95+D4:D96+D5:D97+D6:D98))
or
=MAX(INDEX(D3:D95+D4:D96+D5:D97+D6:D98,0))
1). Both of them work but can someone explain the difference between them?
2). I would also like to define or highlight the cells of the four consecutive numbers that totaled the sum from. Is there a way to tell which four numbers the formula calculated? I thought maybe using Conditional Formatting but I haven't been able to figure out how.
Any advise or guidance would be greatly appreciated.
Thanks in advance,
Frankie
I have a number spreadsheet with 20 columns /100 rows of numbers. I need to get the sum of the highest 4 CONSECUTIVE numbers of each columns. I have found two formula to get the sum:
=SUMPRODUCT(MAX(D3:D95+D4:D96+D5:D97+D6:D98))
or
=MAX(INDEX(D3:D95+D4:D96+D5:D97+D6:D98,0))
1). Both of them work but can someone explain the difference between them?
2). I would also like to define or highlight the cells of the four consecutive numbers that totaled the sum from. Is there a way to tell which four numbers the formula calculated? I thought maybe using Conditional Formatting but I haven't been able to figure out how.
Any advise or guidance would be greatly appreciated.
Thanks in advance,
Frankie
