Explaination of this excel formula and its functions?

[pigment01]

=SUM(B3:INDEX(B:B,MAX(ROW($A$3),COUNT(B$3:B3)-Home!$C$3/15+ROW($A$3))))


The formulas in Col J:Q determine the number of blocks that need summing i.e 6 blocks for 90, 7 for 105, 8 for 120 and so on. This is in the "time data" sheet.


I am having an hard time understanding this formula in this model.

[mcmahobt]

Break it down function by function.

Please Login or Register  to view this content.

What this is saying is take the max, either the value of row A3 (3), or the value of COUNT(B$3:B3)-Home!$C$3/15+ROW($A$3) (1 - (Home!$C$3)/15) + 3). The COUNT() function will increase by a value of 1 as long as there is a numerical value in a cell.

The value that the MAX() function returns is then used in the INDEX() function, and returns the ordinal value in column B:B that matches the value from the MAX() function. This ordinal value is used as a range in the SUM function, starting from B3 and summing to the return of the INDEX() function.