Predicted Timescale vs Actual Timescale

[SPalmerUK]

Hi,

Probably an easy question but the answer eludes me.

I have a target that needs to be hit - 48,185.

Weekly, I need to be hitting 2,878.

So, 48,185 / 2,878 = 16.74. This would be the expected number of weeks it should take to hit the overall target.

Now if my actual is less than 2,878 I need to see how much longer it will take to hit the overall target.

Ideas?

Thanks

 

[Momentman]

If your actual is 2000 for example, then the Extension would be

=48185/2000 - 48185/2878

=7.4

Depending on how your sheet is set-up, you can write to formulas to reflect the above

 

[SPalmerUK]

Ok, works fine for the first week, but if I add in a second week (assuming both weeks hit the 2878) i get -8.37.

2 weeks of hitting the weekly target wouldn't halve my overall time frame.

 

[Momentman]

Can you show a small set-up of your sheet and what you really want to achieve?

if I do 2000 in the first week and 2000 in the second week, what should be the extra time required(mathematically) based on your computation logic

 

[SPalmerUK]

[TABLE="width: 1172"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Week 1[/TD]
[TD][/TD]
[TD="colspan: 2"]Week 2[/TD]
[TD][/TD]
[TD="colspan: 2"]Week 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]16/10/2017 to 22/10/2017[/TD]
[TD][/TD]
[TD="colspan: 2"]23/10/2017 to 29/10/2017[/TD]
[TD][/TD]
[TD="colspan: 2"]30/10/2017 to 05/11/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Target[/TD]
[TD]Achieved[/TD]
[TD]%[/TD]
[TD]Target[/TD]
[TD]Achieved[/TD]
[TD]%[/TD]
[TD]Target[/TD]
[TD]Achieved[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Trucks(Cumulative)[/TD]
[TD="align: right"]2878[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]5677[/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]8555[/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Hauliers[/TD]
[TD="align: right"]115[/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]230[/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]345[/TD]
[TD][/TD]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Trucks(Weekly)[/TD]
[TD="align: right"]2878[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]2878[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]2878[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Current Trucks (Benchmark)[/TD]
[TD] 51,815[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Target Trucks[/TD]
[TD] 100,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trucks to get[/TD]
[TD] 48,185[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Weekly Target[/TD]
[TD] 2,878[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trucks Signed[/TD]
[TD] 79[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]% Signed[/TD]
[TD="align: right"]0.16%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trucks Remaining[/TD]
[TD] 48,106[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]% Remaining[/TD]
[TD="align: right"]99.84%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Weekly Target Hauliers[/TD]
[TD="align: right"]115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Current Hauliers (Benchmark)[/TD]
[TD] 2,076[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Expected Weeks[/TD]
[TD="align: right"]16.74[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Actual Weeks[/TD]
[TD="align: right"]593.19[/TD]
[TD][/TD]
[TD][/TD]
[TD] 46,862.34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


These are the first three weeks of the spreadsheet - it's the actual weeks cell that i'm working on

 

[SPalmerUK]

[TABLE="width: 596"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Week 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]16/10/2017 to 22/10/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Target[/TD]
[TD]Achieved[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Trucks(Cumulative)[/TD]
[TD="align: right"]2878[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Hauliers[/TD]
[TD="align: right"]115[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Trucks(Weekly)[/TD]
[TD="align: right"]2878[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Current Trucks (Benchmark)[/TD]
[TD] 51,815[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Target Trucks[/TD]
[TD] 100,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trucks to get[/TD]
[TD] 48,185[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Weekly Target[/TD]
[TD] 2,878[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trucks Signed[/TD]
[TD] 79[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]% Signed[/TD]
[TD="align: right"]0.16%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trucks Remaining[/TD]
[TD] 48,106[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]% Remaining[/TD]
[TD="align: right"]99.84%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Weekly Target Hauliers[/TD]
[TD="align: right"]115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Current Hauliers (Benchmark)[/TD]
[TD] 2,076[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Expected Weeks[/TD]
[TD="align: right"]16.74[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Actual Weeks[/TD]
[TD="align: right"]593.19[/TD]
[TD][/TD]
[TD][/TD]
[TD] 46,862.34[/TD]
[/TR]
</tbody>[/TABLE]

 

[SPalmerUK]

The solution I needed was:

B10/AVERAGEIFS(D4:DD4,D3:DD3,"Achieved",D4:DD4,">0")