sumproduct and average functions

[Guest]

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

 

[Guest]

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 here:
http://www.ozgrid.com/Excel/advanced-filter.htm

Dave

 

[Guest]

=AVERAGE(IF((A1:A1000>49)*(A1:A1000<101),B1:10000))

ctrl>shift>enter (not just enter)

 

[Guest]

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

 

[Guest]

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.