Sumproduct question

A

ace

Guys,

I have a question regarding the sumproduct formula.
Here is the situation;
considering the following data set;
$0.00 (D66)
$0.00 (D67)
$14.00
$18.75
$18.25
$20.00
$20.00 (D72)
I am computing the adjusted average for the above data set discounting zeros
and the max outlier.
My formula is

SUMPRODUCT((D66:D72>0)*(D66:D72<MAX(D66:D72))*D66:D72)/SUMPRODUCT((D66:D72>0)*(D66:D72<MAX(D66:D72)))

In the situation described above, it must take 14 as the outlier and discard
as that is more qualitatively appropriate.
So my question is, can the sumproduct check for the max and min outlier,
discard whichever is farthest from the simple average (simple average too
discards zeros), discard zeros, and then give me the adjusted average???
 
L

Luke M

We can use the SMALL function to find the first number greater than 0.

=SUMPRODUCT((D66:D72>SMALL(D66:D72,COUNTIF(D66:D72,0)+1))*(D66:D72<MAX(D66:D72))*(D66:D72))/SUMPRODUCT((D66:D72>SMALL(D66:D72,COUNTIF(D66:D72,0)+1))*(D66:D72<MAX(D66:D72)))
 
A

ace

Luke,

This thing removes the 14 in this case, as it should.
But suppose i have a data set
0.00 (D66)
$0.00 (D67)
$14.00
$18.75
$18.25
$19.00
$26.00 (D72)
Here 14 and 26 are both outliers but then in this case it should discount 26
and not 14. i mean can it check for outliers on the max and the min end and
then remove the farthest one, then be it max or min.

Thanks,
Ace
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top