Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT to average
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT to average
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 On Dec 11, 6:58 pm, AJPendragon wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT to average
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) -- Biff Microsoft Excel MVP "AJPendragon" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGE & SUMPRODUCT | Excel Discussion (Misc queries) | |||
Average nesting within Sumproduct? | Excel Worksheet Functions | |||
Using Sumproduct to Find Average | Excel Discussion (Misc queries) | |||
Sumproduct Average | Excel Discussion (Misc queries) | |||
sumproduct & average | Excel Worksheet Functions |