Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Array function does not seem to work | Excel Discussion (Misc queries) | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
1:1 as the Array using the MATCH function | New Users to Excel | |||
Match as well as does not match array function | Excel Discussion (Misc queries) |