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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.