Excel formula to calculate the Gap to Goal and % Achievement

[braunReivn]

Hi,

I'm new here and this is my first post. I hope I am not violating any rules or something.

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 a formula that will calculate the Gap to Goal and the % Achievement of the employee.

Also, aside from the formula, I need to show the Gap to Goal and % achievement result in a pivot table.

I hope I'm able to explain it clearly.

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

GapToGoal.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N
2	Sample data								Desired output
3	Name	Metric Type	Goal	Actual	Gap to Goal	%Attainment			Row Labels	Gap to Goal	% Attainment
4	EmpA	Positive	75%	95%					EmpA
5		Negative	1488	2500					EmpB
6		Positive	$255.00	$0.25					Grand Total
7	EmpB	Positive	80%	50%
8		Negative	25	0.05
9		Positive	$255.00	$400.00
10
Sheet1

Thank you in advance.