SUMPRODUCT to average

  • Thread starter Thread starter AJPendragon
  • Start date Start date
A

AJPendragon

I have a SUMPRODUCT for numbered data, but also require it for % data. My
SUMPRODUCT is:

=SUMPRODUCT(--(Sheet2!B4:B573=A6),(Sheet2!C4:C573))

This provides a total % figure of 475%, but I want the average of the 5
figures selected (it could be as many as 1-5 figures) - is this possible with
the SUMPRODUCT formula.

Thanks for looking
 
An average of 5 percentage values doesn't mean very much unless the
totals for those five values are the same size. You could divide your
formula by a count of those values that met the criteria in A6 (i.e.
by COUNTIF(Sheet2!B4:B573,A6) ), but this wouldn't mean very much.
Perhaps you could explain more about what you want to achieve ...

Pete
 
Try this array formula** :

=AVERAGE(IF(Sheet2!B4:B573=A6,Sheet2!C4:C573))

Format as %

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Back
Top