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:D720)*(D66:D72<MAX(D66:D72))*D66: D72)/SUMPRODUCT((D66:D720)*(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???

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

=SUMPRODUCT((D66:D72SMALL(D66:D72,COUNTIF(D66:D72 ,0)+1))*(D66:D72<MAX(D66:D72))*(D66:D72))/SUMPRODUCT((D66:D72SMALL(D66:D72,COUNTIF(D66:D72, 0)+1))*(D66:D72<MAX(D66:D72)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ace" wrote:

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:D720)*(D66:D72<MAX(D66:D72))*D66: D72)/SUMPRODUCT((D66:D720)*(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???

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

"Luke M" wrote:

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

=SUMPRODUCT((D66:D72SMALL(D66:D72,COUNTIF(D66:D72 ,0)+1))*(D66:D72<MAX(D66:D72))*(D66:D72))/SUMPRODUCT((D66:D72SMALL(D66:D72,COUNTIF(D66:D72, 0)+1))*(D66:D72<MAX(D66:D72)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ace" wrote:

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:D720)*(D66:D72<MAX(D66:D72))*D66: D72)/SUMPRODUCT((D66:D720)*(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???