December 11th, 2017, 12:58 AM  #1 
Newbie Joined: Dec 2017 From: UK Posts: 5 Thanks: 0  Help with Averages
Hi Guys, My maths isn't great and wanted some advice and help on a issue related to averages. I have a screenshot of some data below. As you can see I have 15 users from A to O. For each user I can calculate a percentage based on the value divide by the out of then multiply by 100. This gives me a percentage for that user. The dilemma starts at the bottom where you sum everything up. If I sum up the Value and the Out of column, I get 1494 out of 1648. If I convert that into a percentage exactly the same way I converted each user percentage, I get 90.66 (rounded). If I divide the sum of the columns value and out of by total users which is 15, I get 99.6 out of 109.8666667. The percentage of that is also 90.66 (rounded). However, if I was to sum up all the user percentages and then divide by 15 that gives me 87.09% which is completely different. The problem arises, (see second screenshot) if, for example, I want to display the data without showing the value or the out of column. If I show the percentage column by itself and right at the bottom, I display 90.66 as overall percentage. Then for someone looking at that it won't make sense because if you average out from user A to O the average works out to 87.09% not 90.66. So question is which method is correct? Is calculating the percentage on the value and out of columns an actual average too? Because I thought an average usually has to be divided. Whereas by calculating it overall there is not dividing by 15 Last edited by skipjack; December 11th, 2017 at 04:49 AM. 
December 11th, 2017, 06:09 AM  #2 
Senior Member Joined: May 2016 From: USA Posts: 1,038 Thanks: 423 
Your math is correct. In general, the average of the unweighted ratios will NOT equal the ratio of the sums. Why does this happen? Notice that E contributes 127 out of the total of 1648 whereas J contributes only 39. Yet you are giving their ratios equal weight when you average the ratios. In many cases that makes little sense. You can eliminate the problem with a weighted average. Do you know the method for assigning correct weights? Last edited by JeffM1; December 11th, 2017 at 06:11 AM. 
December 11th, 2017, 06:21 AM  #3  
Newbie Joined: Dec 2017 From: UK Posts: 5 Thanks: 0  Quote:
 
December 11th, 2017, 06:30 AM  #4 
Senior Member Joined: May 2016 From: USA Posts: 1,038 Thanks: 423 
John, I can't answer right now. I shall return in about 3 hours. I hope someone else will have answered you before then, but if not I'll explain when I am again free.

December 11th, 2017, 07:06 AM  #5 
Newbie Joined: Dec 2017 From: UK Posts: 5 Thanks: 0 
Ok Thanks Jeff. Also, am I correct in understanding that the value of 90.66 that is achieved from summing up both columns and turning it into a percentage is actually not an average but more of an overall percentage of all those users?

December 11th, 2017, 08:23 AM  #6 
Math Team Joined: Oct 2011 From: Ottawa Ontario, Canada Posts: 12,619 Thanks: 845 
Simple case: 50 out of 100 : 50% 60 out of 240 : 25%  110 out of 340 : 32.35...% 50 out of 100 : 50% 60 out of 120 : 50%  110 out of 220 : 50% Helps your thinking? 
December 11th, 2017, 10:27 AM  #7  
Senior Member Joined: May 2016 From: USA Posts: 1,038 Thanks: 423  Quote:
I would probably call an overall rate or percentage. You are not focusing on cases A through O with that number, but on what is the final result overall. The percentages that you did for each individual case may be meaningful for your purpose, but they will almost never have an average that will equal the overall percentage. (Do you need to understand why mathematically?) I would label them "individual percentage." Multiply the individual percentage times a weight to get a weighted percentage. This number tells you how much of the overall percentage was contributed by that individual. The sum of the weighted percentages will equal the overall percentage (except perhaps for rounding error). But what are the weights? The weight for case A is 108 / 1648, for B 116 / 1648, and so on. It is easy to do in a spread sheet. (Do you need to understand the math behind this?)  
December 12th, 2017, 06:26 AM  #8 
Newbie Joined: Dec 2017 From: UK Posts: 5 Thanks: 0 
Jeff, that's great. What a great explanation. Makes sense and i've mocked it up in excel creating the weight and works great. If I am looking at that data and let's say I am asked for Average Percentage for Users D to K. What would be the correct data the average or the overall? because the average will be percentage of D divide by percentage of K multiply by 100 which will give 79.95%. Whereas the overall percentage would be 86.26%. 
December 12th, 2017, 07:38 AM  #9 
Senior Member Joined: May 2016 From: USA Posts: 1,038 Thanks: 423 
If you are going to want to do various things with data of this kind, you better understand the math, and you need to distinguish between math and presentation. Let's set up a vocabulary. The numbers that in your specific example add up to 1648 will count "trials," a term that is frequently used in certain kinds of statistics. It is a total. We will call the numbers that add up to 1494 "successes." These terms are sort of arbitrary, but we need names for things. Basic math. You were taught a long time ago how to add fractions and learned that you cannot directly add fractions with different denominators. So let's take a supersimple example of what you originally did. Suppose we have case A with 10 trials and 4 successes, and case B with 20 trials and 2 successes. So the overall success ratio obviously is $\dfrac{4 + 2}{10 + 20} = \dfrac{6}{30} = \dfrac{1}{5} = 20\%.$ Now in case A we have a success ratio of $\dfrac{4}{10} = 40\%,$ and in case B we get $\dfrac{2}{20} = 10\%.$ When we average 40% and 10%, we get 25%, which is the wrong answer. This is an example showing that, in general, $\dfrac{\dfrac{a}{b} + \dfrac{c}{d}}{2} \ne \dfrac{a + c}{b + d}.$ This is the math behind the problem you were having. Now we calculate the weights as follows for A and B respectively $\dfrac{10}{10 + 20} = \dfrac{1}{3} \text { and } \dfrac{20}{10 + 20} = \dfrac{2}{3}.$ Notice that the sum of the weights is 1. In this kind of weighting, they will always sum to 1 unless you have made a mistake. Now let's apply the weights for A: $\dfrac{1}{3} * 40\% = 13.33\%$ for B: $\dfrac{2}{3} * 10\% = 6.67\%.$ These resulting percentages add up to the 20% that is the correct answer, but they do not measure the success rate in each individual case. Is all that clear? Now you ask about doing that for subsets of cases. You can of course do so, but what weights will you use, those based on the entire set of cases or those based on the subset? And how will you explain any of this to the users. That is not an issue of math, but an issue of presentation. I have had meetings fall apart because people did not understand the math and got lost in the explanations. Presentation is important. One simple way to present it is to calculate the success ratios for each case just the way you did, with totals at the bottom for the total number of successes and the total number of trials, but do NOT compute the average under the column of individual success ratios. Calculate the weights (but do not show them) and multiply them by the individual success ratios and show that in the next column over. Maybe call that "contribution percentage." And show the overall success ratio as a sum at the bottom of that column. You do not have to show how you got it. The reader will see it sums and not get lost in the weeds of why weighting works. Each line in that column says how much of the overall success ratio is due to that individual. Now if you want to get fancy, you can sort the table so those with the highest contributions are grouped together. If this is unclear, you can send me your email address via private message, and I can send you a spread sheet to show you what it looks like. Once we deal with subsets, things start to get hairy. Last edited by JeffM1; December 12th, 2017 at 07:42 AM. 
December 13th, 2017, 06:58 AM  #10 
Senior Member Joined: May 2016 From: USA Posts: 1,038 Thanks: 423 
John I sent you an email with an attachment. Let me know if you did not receive it.


Tags 
averages 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Please help me understand averages...  Maths Beginner  Probability and Statistics  4  October 5th, 2016 05:52 AM 
Help with averages please  Letecia  Math  3  July 13th, 2016 07:58 PM 
Calculating averages  Leon22  Algebra  3  August 6th, 2013 05:41 PM 
Averages  mswanson502  Elementary Math  2  October 3rd, 2011 06:31 AM 
Figuring Out Averages  Cyberskull  Elementary Math  2  August 3rd, 2010 07:19 PM 