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.