arrays and averages
 

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.

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.