200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello all,
Needa a help. In the inidvidual rows the percentages are calculated as 5/43+5 Grand total perenctage is 22.41. But if I take an average of all the individual rows percentage it is 24.43. Why is this differnce. How do I get both values to match?
Cases NC Percentage 43 5 10.42% 11 1 8.33% 2 2 50.00% 2 0 0.00% 9 9 50.00% 24.43% 10 0 0.00% 4 8 66.67% 9 1 10.00% 90 26 22.41%
Presume that you received 43 "good" cases and 5 "bad" cases", then 5 would be 10.42% of the total number of cases received (43 + 5 = 48), meaning 10.42% of all cases received were bad. This you correctly calculate using the formula =G2/SUM(F2:G2). The formula =F2/SUM(F2:G2) would return 89.58%, meaning that 89.58% of all cases received were good. The good + bad = 100% = 48 cases.
Note that each row is self-contained with no data from any other row used. This also goes for the last row where, in fact, 22.41% is the average percentage of "bad" cases received. The average is calculated by merging the quantities of cases. It can't be calculated by applying the average of percentages calculated from, essentially, unrelated numbers.
Perhaps this formula is what you are after.
[H2] =G2/SUM($F$2:$G$9)Here the 5 "bad" cases in G2 are calculated against the total number of received cases (90 + 26 = 116) and the results in H2:H9 would add up to 22.41%
you are comparing averages of 2 different things. The first is a Compound Percent formula; The 2nd is a simple sum of percentages. Also, you are only considering Cases as your Numerator, but you use both cases and NC in the Denominator.
You have 2 numbers (columns) and you are only getting the percentage of the 1st column. if you take both columns and get their percentages (they will add up to 1) and sum all 9 of your values for both columns, then divide by 9 you will get 1.
your formula is flawed
Not sure what NC even is....