Sum monthly percentages into quarterly percentages

Hi All,

I am currently working on some KPIs for my company and they come in two modes. Operational and Strategic. The Operational (or Management) KPIs are recorded monthly. The Strategic (or Board) KPIs are recorded Quarterly.

Some KPIs are just numbers of monetary values so these are easy to some up per Quarter. The problem I am having is when they are a percentage. How do you add three months worth of percentages? The KPI spreadsheet has two sheets one for the monthly Operational KPIs and one sheet for the Strategic quarterly KPIs.

In the past I just took the average of the 3 months of that particular quarter and used this as the Quarterly figure. The problem I feel that if I average every 3 months and use this for my Q1 to Q4 figures and then as the grand total for Q1-Q4 I use an average formula for that then I am taking an average of an average.

So I guess my question is how do I calculate quarterly figures for percentages. Our Year starts from April to March; so April, May June will be Q1. So if the figures for those months are 99%, 98%, 99% then the average for Q1 would be 99%. Is the best way to calculate a quarterly percentage?

I would really appreciate some advice to my issue.

I am using Office 2007.

Thanks.

I would get the background data used to calculate the percentages, by month. then calculate a total for the quarter.

Averaging the periods assumes they are all weighted the same, which I will speculate is NOT the case.

Hi Mike,

Thanks for your reply.

If I understand you correctly are you saying that I should ask the person who supplies the source data to re-run the report based on the whole of the quarter period? You see when I receive the monthly figures I get them as a percentage and then I collate the data. However I could ask for a percentage over a quarter.

Thanks again for your help. I really appreciate it.

Hi,

You are correct when you surmise that averaging averages would be wrong. You can't average averages. You need to work with the original data on which the averages were based.

Take for instance 3 2s, total 6 average = 2, and 4 10s total 40 average = 10. The average of 2 & 10 is 6, but the average of the seven values is 6.57

Hi Richard,

Thank you so much for your response. So I guess the answer must be to go back to the source data and ask them for the percentage over the 3 month period? Would this be correct?

Yes indeed.

Don't forget though that you can simplify analysis with a Pivot Table. Once you have the data it should only be a minute or two's work to get your results.

Thanks Richard and Mike your help and advice is greatly appreciated.