Finding a value based on a running sum

[John Casey]

Here's a poser, I'd appreciate any suggestions you may have.

I have a time series with two values, call them A and B. What I need to do
is count back from the present such that when the sum of values in column A
exceeds a certain level, Excel prints the maximum column B value for the
range.

50 5
75 8
60 7
55 6
110 5

In other words, say there are values in A1:A5 and B1:B5. Row 1 is the oldest
and row 5 the most recent data. If the threshold value in column A is 110,
for row 2 Excel prints 8, for row 3 it prints 8, for row 4 it prints 7, for
row 5 it prints 5.

I've got a very kludgy solution involving a column to test every possible
sum but this can run to hundreds of columns. Even nesting IFs to the maximum
extent possible is incredibly time-consuming , error-prone and still
enormous.

I wonder if you can think of a more elegant solution to this problem. I've
defined it fairly simply but I can think of any number or real-world cases
where exactly this question arises. Any help you can offer would be much
appreciated.

Many thanks in advance,



John

 

[Mike H]

John


50 5
75 8
60 7
55 6
110 5

In other words, say there are values in A1:A5 and B1:B5. Row 1 is the oldest
and row 5 the most recent data. If the threshold value in column A is 110,
for row 2 Excel prints 8, for row 3 it prints 8, for row 4 it prints 7, for
row 5 it prints 5.


In your data above why is row 1 = 5 and why does row 2 =8 etc

Mike

 

[Lori]

Maybe this filled down from c2 (with d1 as threshold value):

=-LOOKUP(-MIN(D$1,SUM(A$1:A2)),-SUBTOTAL({9,4},
OFFSET(A2,ROW(A$1:A2)-ROW(),{0,1},ROW()-ROW(A$1:A2)+1)))

 

[John Casey]

This is it. Brilliant, thanks. I have to work through this as I've never used
most of these functions before, but it's extremely elegant and it gives
exactly the result expected. Sl thanks a million, Lori.

 

[Lori]

Glad it worked for you and thanks for the reply.

Basically, the subtotal part returns an array of running sums and associated
max values. The negatives and min function ensure the data is ordered
correctly for the lookup function.