Planned Vs Actual Weeks behind or ahead calculation

[Pedro Fernandes]

Hi there,
On a simple weekly Planned vs Actual cumulative value comparison, I would like to calculate how many weeks behind or ahead the actual is
The caveat is I would like to calculate it including a decimal case, ie I would like to know not only how many cells above or below the planned value would be but also how far in between cells it would be. (Ex: -3.8 weeks behind).
Sample file with column attached with what the result should be

[Richard Buttrey]

Would you explain the arithmetic behind your required results? i.e. which cells contribute to the results.

[JohnTopley]

Appears to be

=(E2-D2)/10

???

[Pedro Fernandes]

Can not find option to delete post on edit window.

[Pedro Fernandes]

Can not find option to delete post on edit window.

[Pedro Fernandes]

Would you explain the arithmetic behind your required results? i.e. which cells contribute to the results.

It only needs to compare Column E vs D.
For each cell of column F I would like it to say the same Row on column E how many weeks ahead or behind it is from Column D.

[Pedro Fernandes]

Appears to be

=(E2-D2)/10

???

That would only work for the weekly planned 10 units every week of the example (exactly to make the expected values easier to list).
I am looking for a formula that calculates regardless of the planned values.

[PietBom]

This might be the formula you need:

Please Login or Register  to view this content.

[Pedro Fernandes]

Unfortunately that will only work if weekly planned values are always the same.

[AliGW]

So you keep saying, yet the provided sample has every week at a planned value of 10!!! How did you expect your helpers to deduce that this was NOT a fixed value?

Please provide a more realistic sample dataset with expected results manually calculated. Thanks.

[Pedro Fernandes]

You are right Ali. Should have updated the file after the first replies.
Attached revised planned and actuals with variability on both.

Thanks for the input.

[PietBom]

My formula from #8 is working, but now I am confused by your expected results...

[Pedro Fernandes]

My formula from #8 is working, but now I am confused by your expected results...

Thanks for the reply Pitbom.
It does not work on variable planned values.
I have attached a file with an explanation in blue on how the results are calculated and a graph illustrating it.

[kvsrinivasamurthy]

Attached file could not be opened. Attach the file once again. In the file you show for few rows Manually show the calculations in detail, how you are getting the expected results.

[AliGW]

Which file? They all open for me ...

[kvsrinivasamurthy]

Post# 13 file was not opening when I tried. Now it is Ok.

[PietBom]

I have attached a file with an explanation in blue on how the results are calculated and a graph illustrating it.

Sorry Pedro, I read your explanation in blue a few times, but could not put my finger on it.
To me it is means: Weeks behind schedule is the time difference for the actual produced items and the time that it would it with the planned productivity (Qty / Time)
In the attachment I made a bar chart to make it clear.
I stil don't understand your explanation. Now you mention de middle of the week, which was not in your original question. And I don't understand your statement that it only works with a fixed quantity. In your second I see different quantities planned and my formula could easily be pulled down.
Can you explain a bit more how it should work step by step to see how you come to your expected results.

[kvsrinivasamurthy]

Pl see file.
NOTE: columns H, I, J and K are helper columns

ARRAY formulas in H and I columns
In H2

=MAX(IF($D$2:$D$22<E2,$D$2:$D$22,""))

In I2

=MIN(IF($D$2:$D$22>E2,$D$2:$D$22,""))

These are normal formulas.
In J2

=INDEX($A$2:$A$22,MATCH(H2,$D$2:$D$22,0))

In K2

=INDEX($A$2:$A$22,MATCH(I2,$D$2:$D$22,0))

In L2

=(K2-((K2-J2)*(I2-E2)/(I2-H2))-A2)/7

Then all copied down.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[PietBom]

@kvsrinivasamurthy,
I must admit, your approach is better.
Even with a small change it gives the same results as the TS expected:

=MAX(IF($D$2:$D$22< = E2,$D$2:$D$22,""))

[Pedro Fernandes]

Pl see file.
NOTE: columns H, I, J and K are helper columns

ARRAY formulas in H and I columns
In H2

=MAX(IF($D$2:$D$22<E2,$D$2:$D$22,""))

In I2

=MIN(IF($D$2:$D$22>E2,$D$2:$D$22,""))

These are normal formulas.
In J2

=INDEX($A$2:$A$22,MATCH(H2,$D$2:$D$22,0))

In K2

=INDEX($A$2:$A$22,MATCH(I2,$D$2:$D$22,0))

In L2

=(K2-((K2-J2)*(I2-E2)/(I2-H2))-A2)/7

Then all copied down.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

Thank you kvsrinivasamurthy .
Only one cell seems to show a small difference on line 17.

Outside of this forum got a formula that seems to work well and without use of auxiliary columns.
I have attached the file with the formula if it useful for anyone here.

Thank you very much for everyone's assistance.

[kvsrinivasamurthy]

It is a combination of all formulas. To make formula simpler I used helper columns.
In Formula of L2 If K2,i2,J2,H2 are replaced by respective formula you will get result. But formula is lengthy.
Ok .Happy your problem solved.

[PietBom]

@pedro,
Did you ever think about a VBA solution ?
The advantage for this question is that the calculations and formulas can be complex and you will not see helper formulas.
the complexity is handeled in the VBA code and the user will not see the code.
attached an example with VBA