Offset Processes

P

Pestomania

Active Member
Joined
May 30, 2018
Messages
332
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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

D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
318
etaf
etaf
apgmin
  • Question Question
Match and select
Replies
1
Views
202
Cubist
Cubist
J
  • Question Question
Index, Match, Large based on two Criteria
Replies
3
Views
114
Fluff
Fluff
S
  • Locked
  • Question Question
IF + VLOOKUP return result within date range
Replies
3
Views
70
Joe4
Joe4
S
  • Solved
IF and VLOOKUP with Dates values
Replies
7
Views
176
StaceyVECL
S

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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