Average(If...

  • Thread starter Thread starter MD
  • Start date Start date
M

MD

Please Help...This is a Quality Monitoring Form.

Sheet B has all representatives sorted Alphanumerically.
They all report to different teams. My formula resides on
sheet A and reads {=AVERAGE(IF('Sheet B'!$D$2:$D$200="Team
1",'Sheet B'!$F$2:$F$200))}

The "D" column represents the Team Number and the "F"
column represents the score that I want retrieved.

I can't sort by Team, because they change often. The
reason I can't change the sheets is because each of the
sheets represent a week in the month with the same
Representatives.

If this is too unclear, please contact me for
clarification.

Thank you.
 
I'm not sure exactly what you're asking for, but I'll describe how I do this, and
you might get an idea.

If your items in column D change frequently, your formulas will soon become
obsolete. I like to use the region below the table to set out the items I want to
sum by. I'll just show my summaries below columns D and F:

Row Column D Column F
--- -------- --------
201 Team 1 {=AVERAGE(IF($D$2:$D$200=$D201,F$2:F$200))}
202 Team 2 {=AVERAGE(IF($D$2:$D$200=$D202,F$2:F$200))}

I can easily get my totals for Team 1, Team 2, etc., and if I'm smart writing the
formula, I can write it once and drag or copy-paste it to use it in more cells. I
took the $ signs off the references to column F, in case I also want to total up
column G.

If columns B and C list other attributes, you can expand this to add by any single
column, or any combination of columns.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top