Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Using average in two condition arrays... | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) |