Minimum rolling total of a series of cash flows in one cell

[Billy8147]

I am trying to calculate the minimum rolling total of a series of cash flows without using multiple cells. For example, cells A3:A11 contain the following values [-1,-1,-4,2,1,-5,8,2,-1] and I would like to put a formula in cell A1 which would return the value -8.

Any ideas?

 

[tusharm]

What is a "minimum rolling total" and how is it -8 for your example?

I am trying to calculate the minimum rolling total of a series of cash flows without using multiple cells. For example, cells A3:A11 contain the following values [-1,-1,-4,2,1,-5,8,2,-1] and I would like to put a formula in cell A1 which would return the value -8.

Any ideas?

 

[Billy8147]

The "rolling total" for the series of values in my example would be -1, -2, -6, -4, -3, -8, 0, 2, 1.

The "minimum rolling total" would just be the min of those values (-8). The maximum rolling total would be 2.

 

[Ron Coderre]

With your values in A3:A11


This regular formula returns the MINIMUM cumulative total:

B1: =MIN(INDEX(SUBTOTAL(9,OFFSET(A3,,,ROW(A3:A11))),0))

This regular formula returns the MAXIMUM cumulative total:

C1: =MAX(INDEX(SUBTOTAL(9,OFFSET(A3,,,ROW(A3:A11))),0))

In your example, the MIN is: -8
The MAX is 2

Is that something you can work with?

 

[tusharm]

If you can put the result in some other column than A, i.e., reserve A for the data series. Then use =MIN(A:A) for the minimum.

If you must put the result in A1 and the rest of A is for the series, use =MIN(A3:A{lastrow}) where {lastrow} is 65536 or 1048576 (Excel 2007 or later).

The "rolling total" for the series of values in my example would be -1, -2, -6, -4, -3, -8, 0, 2, 1.

The "minimum rolling total" would just be the min of those values (-8). The maximum rolling total would be 2.

 

[Ron Coderre]

Hi, Tushar (you were missed in WA last week)

Unless I'm mistaken, I believe Col_A contains the actual values, not the cumulative totals. Hence, the complication. The single formula needs to calculate all cumulative totals: Sum(A2), SUM(A2:A3), SUM(A2:A4), etc
and return the max or min.

 

[tusharm]

Hi Ron,

Yeah, I had to cancel at the last minute.

Oh, I see. Billy8147 is adding up the values and then computing the min and max of those values. My bad.

Hi, Tushar (you were missed in WA last week)

Unless I'm mistaken, I believe Col_A contains the actual values, not the cumulative totals. Hence, the complication. The single formula needs to calculate all cumulative totals: Sum(A2), SUM(A2:A3), SUM(A2:A4), etc
and return the max or min.

 

[Billy8147]

Works like magic. Thanks Ron!

 

[Ron Coderre]

You're very welcome, Billy8147

One minor tweak to the formulas, though ...
Since the data does not start on Rol_1, we need to compensate:

MIN

B1: =MIN(INDEX(SUBTOTAL(9,OFFSET(A3,,,ROW(A3:A11)-ROW(A3)+1)),0))

MAX

C1: =MAX(INDEX(SUBTOTAL(9,OFFSET(A3,,,ROW(A3:A11)-ROW(A3)+1)),0))

 

[AENoonan0602]

This works for data that is in a column but what if you are working with data in a row instead? And how do you compensate for it not being in the first column?