Offset Processes

P

Pestomania

Active Member
Joined
May 30, 2018
Messages
292
Office Version
  1. 365
Platform
  1. Windows
How can I create a dynamic formula to "offset" the input based on how many months to start a process before delivery.

Cell Q14 is determined by:

This offset is determined by identifying the sum of process 1 - 3 and determining how many days in months. So Process 1 would need to start 8 months (cell E2) before delivery in Aug '23.
Process 2 will take the sum of Process 2-3, etc.

Book4
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
1ProgramProcess 1Process 2Process 3Total MonthsHours / moEfficiencyJan '23Feb '23Mar '23Apr '23May '23Jun '23Jul '23Aug '23Sep '23Oct '23Nov '23Dec '23Jan '24Feb '24Mar '24Apr '24May '24Jun '24Jul '24Aug '24Sep '24Oct '24Nov '24Dec '24
2Program 152661128Process 11600.84375Program 1488488488488444444444448
3Program 2891229610Process 21600.84625Program 2112112112112122122122122
4Program 36015011511Process 31600.86Program 3171734173417173417341734171717171734171717171734
5Program 41479013712Process 41600.86Program 4022022022022022222222222
6Program 574411148Process 51600.85Program 5101010101010101010101015055555055555
7Program 611014212813Process 61600.8Program 601501501515015015150150150151501501515
8Program 7719813410Process 71600.8Program 72020202020202020202020300010001000100010
9Program 812651158Process 81600.8Program 800707007070777777147777714
10Program 9201061218Process 91600.8Program 9010101010101010101010100100100101001001010
11
12
13Jan '23Feb '23Mar '23Apr '23May '23Jun '23Jul '23Aug '23Sep '23Oct '23Nov '23Dec '23Jan '24Feb '24Mar '24Apr '24May '24Jun '24Jul '24Aug '24Sep '24Oct '24Nov '24Dec '24
14PROCESS 1Program 1418.5418.5216418.5418.5216418.5418.5216418.5418.5216216216216216216216216216216216418.5216
15Program 294.594.594.518994.594.518994.594.518994.518918994.518918994.518918994.518918994.5189
16Program 3205220521026205210261026205210262052102620521026102610261026102620521026102610261026102620521026
17Program 402970297297029729702972972972972972972972972972972970297297297
18Program 5742.5742.5742.5742.5742.5742.5742.5742.5742.51120.50378378378378378037837837837837800
19Program 61660.501660.51660.501660.501660.51660.501660.501660.501660.51660.501660.501660.51660.5000
20Program 7143114311431143114311431143114311431213300715.500715.500715.500715.50715.5
21Program 889100891089108918918918918918911768.58918918918918911768.5008910
22Program 9202.5202.5202.5202.5202.5202.5202.5202.5202.5202.5202.5202.50202.50202.50202.5202.50202.50202.5202.5
Sheet1
Cell Formulas
RangeFormula
Q1:AZ1Q1=TEXT(DATE(2023,SEQUENCE(,12*COUNTIF(A1:D1,"<>'")-12),1),"mmm 'yy")
P2:P10P2=UNIQUE(A2:A10)
E2:E10E2=SUM(B2:D2)/30
P14:P22P14=UNIQUE(A2:A10)
Q14,Q18,Q21:Q22Q14=ROUNDUP((X2*VLOOKUP($P14,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
R14:AN14,R16:AN16R14=ROUNDUP((S2*VLOOKUP($P14,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
Q15:Q16,Q20Q15=ROUNDUP((Z3*VLOOKUP($P15,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
R15:AN15,R18:AN18,R20:AN20R15=ROUNDUP((T3*VLOOKUP($P15,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
Q17Q17=ROUNDUP((AC5*VLOOKUP($P17,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
R17:AN17,R21:AN21R17=ROUNDUP((V5*VLOOKUP($P17,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
Q19Q19=ROUNDUP((AD7*VLOOKUP($P19,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
R19:AN19R19=ROUNDUP((U7*VLOOKUP($P19,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
R22:AN22R22=ROUNDUP((R10*VLOOKUP($P22,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
Dynamic array formulas.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Pestomania said:
How can I create a dynamic formula to "offset" the input based on how many months to start a process before delivery.
Click to expand...
Still trying To understand your requirements.

Where is the date of delivery? - I couldn't find it
Where exactly you want "offset" thing? - Q14:AN19? Please explain.
 
Upvote 0
What generates the numbers in Q2:AO10 ? It's difficult to find the logic, when I look in the first part of the formulas in Q14:AO22
 
Upvote 0
SanjayGMusafir said:
Still trying To understand your requirements.

Where is the date of delivery? - I couldn't find it
Where exactly you want "offset" thing? - Q14:AN19? Please explain.
Click to expand...
Hi Sanjay,

Date of delivery is cell Q1:AN1. It is just the month, for purposes of this exercise, we could use the end of the month as the date of delivery.

Q14:AN19, sorry for that not being clear.

Best case is that Q14 would look at how long the amount of time to complete the remaining process takes (sum of B2:D2) and go that many months ahead in the delivery table, which is (X2) because the "offset" would be 8 months.
 
Upvote 0
ebea said:
What generates the numbers in Q2:AO10 ? It's difficult to find the logic, when I look in the first part of the formulas in Q14:AO22
Click to expand...
In this example, I made those numbers static (because the formula is part of another thread, didn't want to get turned off). In case you want to see that and see if you can help with the problem I am having: Update Equally Distributed Formula.

Thank you.
 
Upvote 0
As I was working with your sample data, I found few discrepancies that need to be understood.

In cell Q14 you multiply with X2 and in adjacent cell (R14) it becomes S2 instead of Y2
Similarly in row below it becomes Z3 instead of X3

There are many more of such kind which are difficult to understand.

There has be some pattern to tell excel what and where to look for under a particular circumstance...
 
Upvote 0
SanjayGMusafir said:
As I was working with your sample data, I found few discrepancies that need to be understood.

In cell Q14 you multiply with X2 and in adjacent cell (R14) it becomes S2 instead of Y2
Similarly in row below it becomes Z3 instead of X3

There are many more of such kind which are difficult to understand.

There has be some pattern to tell excel what and where to look for under a particular circumstance...
Click to expand...
You are right. I had made an edit in column Q and must not have dragged it to the right. I have added a change here:

I am very sorry for messing that up. Does this help?

Book4
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
1ProgramProcess 1Process 2Process 3Total MonthsHours / moEfficiencyJan '23Feb '23Mar '23Apr '23May '23Jun '23Jul '23Aug '23Sep '23Oct '23Nov '23Dec '23Jan '24Feb '24Mar '24Apr '24May '24Jun '24Jul '24Aug '24Sep '24Oct '24Nov '24Dec '24
2Program 152661128Process 11600.84375Program 1488488488488444444444448
3Program 2891229610Process 21600.84625Program 2112112112112122122122122
4Program 36015011511Process 31600.86Program 3171734173417173417341734171717171734171717171734
5Program 41479013712Process 41600.86Program 4022022022022022222222222
6Program 574411148Process 51600.85Program 5101010101010101010101015055555055555
7Program 611014212813Process 61600.8Program 601501501515015015150150150151501501515
8Program 7719813410Process 71600.8Program 72020202020202020202020300010001000100010
9Program 812651158Process 81600.8Program 800707007070777777147777714
10Program 9201061218Process 91600.8Program 9010101010101010101010100100100101001001010
11
12
13Jan '23Feb '23Mar '23Apr '23May '23Jun '23Jul '23Aug '23Sep '23Oct '23Nov '23Dec '23Jan '24Feb '24Mar '24Apr '24May '24Jun '24Jul '24Aug '24Sep '24Oct '24Nov '24Dec '24
14PROCESS 1Program 1418.5418.5216418.5418.5216216216216216216216216216216216418.5216216216216216216216
15Program 294.594.518994.518918994.518918994.518918994.518918994.518918994.518918994.5189189
16Program 3205210262052102610261026102610262052102610261026102610262052102620522052205220522052102620522052
17Program 40297297297297297297297297297297297029729729702972972970297297297
18Program 5742.5742.5742.5742.51120.503783783783783780378378378378378000003780
19Program 61660.501660.501660.51660.501660.501660.51660.5000000000001660.50
20Program 714311431213300715.500715.500715.500715.50715.50715.50715.50715.50
21Program 8891089108918918918918918911768.58918918918918911768.500891089100
22Program 9202.5202.5202.5202.5202.50202.50202.50202.5202.50202.50202.5202.50202.50202.50202.50
Sheet1
Cell Formulas
RangeFormula
Q1:AZ1Q1=TEXT(DATE(2023,SEQUENCE(,12*COUNTIF(A1:D1,"<>'")-12),1),"mmm 'yy")
P2:P10P2=UNIQUE(A2:A10)
E2:E10E2=SUM(B2:D2)/30
P14:P22P14=UNIQUE(A2:A10)
Q14:AN14,Q18:AN18,Q21:AN22Q14=ROUNDUP((X2*VLOOKUP($P14,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
Q15:AN16,Q20:AN20Q15=ROUNDUP((Z3*VLOOKUP($P15,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
Q17:AN17Q17=ROUNDUP((AC5*VLOOKUP($P17,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
Q19:AN19Q19=ROUNDUP((AD7*VLOOKUP($P19,$A$2:$B$10,2,FALSE))/(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE))),1)*(VLOOKUP($O$14,$J$1:$L$10,2,FALSE)*(VLOOKUP($O$14,$J$1:$L$10,3,FALSE)))
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Solver not finding solution
Replies
0
Views
202
millhouse123
M
gtd526
  • Question
search and answer only ODD rows
Replies
6
Views
145
gtd526
gtd526
J
  • Solved
Dynamic Tier System
2
Replies
18
Views
772
Dave Patton
D
P
  • Solved
Update Equally Distributed Formula
2
Replies
19
Views
599
felixstraube
F
J
  • Question
Formula is changing after getting new data?
Replies
7
Views
327
JamesRo
J

Forum statistics

Threads
1,215,072
Messages
6,122,966
Members
449,094
Latest member
Anshu121

Share this page

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