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.

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

"PAL" wrote:

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.

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

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

Mike

"Mike H" wrote:

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

"PAL" wrote:

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.

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)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"PAL" wrote:

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.

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")


--
__________________________________
HTH

Bob

"Mike H" wrote in message
...
and just because it's slow in here a non array version

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

Mike

"Mike H" wrote:

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

"PAL" wrote:

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.

PAL wrote:
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).

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

"Bob Phillips" wrote:

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")


--
__________________________________
HTH

Bob

"Mike H" wrote in message
...
and just because it's slow in here a non array version

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

Mike

"Mike H" wrote:

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

"PAL" wrote:

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.

How do I measure the diferenece in performance

There is calculation timer code he

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

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
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

"Bob Phillips" wrote:

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")


--
__________________________________
HTH

Bob

"Mike H" wrote in message
...
and just because it's slow in here a non array version

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

Mike

"Mike H" wrote:

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

"PAL" wrote:

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.

Thanks Biff I'll look at that

"T. Valko" wrote:

How do I measure the diferenece in performance

There is calculation timer code he

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

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
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

"Bob Phillips" wrote:

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")


--
__________________________________
HTH

Bob

"Mike H" wrote in message
...
and just because it's slow in here a non array version

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

Mike

"Mike H" wrote:

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

"PAL" wrote:

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.