Calculate Gap to Goal and % Achievement

Hi, again!

I'm not sure if my post earlier was posted or not. So pls pardon should there be a double post.

I hope somebody can help with this simple problem.

I have a table of metric type, target, actual.

Name Metric Type Target Actual
EmpA Positive 45% 0%
Negative 70% 100%
Positive 17% 37.91%
EmpB Positive 45% 55%
Negative 70% 0%
Positive 17% 90%

For each metric type, I need to calculate the Gap to Goal and the % Achievement of the employee.

*Negative = the lower the actual score is, the better.

Also, the data will be presented using pivottable. Should the Gap to Goal and %Achievement be shown as Sum or Average?

Pardon if the sample data is not in excel form, I am using a mobile phone.

Thank you in advance.

Thanks Ali for taking the time to reply.

I have updated my profile with my current Excel version (Excel 2016). I am using Windows.

Attached here is a sample workbook.

Looking forward to getting help.

Hi,

I'm not sure I understand what you mean.


So, in the sample workbook, I have a table (on the left side) and a pivot table (on the right)

Basically, what I need is to get the Gap to Goal and % Achievement and show it in pivot table.

I am using Windows and Excel 2016.

I hope this answers your question.

Thanks.

Is the subject related with Google Sheets?