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