calculating a mean using sumproduct

  • Thread starter Thread starter skweetis
  • Start date Start date
S

skweetis

Hello all,
My prusuit to find a formula continues again.
I am trying calculate a mean of 95% from a list of data. This data i
selected w/ different criteria as well.

What I have so far is:
=(SUMPRODUCT(('[ATTR Report.xls]Dec Resi'!$A$2:$A$2000=BI7)*('[ATT
Report.xls]Dec Resi'!$H$2:$H$2000)*('[ATTR Report.xls]De
Resi'!$F$2:$F$2000=I5)))/(I7-(I7*0.05))

The Sumproduct selects the data I want to pull the mean from wit
specific criteria. I then Divide this number by a the count of of th
number of entries in the mean designated in I7.

This is not accurate however as it is taking the total sum and dividin
the number of records that are in the mean. What I am needing is
formula that can either calculate the mean for me, or a formula tha
will determine the sum of the records in mean.

Any assistance is appreciated.

Thanks,
Scot
 
skweetis said:
What I have so far is:
=(SUMPRODUCT(('[ATTR Report.xls]Dec Resi'!$A$2:$A$2000=BI7)*('[ATTR
Report.xls]Dec Resi'!$H$2:$H$2000)*('[ATTR Report.xls]Dec
Resi'!$F$2:$F$2000=I5)))/(I7-(I7*0.05))
Click to expand...

The '/(I7-(I7*0.05))' is equivalent to '/I7/0.95'.
The Sumproduct selects the data I want to pull the mean from with
specific criteria. I then Divide this number by a the count of of the
number of entries in the mean designated in I7.

This is not accurate however as it is taking the total sum and dividing
the number of records that are in the mean. What I am needing is a
formula that can either calculate the mean for me, or a formula that
will determine the sum of the records in mean.
Click to expand...

Maybe the array formula

=AVERAGE(IF(('[ATTR Report.xls]Dec Resi'!$A$2:$A$2000=BI7)
*('[ATTR Report.xls]Dec Resi'!$F$2:$F$2000=I5),
'[ATTR Report.xls]Dec Resi'!$H$2:$H$2000))

Sometimes SUMPRODUCT *isn't* the right function to use.
 
Let me clarify...maybe I'm looking for something a bit different and
just asking the wrong question.

How would I calculate the sum of a list leaving out the top and bottom
5%. Keeping in mind that the Min and Max are dynamic.
 
skweetis said:
Let me clarify...maybe I'm looking for something a bit different and
just asking the wrong question.

How would I calculate the sum of a list leaving out the top and bottom
5%. Keeping in mind that the Min and Max are dynamic.
Click to expand...

Is that excluding the bottom 5% and the top 5% or the extreme 5%, so bottom
2.5% and top 2.5%? I'll assume the former. Use Excel's TRIMMEAN function.
See the description in online help.
 
From Help for the TRIMMEAN function:

"Returns the mean of the interior of a data set. TRIMMEAN calculates the mean taken by excluding
a percentage of data points from the top and bottom tails of a data set. You can use this
function when you wish to exclude outlying data from your analysis.

"Syntax

" TRIMMEAN(array,percent)

" Array is the array or range of values to trim and average.

" Percent is the fractional number of data points to exclude from the calculation. For
example, if percent = 0.2, 4 points are trimmed from a data set of 20 points (20 x 0.2): 2 from
the top and 2 from the bottom of the set. "

Sounds like it was made to order for your problem. The 2nd argument should be 0.1 to exclude 5%
from both ends.
 
skweetis said:
Yeah, I was looking at that one. But how would I incorporate 3
different criterias.
Click to expand...

Actually, TRIMMEAN may not be ideal due to the rounding procedure it uses.

General condition averaging would be accomplished with

=SUMPRODUCT(--(CritExpr1),--(CritExpr2),...,--(CritExprN),Values)
/SUMPRODUCT(--(CritExpr1),--(CritExpr2),...,--(CritExprN))
 
Harlan Grove said:
Actually, TRIMMEAN may not be ideal due to the rounding procedure it
uses.

General condition averaging would be accomplished with

=SUMPRODUCT(--(CritExpr1),--(CritExpr2),...,--(CritExprN),Values)
/SUMPRODUCT(--(CritExpr1),--(CritExpr2),...,--(CritExprN)) *
Click to expand...


Well, this is what I'm trying to get away from. A straight average
defeats the purpose of the mean I'm trying to establish. The trimmean
is what I want to use, but I cant seem to get an IF function to work in
the array part of the function.
 
skweetis said:
Harlan Grove wrote: ....

Well, this is what I'm trying to get away from. A straight average
defeats the purpose of the mean I'm trying to establish. The trimmean
is what I want to use, but I cant seem to get an IF function to work in
the array part of the function.
Click to expand...

What's wrong with the general approach?

Anyway, =TRIMMEAN(IF((CritExpr1)*...*(CritExprN),Values),0) returns the same
result as =AVERAGE(IF((CritExpr1)*...*(CritExprN),Values)) when both are
entered as array formulas (even when entered as regular formulas - then both
return #VALUE!). However, much fun & games when you give TRIMMEAN a positive
2nd argument since it rounds the number of excluded observations.
 
Back
Top