Weighted Average for 3 different pass rate percentages for a 3 year period

[Meghan_M]

I need help with the below issue.


January-October 2017 Successful Attempts 684 Total Attempts 996 Pass Rate 68.7%
January-October 2018 Successful Attempts 700 Total Attempts 1,063 Pass Rate 65.9%
January-October 2019 Successful Attempts 1,110 Total Attempts 1,449 Pass Rate 76.6%

Now I need to go into excel and create a bar graph showing the pass rate % for each of the above years, but I also need to factor in the fact that the total # of attempts is different for 2017, 2018 and 2019, as there were a lot more attempts in 2019 than in 2017 and 2018. How do I factor in the weighted average to each percentage so the data is comparing apples to apples, but I still have 3 data points to show in my graph?

Thanks in advance,

Meghan

[Sam Capricci]

Welcome to the forum Meghan, please check the forum rules (particularly #1) and change your title to something more appropriate.
As for your issue, it sounds doable but you need to change the post title first.

[Sam Capricci]

Thank you for changing it. Now, for your issue it sounds like you need to use a dual axis graph. But I'm not sure if that is what you are asking.
to create a dual axis chart you go into the charts and select combo and one may be bars (your numbers by year) and the other could be line with markers and for that you select to have a secondary axis.

[Meghan_M]

I will upload an example of what I am attempting in excel. I need help in the yellow box where it says "how do I apply the weighted average to each of the pass rates".

I was thinking you would take the 2017 pass rate of 68.7% times the 28% but that gives you 19%, which does not seem like the correct answer.

[Sam Capricci]

I might consider summing the Passes (2494) and basing your weighted average off that. Take the total (3508) and divide that into the passes and that would return 71%. That will show you that your overall average was 71% while your individual 10 month period average percentages were 68.7%, 65.9% and 76.6%. Now what you want to do with that is up to you.

I'm uploading your workbook with an example (cells D8:D12) of what a weighted average of the success rate would be if applied to the total attempts. And I put a graph in that represents the three metrics.