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

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

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

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

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