Excel Formula Question

Question

I'm trying to work on a score metric to determine a combination of my KPI's I have to measure and report on each month.

The score metric for start of the year (Jan 21) would be 50 with a target of 100 by the end of the year (Dec 21) if all targets were met. It could be higher if targets exceed.

Each KPI below has a baseline weighting score of 10.

What I want to is to each month provide combining the KPI's is to use one metric to track success and report on. So far example in February is the score is 45, things aren't going well or in March it might be 90 and almost hitting the proposed target.

What I want to work out is a formula to calculate the value in the 'Feb score' column.

For example for KPI #1.

The baseline target starting in January is 10,000 (baseline score of 10) with a proposed 50% increase target by December meaning the success score would be 20.

So in February I have a value of 11,000 which means there was an increase of 10%.

I can't work out what the formula would be to determine the score in February. It would be around 12 I would assume but I don't know the equation.

Thank you for any assistance.

	KP1 Jan Baseline	Jan Baseline score	Dec KPI target	Dec Proposed score	Feb KPI score	Feb score??? <br>What is the value
KPI #1	10,000	10	15,000	20	11,000	What is the formula here??
KPI #2	20,000	10	40,000	20	10,000
KPI #3	5,000	10	7,500	20	5,0000
KPI #4	2,000	10	2,200	20	2,200
KPI #5	1,000	10	2,000	20	3,000
	Baseline metric	50		100 (target)

Answer 1

G2: =(F2-B2)/(D2-B2)*(E2-C2)+C2

drag down.

Answer 2

Thank you kindly. Much appreciated. Worked well.