AVERAGE & SUMPRODUCT

  • Thread starter Thread starter Dave F
  • Start date Start date
D

Dave F

I'm trying to calculate an average of a series of numbers, which
numbers are within a range. Not all members of the range should be
included in the average calculation, however.

I have the following:

=SUMPRODUCT(--(K2:K13="WMT"),(M2:M13))/COUNTIF(K2:K13,"WMT")

which obviously works but it would seem to me there is a more direct
way to do this using the AVERAGE function.

(I'm using SUMPRODUCT here instead of SUMIF because I may need to add
conditions in the future.)

Thoughts?
 
try
=average(if(K2:K13="WMT",M2:M13)) entered as array control-shift-enter
you could also use
=average(if(and(K2:K13="WMT",criteria 2),M2:M13)) entered as array
control-shift-enter

you could use Or instead of And if needed.
 
try:

=AVERAGE(IF(K2:K13="WMT",M2:M13))

Enter with Ctrl+Shift+Enter.

If entered correctly, you will get {} brackets round the formula.

HTH
 
You could use =averageif() in xl2007.


Dave said:
I'm trying to calculate an average of a series of numbers, which
numbers are within a range. Not all members of the range should be
included in the average calculation, however.

I have the following:

=SUMPRODUCT(--(K2:K13="WMT"),(M2:M13))/COUNTIF(K2:K13,"WMT")

which obviously works but it would seem to me there is a more direct
way to do this using the AVERAGE function.

(I'm using SUMPRODUCT here instead of SUMIF because I may need to add
conditions in the future.)

Thoughts?
Click to expand...
 
Excellent suggestion. How do I get my employer to install Office 07
on my pc?

The consensus here seems to be that an array formula is the way to go
on pre-XL07 installations.

Thanks to all for the feedback.
 
The average(if()) array construction works well for me in the interim.

No luck on getting XL 07 installed till early next year at my co.

Not an issue though.

Thanks for the help.

Dave
 
Back
Top