arrays and averages
Try this array formula** :
For the average of region US:
=AVERAGE(IF(B1:B3="US",C1:C3))
Better to use a cell to hold the criteria:
E1 = US
=AVERAGE(IF(B1:B3=E1,C1:C3))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
--
Biff
Microsoft Excel MVP
"PAL" wrote in message
...
I have a table 30 rows, 3 columns
Col A is product name (3 products)
Col B is region name (US)
Col C is # Unit for each product (5)
Example
Cars US 5
Trucks US 3
Cars Europe 6
How create a formula that will give me the average number of units for the
sum of the products in a given region.
Output in example should be (5+3)/2 =4.5
Thanks.
|