Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PAL PAL is offline
external usenet poster
 
Posts: 200
Default Arrays and Average

I am working w/ a table. Lets say 25 rows/3 columns

Col 1 is product name
Col 2 is region name
Col 3 is number of units sold

I would like to get the average number of units for the sum of the products.
So if there are 2 products in the US that sold a combined 15 units, the
average would be 7.5.

I would like to do this for each region.

Please advise on formula. Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,420
Default Arrays and Average

=SUMIF(B1:B25,"US",C1:C25)/COUNT(1/IF(B1:B25="US",MATCH(A1:A25,A1:A25,0)=ROW($A1:A25)-ROW(A1)+1))

it is an array formula, so commit with ctrl-shift-enter

--
__________________________________
HTH

Bob

"PAL" wrote in message
...
I am working w/ a table. Lets say 25 rows/3 columns

Col 1 is product name
Col 2 is region name
Col 3 is number of units sold

I would like to get the average number of units for the sum of the
products.
So if there are 2 products in the US that sold a combined 15 units, the
average would be 7.5.

I would like to do this for each region.

Please advise on formula. Thanks.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Arrays and Average

PAL wrote:
I am working w/ a table. Lets say 25 rows/3 columns

Col 1 is product name
Col 2 is region name
Col 3 is number of units sold

I would like to get the average number of units for the sum of the products.
So if there are 2 products in the US that sold a combined 15 units, the
average would be 7.5.

I would like to do this for each region.

Please advise on formula. Thanks.



Two possible solutions he

http://www.savefile.com/files/2001590
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 144
Default Arrays and Average

try

=SUMPRODUCT(--(A2:A10="ABC"),--(B2:B10="us"),C2:C10)/SUMPRODUCT(--(A2:A10="ABC"),--(B2:B10="US"))
or
=SUMPRODUCT(--(A2:A10=D2),--(B2:B10=E2),C2:C10)/SUMPRODUCT(--(A2:A10=D2),--(B2:B10=E2))

D2 and E2 are where you type the product name and region

this give you the average of the sum based on product and region

HTH
--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis





"PAL" wrote:

I am working w/ a table. Lets say 25 rows/3 columns

Col 1 is product name
Col 2 is region name
Col 3 is number of units sold

I would like to get the average number of units for the sum of the products.
So if there are 2 products in the US that sold a combined 15 units, the
average would be 7.5.

I would like to do this for each region.

Please advise on formula. Thanks.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
A formula to AVERAGE IF but only average a set number of values [email protected] Excel Worksheet Functions 2 January 31st 08 09:28 PM
Find monthly average but have average automatically configured kimbafred Excel Discussion (Misc queries) 2 August 8th 07 12:28 AM
Error Handling #N/A with AVERAGE Function - Average of values in Row Sam via OfficeKB.com Excel Worksheet Functions 13 July 31st 05 03:59 PM
Using average in two condition arrays... andyd2k Excel Worksheet Functions 1 July 16th 05 05:56 AM
Weighed Average of a weiged average when there are blanks krl - ExcelForums.com Excel Discussion (Misc queries) 1 July 6th 05 07:37 PM


All times are GMT +1. The time now is 03:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"