THANKS!
It works. I'll need to spend some time with it to figure out why, but I
really appreciate geting an answer that does the job - especially so quickly.
Thanks again.

"T. Valko" wrote:

Try this array formula** :

=MATCH(D2,SUBTOTAL(9,OFFSET(A1:A10,,,ROW(A1:A10)-MIN(ROW(A1:A10)))))-(SUM(A1:A10)D2)


--
Biff
Microsoft Excel MVP


"Chas Grad" wrote in message
...
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