Understanding SUMPRODUCT

[Guest]

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.

 

[Norman Jones]

Hi Jordan,

See xlDynamic's SumProduct page at:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

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

 

[Biff]

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

 

[Biff]

A few keystrokes shorter:

=INDEX(list,MATCH(MIN(ABS(list-target)),ABS(list-target),0))

Biff

 

[Biff]

Disregard. I had a brain seizure!

Biff

Hi!

Hmmm...


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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Biff]

=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]

Because of the ABS

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[Biff]

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]

They both evaluate as an array on my system Biff!

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)