| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and average functions
I have a list of accts with their # of employees and their annual revenues.
I want to take the average of their annual revenue if they have between 50 & 100 employees. I figured I needed to use the average and sumproduct functions, but I'm not sure how. Please help, thanks -- Turi |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and average functions
I would use Advanced Filter to extract from your list those records for which
the number of emplyees is 49 and <101. And then run your average on that extracted table. For info on Advanced Filter, see he http://www.ozgrid.com/Excel/advanced-filter.htm Dave -- Brevity is the soul of wit. "Turi" wrote: I have a list of accts with their # of employees and their annual revenues. I want to take the average of their annual revenue if they have between 50 & 100 employees. I figured I needed to use the average and sumproduct functions, but I'm not sure how. Please help, thanks -- Turi |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and average functions
=AVERAGE(IF((A1:A100049)*(A1:A1000<101),B1:10000) )
ctrlshiftenter (not just enter) "Turi" wrote: I have a list of accts with their # of employees and their annual revenues. I want to take the average of their annual revenue if they have between 50 & 100 employees. I figured I needed to use the average and sumproduct functions, but I'm not sure how. Please help, thanks -- Turi |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and average functions
That would work for one range, but I failed to say that I've got a bunch of
employee ranges that I'd like to be able to apply the formula to. (ie 1-25 employee rev ave, 25-50 employee rev ave, etc.) -- Turi "Dave F" wrote: I would use Advanced Filter to extract from your list those records for which the number of emplyees is 49 and <101. And then run your average on that extracted table. For info on Advanced Filter, see he http://www.ozgrid.com/Excel/advanced-filter.htm Dave -- Brevity is the soul of wit. "Turi" wrote: I have a list of accts with their # of employees and their annual revenues. I want to take the average of their annual revenue if they have between 50 & 100 employees. I figured I needed to use the average and sumproduct functions, but I'm not sure how. Please help, thanks -- Turi |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and average functions
Well, you can use the formula provided by teethless mama in response to your
initial question, but even there you have to specify the constraints you want to use. -- Brevity is the soul of wit. "Turi" wrote: That would work for one range, but I failed to say that I've got a bunch of employee ranges that I'd like to be able to apply the formula to. (ie 1-25 employee rev ave, 25-50 employee rev ave, etc.) -- Turi "Dave F" wrote: I would use Advanced Filter to extract from your list those records for which the number of emplyees is 49 and <101. And then run your average on that extracted table. For info on Advanced Filter, see he http://www.ozgrid.com/Excel/advanced-filter.htm Dave -- Brevity is the soul of wit. "Turi" wrote: I have a list of accts with their # of employees and their annual revenues. I want to take the average of their annual revenue if they have between 50 & 100 employees. I figured I needed to use the average and sumproduct functions, but I'm not sure how. Please help, thanks -- Turi |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Sumproduct to Find Average | Excel Discussion (Misc queries) | |||
| Sumproduct Average | Excel Discussion (Misc queries) | |||
| sumproduct & average | Excel Worksheet Functions | |||
| Need to bring back Average using either Sumproduct or CSE formula | Excel Discussion (Misc queries) | |||
| Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Linear Mode
