SUBTOTAL returns an array of sums based on the height argument. SUM would
return a *single* sum value of the entire range. So we need to use SUBTOTAL
to "step" through the range.

--
Biff
Microsoft Excel MVP


"Chas Grad" wrote in message
...
I made the problem harder by not including in the sample the fact that I
already had the list {1, 2, 3.) available in the real spreadsheet and didn't
need to resort to the ROW function for that. Judging by how quickly you
responded, it didn't faze you, though. The key concept I missed (even
after
staring at your elegant solution for a while) was that I needed the height
of
the array returned by offset to be other than 1. A dumb oversight now
that I
see it. I still don't understand why it works with SUBTOTAL and not with
SUM, though. Is there a concept I am missing or is it an Excel quirk?
The formula in the actual spreadsheet is (now) as follows. I'm trying to
figure in what year limited partners in an oil well get their original
investment back.
MATCH(Initial_Investment,
SUBTOTAL(9,OFFSET(net_cash_flow,,,Production_Years
) ) ) - 1

Thanks again for your help. I really wanted to avoid another row of
numbers
in this spreadsheet.

"Chas Grad" wrote:

I want to find the last location in a series of numbers where the
cumulative
sum of the numbers does not exceed a target value.
This is easy to do using the MATCH function and a second list that
contains
the cumulative sum of the values in the first list.
In the example below, A1:A1 contains the list of interest and B1:B10
contains the cumulative sums.
The formula MATCH(D2,B1:B101) in D3 finds the location of the first value
in
B1:B10 that is less than or equal to the target.
I want to achieve that result without using the second list containg the
cumulative sums.
I created an array formula to yield the results of col B for the MATCH
function to work on, but the result is #NA
The formula I used was {MATCH( D2, SUM(OFFSET(A1,0,0,A1:A10,1), 1)}
When I just put the offset formula in a cell and hit F9 I see this
result:
{1;1;1;1;1;1;1;1;1;1}
Any suggestions on how I can accomplish my goal of not having to have the
formulas in col B?
I was unsuccessful at pasting a picture here. Here's a crude
reproduction
of what the sample sheet looked like:
A B C D
1 1 1 Target 35
2 2 3 Location 7 ( =MATCH(D2, B1:B10,1) )
3 3 6
4 4 10
5 5 15
6 6 21
7 7 28
8 8 36
9 9 45
10 10 55