Timeline Conditions

Kamran01

Board Regular
Joined
Feb 10, 2017
Messages
86
Dear Experts,

I need your help regarding timeline, i have below data from style to last date and i want to calculate day wise quantities according to below criteria.
1.5 Change over days are constant and days required are calculated =4000/800 >> 5 +C/O days >> 6.5 days.

1st Day Plan: 25% of P/D target2nd Day Plan: 50% of P/D target
3rd Day Plan: 75% of P/D target
rest all days: 100% of P/D target
Last day:Remaining Balance Percentage from plan qty

Style #Plan QtyP/D Tgt.C/O DaysDays Req.First PlanLast Date19/420/422/423/424/425/426/427/429/430/4
AVA-0652NM40008001.56.5019/426/4200400600800800800400
AVA3501F112009001.52.827/430/4225450525

<tbody>
</tbody>
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
try

Book1
ABCDEFGHIJKLMNOPQ
1Style #Plan QtyP/D Tgt.C/O DaysDays Req.First PlanLast Date19-Apr20-Apr22-Apr23-Apr24-Apr25-Apr26-Apr27-Apr29-Apr30-Apr
2AVA-0652NM40008001.56.519-Apr26-Apr200400600800800800400   
3AVA3501F112009001.52.827-Apr30-Apr       225450675
Sheet2
Cell Formulas
RangeFormula
H2=IF($F2=H$1,$C2*0.25,IF(NETWORKDAYS($F2,H$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,H$1)+1=3,$C2*0.75,IF($G2=H$1,$B2-(SUM($G2:G2)-$G2),IF(AND(H$1<$G2,H$1>=$F2),$C2,"")))))
H3=IF($F3=H$1,$C3*0.25,IF(NETWORKDAYS($F3,H$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,H$1)+1=3,$C3*0.75,IF($G3=H$1,$B3-(SUM($G3:G3)-$G3),IF(AND(H$1<$G3,H$1>=$F3),$C3,"")))))
I2=IF($F2=I$1,$C2*0.25,IF(NETWORKDAYS($F2,I$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,I$1)+1=3,$C2*0.75,IF($G2=I$1,$B2-(SUM($G2:H2)-$G2),IF(AND(I$1<$G2,I$1>=$F2),$C2,"")))))
I3=IF($F3=I$1,$C3*0.25,IF(NETWORKDAYS($F3,I$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,I$1)+1=3,$C3*0.75,IF($G3=I$1,$B3-(SUM($G3:H3)-$G3),IF(AND(I$1<$G3,I$1>=$F3),$C3,"")))))
J2=IF($F2=J$1,$C2*0.25,IF(NETWORKDAYS($F2,J$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,J$1)+1=3,$C2*0.75,IF($G2=J$1,$B2-(SUM($G2:I2)-$G2),IF(AND(J$1<$G2,J$1>=$F2),$C2,"")))))
J3=IF($F3=J$1,$C3*0.25,IF(NETWORKDAYS($F3,J$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,J$1)+1=3,$C3*0.75,IF($G3=J$1,$B3-(SUM($G3:I3)-$G3),IF(AND(J$1<$G3,J$1>=$F3),$C3,"")))))
K2=IF($F2=K$1,$C2*0.25,IF(NETWORKDAYS($F2,K$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,K$1)+1=3,$C2*0.75,IF($G2=K$1,$B2-(SUM($G2:J2)-$G2),IF(AND(K$1<$G2,K$1>=$F2),$C2,"")))))
K3=IF($F3=K$1,$C3*0.25,IF(NETWORKDAYS($F3,K$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,K$1)+1=3,$C3*0.75,IF($G3=K$1,$B3-(SUM($G3:J3)-$G3),IF(AND(K$1<$G3,K$1>=$F3),$C3,"")))))
L2=IF($F2=L$1,$C2*0.25,IF(NETWORKDAYS($F2,L$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,L$1)+1=3,$C2*0.75,IF($G2=L$1,$B2-(SUM($G2:K2)-$G2),IF(AND(L$1<$G2,L$1>=$F2),$C2,"")))))
L3=IF($F3=L$1,$C3*0.25,IF(NETWORKDAYS($F3,L$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,L$1)+1=3,$C3*0.75,IF($G3=L$1,$B3-(SUM($G3:K3)-$G3),IF(AND(L$1<$G3,L$1>=$F3),$C3,"")))))
M2=IF($F2=M$1,$C2*0.25,IF(NETWORKDAYS($F2,M$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,M$1)+1=3,$C2*0.75,IF($G2=M$1,$B2-(SUM($G2:L2)-$G2),IF(AND(M$1<$G2,M$1>=$F2),$C2,"")))))
M3=IF($F3=M$1,$C3*0.25,IF(NETWORKDAYS($F3,M$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,M$1)+1=3,$C3*0.75,IF($G3=M$1,$B3-(SUM($G3:L3)-$G3),IF(AND(M$1<$G3,M$1>=$F3),$C3,"")))))
N2=IF($F2=N$1,$C2*0.25,IF(NETWORKDAYS($F2,N$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,N$1)+1=3,$C2*0.75,IF($G2=N$1,$B2-(SUM($G2:M2)-$G2),IF(AND(N$1<$G2,N$1>=$F2),$C2,"")))))
N3=IF($F3=N$1,$C3*0.25,IF(NETWORKDAYS($F3,N$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,N$1)+1=3,$C3*0.75,IF($G3=N$1,$B3-(SUM($G3:M3)-$G3),IF(AND(N$1<$G3,N$1>=$F3),$C3,"")))))
O2=IF($F2=O$1,$C2*0.25,IF(NETWORKDAYS($F2,O$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,O$1)+1=3,$C2*0.75,IF($G2=O$1,$B2-(SUM($G2:N2)-$G2),IF(AND(O$1<$G2,O$1>=$F2),$C2,"")))))
O3=IF($F3=O$1,$C3*0.25,IF(NETWORKDAYS($F3,O$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,O$1)+1=3,$C3*0.75,IF($G3=O$1,$B3-(SUM($G3:N3)-$G3),IF(AND(O$1<$G3,O$1>=$F3),$C3,"")))))
P2=IF($F2=P$1,$C2*0.25,IF(NETWORKDAYS($F2,P$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,P$1)+1=3,$C2*0.75,IF($G2=P$1,$B2-(SUM($G2:O2)-$G2),IF(AND(P$1<$G2,P$1>=$F2),$C2,"")))))
P3=IF($F3=P$1,$C3*0.25,IF(NETWORKDAYS($F3,P$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,P$1)+1=3,$C3*0.75,IF($G3=P$1,$B3-(SUM($G3:O3)-$G3),IF(AND(P$1<$G3,P$1>=$F3),$C3,"")))))
Q2=IF($F2=Q$1,$C2*0.25,IF(NETWORKDAYS($F2,Q$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,Q$1)+1=3,$C2*0.75,IF($G2=Q$1,$B2-(SUM($G2:P2)-$G2),IF(AND(Q$1<$G2,Q$1>=$F2),$C2,"")))))
Q3=IF($F3=Q$1,$C3*0.25,IF(NETWORKDAYS($F3,Q$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,Q$1)+1=3,$C3*0.75,IF($G3=Q$1,$B3-(SUM($G3:P3)-$G3),IF(AND(Q$1<$G3,Q$1>=$F3),$C3,"")))))
 
Upvote 0
Actually you should use this formula instead.
Code:
=IF($F2=H$1,$C2*0.25,IF(NETWORKDAYS.INTL($F2,H$1,"0000001")=2,$C2*0.5,IF(NETWORKDAYS.INTL($F2,H$1,"0000001")=3,$C2*0.75,IF($G2=H$1,$B2-(SUM($G2:G2)-$G2),IF(AND(H$1<$G2,H$1>=$F2),$C2,"")))))
 
Upvote 0
That is the list of days Monday-Sunday 0=workday 1=not a workday. Since we are not using the normal two day weekend we have to use a custom work schedule.
 
Upvote 0
Hi All,

First of all really thanks to provide such an amazing solution to us, moreover, having the same problem and want a slight change in it, here is the condition to be applied in order to get the desire result.

Condition - If previous style is same so first output will be taken at 100% (on wards will end) instead of 25%.

TIA
 
Upvote 0
Hi All,

First of all really thanks to provide such an amazing solution to us, moreover, having the same problem and want a slight change in it, here is the condition to be applied in order to get the desire result.

Condition - If previous style is same so first output will be taken at 100% (on wards will end) instead of 25%.




TIA


One more thing to add in above, if the previous style is same/repeat and its per day capacity is 1200 but the last out put was 600 on dated 24-April then the formula will show the rest 600 pieces underneath the same date to fulfill the capacity and from next date it will show 1200 which is the actual capacity. hope it makes sense.
 
Upvote 0
Could you post a sample of your data and the results you want.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top