Can't get array formula to work in MATCH function
 

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

Can't get array formula to work in MATCH function
 

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

Can't get array formula to work in MATCH function
 

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

Can't get array formula to work in MATCH function
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

Can't get array formula to work in MATCH function
 

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 didnt
need to resort to the ROW function for that. Judging by how quickly you
responded, it didnt 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 dont 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. Im 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

Can't get array formula to work in MATCH function
 

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