arrays and avgs

P

PAL

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.
 
T

T. Valko

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top