Formula Help Needed for Progress-to-Target Calculation

Hi everyone,
I am in need of someone's Excel genious. I need to calculate the below Progress to Target percentage for 10 areas, but am having trouble creating a formula. Please take a look at my below information and help if able. THANKS SOOOO MUCH IN ADVANCE!! (the dashed line is supposed to equal a division line)

Baseline - Result
----------------- x 100 = OQL Progress-to-Target (%)
Baseline - Target

Area| Actual| Target| Baseline| Progress to Target (%)
1 | 0.140 | 0.138 | 0.067 | ?
2 | 0.025 | 0.044 | 0.020 | ?
3 | 0.120 | 0.125 | 0.114 | ?
4 | 0.015 | 0.016 | 0.022 | ?
5 | 0.202 | 0.306 | 0.213 | ?
6 | 0.014 | 0.034 | 0.016 | ?
7 | 0.036 | 0.068 | 0.034 | ?
8 | 0.016 | 0.006 | 0.005 | ?
9 | 0.199 | 0.236 | 0.233 | ?
10 | 0.000 | 0.010 | 0.000 | ?

RE: Progress-to-Target Calculation

What do you mean by baseline? And how would you like this factored into the formula?

RE: Progress-to-Target Calculation

This baseline is set from my previous year's outgoing quality.

The formula that I was given was:

baseline (minus) actual
------------------------ multiplied by 100
baseline (minus) target

RE: Progress-to-Target Calculation

This baseline is set from my previous year's outgoing quality.

The formula that I was given was:

baseline (minus) actual
------------------------ multiplied by 100
baseline (minus) target

Progress-to-Target Calculation

This is the exact conversion of your question into excel, test and let me know.


=SUM((D2-B2)/(D2-C2))*100

Re: Progress-to-Target Calculation

I have entered the formula. But not sure it's correct. Please tell me what you think.

 

 

  Actual Target Baseline Progress to Target (%)
1 0.140 0.138 0.067 10281.7%
2 0.025 0.044 0.020 2083.3%
3 0.120 0.125 0.114 5454.5%
4 0.015 0.016 0.022 11666.7%
5 0.202 0.306 0.213 -1123.0%
6 0.014 0.034 0.016 -1111.1%
7 0.036 0.068 0.034 588.2%
8 0.016 0.006 0.005 110000.0%
9 0.199 0.236 0.233 -133076.9%
10 0.000 0.010 0.000 0.0%
         
OQL Progress-to-Target (%) = Baseline - Result x  100
Baseline - Target

Progress-to-Target Calculation

Hi,

Sorry about the delay!

It appears you have converted the cells to a percentage, so either convert those back to numbers or remove the *100 from the formula. You should end up with:

Actual Target Baseline Progress to Target (%)
1 0.14 0.138 0.067 102.82
2 0.025 0.044 0.02 20.83
3 0.12 0.125 0.114 54.55
4 0.015 0.016 0.022 116.67
5 0.202 0.306 0.213 -11.83
6 0.014 0.034 0.016 -11.11
7 0.036 0.068 0.034 5.88
8 0.016 0.006 0.005 1100.00
9 0.199 0.236 0.233 -1133.33
10 0 0.01 0 0.00

Progress-to-Target Calculation

Sorry that looks messy, the result column is:

%
102.82
20.83
54.55
116.67
-11.83
-11.11
5.88
1100.00
-1133.33
0.00

Matt

Re: Progress-to-Target Calculation

Great! Thank you so much for all your help!

Progress-to-Target Calculation

Anytime.

Have to be honest though, despite not knowing what the data is fully, it doesn't appear to show anything...

Let me know if you have any other problems.

Webby2013