Do not use on any
shared computer
May 17, 2008 11:34am pdt
05.08.2008 at 02:25PM PDT, ID: 23387772
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

Average Calculation
Tags: Microsoft, Office, 2003, Excel
Hello,

I have another Excel question.  Last one went pretty smooth and now I have a new one regarding a formula.  I have a spreadsheet with test scores for different employees, looks something like this:

Group A:

Name                     Test 1              Test 2         Location

John Doe                 25%                8%                 A
Jane Doe                 13%               32%                B
John Smith               82%               66%                B
Jane Smith               55%               95%                A

Problem is:  Management wants an average for each test in Group A.   Next, they want an average per location only.  When I use the average function for Test 1, I get 43.75, which is fine.

But then when I do an average for Test 1, of each location, for example, Location A then Location B.  The total of both locations does not amount to the overall average of Test 1.  I don't know if it's supposed to or not, but I just want to show management that my overall test averages are correct.  They are asking why when broken up into individual locations, the totals do not add up.

My math skills are not doing too well today (or maybe it's management's), so please bear with me.

Please let me know if more info is needed.  Thank you.
Start your free trial to view this solution
Related Solutions: Average Calculation pt 2
Question Stats
Zone: Software
Question Asked By: Lucho305
Solution Provided By: saurabh726
Participating Experts: 4
Solution Grade: A
Views: 17
Translate:
Loading Advertisement...
05.08.2008 at 02:31PM PDT, ID: 21528647

Rank: Sage

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.08.2008 at 02:32PM PDT, ID: 21528660

Rank: Master

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.08.2008 at 02:35PM PDT, ID: 21528677

Rank: Sage

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.08.2008 at 02:37PM PDT, ID: 21528696

Rank: Master

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.08.2008 at 02:51PM PDT, ID: 21528771

Rank: Master

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.08.2008 at 11:10PM PDT, ID: 21530668

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.09.2008 at 12:22AM PDT, ID: 21530900

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.09.2008 at 12:37AM PDT, ID: 21530948

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.09.2008 at 04:48AM PDT, ID: 21531823

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.09.2008 at 04:57AM PDT, ID: 21531872

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.09.2008 at 06:15AM PDT, ID: 21532414

Rank: Sage

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.09.2008 at 06:46AM PDT, ID: 21532706

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.12.2008 at 07:22AM PDT, ID: 21547234

Rank: Sage

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.12.2008 at 02:12PM PDT, ID: 21550564

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Handhelds / PDAs
  • Displays / Monitors
  • Components
  • Networking Hardware
  • Peripherals
  • Laptops/Notebooks
  • Storage
  • Servers
  • Desktops
  • New Users
  • Misc
  • Apple
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMWare
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMWare
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Community Advisor
  • Lounge
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • Community Advisor
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
05.08.2008 at 02:31PM PDT, ID: 21528647

Rank: Sage

Did you meant this....

HTH...

Saurabh...
 
Data..
 
 
05.08.2008 at 02:32PM PDT, ID: 21528660

Rank: Master

Your averages will not necessarily match as shown below
Score S
Count C

Total average = (SA+ SB)/(CA+CB)
Average A= SA/CA
AVerage B = SB/CB
Average A + Average B=SA/CA+SB/CB
To get your total average from the sum of the averages, you need to do a weighted average, i.e.
SA/CA * CA/(CA+CB) + SB/CB * CB/(CA+CB)
 
05.08.2008 at 02:35PM PDT, ID: 21528677

Rank: Sage

And Also just to add ...what Thomas Said...

Averages of Average  and Average doesnt match ever...because its practically not possible to match..because they just simply dont ever....

Saurabh...
 
05.08.2008 at 02:37PM PDT, ID: 21528696

Rank: Master

Unless each category has an equal weight in the repartition.
 
05.08.2008 at 02:51PM PDT, ID: 21528771

Rank: Master

Simple way to do it with the daverage() function, and a way to write a formula like nutsch suggests so the result is weighted.
 
find location averages
 
 
05.08.2008 at 11:10PM PDT, ID: 21530668
Wow, I didn't know it was much more complicated than just doing an AVERAGE function.  Could someone please explain what each of those functions is doing?  Or maybe a link where I can do some reading up.  I am really interested and also think it would come in handy for future projects.

I have attached one of the actual spreadsheets, so you can see what I'm dealing with.  Management wants an average for each Group (1, 2, 3, etc.) and then an average for each Ethnicity (W, B, H, etc.).  Anything else you think they might want?  Thank you for the quick responses and all your help.  

***edited for content m_matt***
 
05.09.2008 at 12:22AM PDT, ID: 21530900

Rank: Guru

Lucho305, your data looks real. If that is the case, you should ask community support to remove it immediately. You cannot publish this sort of information (even if we are not supposed to know what the numbers are). Replace the names with a number or a random string of characters.

(°v°)
 
05.09.2008 at 12:37AM PDT, ID: 21530948

Rank: Guru

Back to your question:

> They are asking why when broken up into individual locations, the totals do not add up.

If you are creating subtotals, then the subtotals should add up to form the grand total. If you are calculating averages, the averages will neither sum up nor average to the grand average. How come?

Say there are five people, with their heights:

    Albert: 176 cm
    Ben: 174 cm
    Carlos: 172 cm
    Debby: 170 cm
    Emily: 168 cm

The average height will be 172 cm. Right? The average for males will be 174 cm, and the average for females 169 cm. Why should 174 + 169 = 172? It doesn't make sense, does it? The average of the averages will not match either: it's 171.5 cm instead of 172 cm. As nutsch already said, since there are three people in the male average and only two in the female average, they don't have the same "weight". A weighted average of the averages would return 172 cm.

Does that help?
(°v°)
 
05.09.2008 at 04:48AM PDT, ID: 21531823
I uploaded the wrong one.  The one I was supposed to upload did not have any names.  Will get that done ASAP.  I apologize, it was pretty late when I did it.
 
05.09.2008 at 04:57AM PDT, ID: 21531872
OK, I just contacted the site to have it removed.  So hopefully it will be done soon.  Here is the correct one.

Regarding the averages, I see that the averages are not going to add up.  That is fine.  But, what formula should I use to calculate the average of each test, for each group and then for each ethnicity?
 
05.09.2008 at 06:15AM PDT, ID: 21532414

Rank: Sage

Two ways to do the same..One with pivot and other with formulas...i have done both the ways..so choose as per what you like...

HTH...

Saurabh..

 
 
Accepted Solution
 
05.09.2008 at 06:46AM PDT, ID: 21532706

Rank: Guru

Yet another way to check the figures.

* (Data | Sort), Sort by: Group, then by: Ethnicity, [OK]
* (Data | Subtotals), At each change in: Group, use function: Average, add subtotals to: [check all the columns after Group], [OK]
* (Data | Subtotals), At each change in: Ethnicity, [rest is the same], uncheck "Replace current subtotals", [OK]

Cheers!
(°v°)
Assisted Solution
 
05.12.2008 at 07:22AM PDT, ID: 21547234

Rank: Sage

Yw...Glad to help..Thanx for the Grade...and you can start anytime a new question..and there are lot of experts out here..to help you out..dont you worry about that....

Saurabh...
 
05.12.2008 at 02:12PM PDT, ID: 21550564

Rank: Guru

Thanks. If you want to make sure we see your next question, use the "ask a related question" link; that way, we will receive a notification about it, but other experts will feel free to answer as well.

(°v°)
 
 
20080206-EE-VQP-25 / EE_QW_2_20070628