sumproduct and average functions

T

Turi

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
 
T

Teethless mama

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

ctrl>shift>enter (not just enter)
 
T

Turi

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

Dave F

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