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