Arrays and Averages

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

 

[Mike H]

Hi,

=AVERAGE(IF(B1:B20="US",C1:C20))

I think the average of 5+3 is 4 but perhaps that's just me

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

 

[Mike H]

and just because it's slow in here a non array version

=SUMPRODUCT((B1:B20="US")*(C1:C20))/COUNTIF(B1:B20,"US")

Mike

 

[Shane Devenshire]

Hi,

Suppose your data starts in A1:C99 with titles Items, Region, Units on the
first row

In a blank cell enter the title at the top of the Region column (in my
example F1) and under it the region you want to total, then use the formula:

=DSUM(A1:C99,C1,F1:F2)

 

[Bob Phillips]

SUMPRODUCT is faster than array formulae, but not enough to worry about IMO.

But if you have interpreted the question correctly, there is no need to use
SUMPRODUCT, you can use the MUCH faster SUMIF

=SUMIF(B1:B20,"US",C1:C20)/COUNTIF(B1:B20,"US")

 

[Glenn]

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.

Hey PAL...instead of repeatedly posting the same question (6 times), maybe you
could respond to one of the many solutions you've been offered (12 posts by 7
people according to my count).

 

[Mike H]

Bob,

I took all 3 formula, my 2 and yours and reset the ranges to 1 - 65535 and
filled down using the original OP data. I then Changed a single cell in the
sum range to force re-calculation and all the formula refreshed virtually
instantly.

How do I measure the diferenece in performance or would the formula need to
be more complicated to notice a difference?

Mike

 

[T. Valko]

How do I measure the diferenece in performance

There is calculation timer code here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

Or, you can pick up a copy of Charles Williams FastExcel

http://www.decisionmodels.com/index.htm

 

[Mike H]

Thanks Biff I'll look at that

There is calculation timer code here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

Or, you can pick up a copy of Charles Williams FastExcel

http://www.decisionmodels.com/index.htm