| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Understanding SUMPRODUCT
I needed to know how to find the closest match to a reference number
regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT
Hi Jordan,
See xlDynamic's SumProduct page at: http://www.xldynamic.com/source/xld.SUMPRODUCT.html --- Regards, Norman "Jordan" wrote in message ... I needed to know how to find the closest match to a reference number regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT
If it is possible to have a tie, SUMPRODUCT will sum the items in list that
are closest to your reference number. You could also use =INDEX(list,MATCH(TRUE,ABS(list-target)=MIN(ABS(list-target)),0)) which would return the first number in your list closest to the reference (it must be entered with Control+Shift+Enter) More on SUMPRODUCT and array formulas. http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://www.cpearson.com/excel/array.htm "Jordan" wrote: I needed to know how to find the closest match to a reference number regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT
Hi!
Hmmm... =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) That formula only works if it's entered as an array. But, if you change it around to: =SUMPRODUCT(((ABS(list-target))=ABS(MIN(list-target)))*list) Or, how I would write it: =SUMPRODUCT(--(ABS(list-target)=ABS(MIN(list-target))),list) And enter it normally, it works Can anyone explain that? Biff "Jordan" wrote in message ... I needed to know how to find the closest match to a reference number regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT
A few keystrokes shorter:
=INDEX(list,MATCH(MIN(ABS(list-target)),ABS(list-target),0)) Biff "JMB" wrote in message ... If it is possible to have a tie, SUMPRODUCT will sum the items in list that are closest to your reference number. You could also use =INDEX(list,MATCH(TRUE,ABS(list-target)=MIN(ABS(list-target)),0)) which would return the first number in your list closest to the reference (it must be entered with Control+Shift+Enter) More on SUMPRODUCT and array formulas. http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://www.cpearson.com/excel/array.htm "Jordan" wrote: I needed to know how to find the closest match to a reference number regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT
Disregard. I had a brain seizure!
Biff "Biff" wrote in message ... Hi! Hmmm... =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) That formula only works if it's entered as an array. But, if you change it around to: =SUMPRODUCT(((ABS(list-target))=ABS(MIN(list-target)))*list) Or, how I would write it: =SUMPRODUCT(--(ABS(list-target)=ABS(MIN(list-target))),list) And enter it normally, it works Can anyone explain that? Biff "Jordan" wrote in message ... I needed to know how to find the closest match to a reference number regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT
I really think this is a wrong choice to use SUMPRODUCT here, there is no
summing required, and it has to be array-entered anyway. The INDEX/MATCH options offered are a better solution IMO. -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "Jordan" wrote in message ... I needed to know how to find the closest match to a reference number regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT
Sorry, forgot this bit.
Taking BIff's formula ABS(list-target) creates an array of values which forces the closest matching n umber to be the smallest in the array (as it makes smaller numbers absolute, for instance if the list has 1,2,3,4,5 and target is 3.3, this creates an array of {2.3;1.3;0.3;0.7;1.7;3.3;3.3;3.3;3.3;3.3}- you can see the third item is 0.3. MIN(ABS(list-target) gets the smallest value there - 0.3 in the example This minimum value is matched against the same list MATCH(MIN(ABS(list-target)),ABS(list-target),0), which returns the index of that smallest number in the list This index is then used to get the value from the list INDEX(list, index_of_smallest,0) -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "Jordan" wrote in message ... I needed to know how to find the closest match to a reference number regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT
=SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list)
That formula only works if it's entered as an array. The superfluous ( ) aside, why doesn't that formula work if normally entered? =MIN(ABS(list-target)) Doesn't evaluate as an array unless array entered. Biff "Bob Phillips" wrote in message ... Sorry, forgot this bit. Taking BIff's formula ABS(list-target) creates an array of values which forces the closest matching n umber to be the smallest in the array (as it makes smaller numbers absolute, for instance if the list has 1,2,3,4,5 and target is 3.3, this creates an array of {2.3;1.3;0.3;0.7;1.7;3.3;3.3;3.3;3.3;3.3}- you can see the third item is 0.3. MIN(ABS(list-target) gets the smallest value there - 0.3 in the example This minimum value is matched against the same list MATCH(MIN(ABS(list-target)),ABS(list-target),0), which returns the index of that smallest number in the list This index is then used to get the value from the list INDEX(list, index_of_smallest,0) -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "Jordan" wrote in message ... I needed to know how to find the closest match to a reference number regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT
Because of the ABS
-- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "Biff" wrote in message ... =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) That formula only works if it's entered as an array. The superfluous ( ) aside, why doesn't that formula work if normally entered? =MIN(ABS(list-target)) Doesn't evaluate as an array unless array entered. Biff "Bob Phillips" wrote in message ... Sorry, forgot this bit. Taking BIff's formula ABS(list-target) creates an array of values which forces the closest matching n umber to be the smallest in the array (as it makes smaller numbers absolute, for instance if the list has 1,2,3,4,5 and target is 3.3, this creates an array of {2.3;1.3;0.3;0.7;1.7;3.3;3.3;3.3;3.3;3.3}- you can see the third item is 0.3. MIN(ABS(list-target) gets the smallest value there - 0.3 in the example This minimum value is matched against the same list MATCH(MIN(ABS(list-target)),ABS(list-target),0), which returns the index of that smallest number in the list This index is then used to get the value from the list INDEX(list, index_of_smallest,0) -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "Jordan" wrote in message ... I needed to know how to find the closest match to a reference number regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT
I don't mean to be a pita, but.........
SUMPRODUCT(((ABS(list-target)) evaluates as an array, but MIN(ABS(list-target)) doesn't. In my "brain seizure" post you'll see where if you swap the position of ABS and MIN: ABS(MIN(list-target)) then it is evaluated as array and can be normally entered although the logic is incorrect and can lead to incorrect results. Just trying to figure this out. Biff "Bob Phillips" wrote in message ... Because of the ABS -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "Biff" wrote in message ... =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) That formula only works if it's entered as an array. The superfluous ( ) aside, why doesn't that formula work if normally entered? =MIN(ABS(list-target)) Doesn't evaluate as an array unless array entered. Biff "Bob Phillips" wrote in message ... Sorry, forgot this bit. Taking BIff's formula ABS(list-target) creates an array of values which forces the closest matching n umber to be the smallest in the array (as it makes smaller numbers absolute, for instance if the list has 1,2,3,4,5 and target is 3.3, this creates an array of {2.3;1.3;0.3;0.7;1.7;3.3;3.3;3.3;3.3;3.3}- you can see the third item is 0.3. MIN(ABS(list-target) gets the smallest value there - 0.3 in the example This minimum value is matched against the same list MATCH(MIN(ABS(list-target)),ABS(list-target),0), which returns the index of that smallest number in the list This index is then used to get the value from the list INDEX(list, index_of_smallest,0) -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "Jordan" wrote in message ... I needed to know how to find the closest match to a reference number regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT
They both evaluate as an array on my system Biff!
-- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "Biff" wrote in message ... I don't mean to be a pita, but......... SUMPRODUCT(((ABS(list-target)) evaluates as an array, but MIN(ABS(list-target)) doesn't. In my "brain seizure" post you'll see where if you swap the position of ABS and MIN: ABS(MIN(list-target)) then it is evaluated as array and can be normally entered although the logic is incorrect and can lead to incorrect results. Just trying to figure this out. Biff "Bob Phillips" wrote in message ... Because of the ABS -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "Biff" wrote in message ... =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) That formula only works if it's entered as an array. The superfluous ( ) aside, why doesn't that formula work if normally entered? =MIN(ABS(list-target)) Doesn't evaluate as an array unless array entered. Biff "Bob Phillips" wrote in message ... Sorry, forgot this bit. Taking BIff's formula ABS(list-target) creates an array of values which forces the closest matching n umber to be the smallest in the array (as it makes smaller numbers absolute, for instance if the list has 1,2,3,4,5 and target is 3.3, this creates an array of {2.3;1.3;0.3;0.7;1.7;3.3;3.3;3.3;3.3;3.3}- you can see the third item is 0.3. MIN(ABS(list-target) gets the smallest value there - 0.3 in the example This minimum value is matched against the same list MATCH(MIN(ABS(list-target)),ABS(list-target),0), which returns the index of that smallest number in the list This index is then used to get the value from the list INDEX(list, index_of_smallest,0) -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "Jordan" wrote in message ... I needed to know how to find the closest match to a reference number regardless of whether its larger or smaller. I did a search and found a post back in March that said to use the following: =SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list) I applied it to my application and it works, I just have no idea why. Can anyone explain this formula to me or tell me where I can find a good resource. I used the help in Excel, but didnt find it very helpful. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
| Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
| Sumproduct | Excel Worksheet Functions | |||
| Sumproduct function not working | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
