Duration formula tied to allocation percentage
I am trying to create the correct formula tied to the percentage allocated for each task for the full duration
For example the total task has a duration of 54 Days. The Create task allocated .75 of the total duration (which is the same cell for each task) but I am not sure what formula should be put in the allocation duration cell. I tried different formulas(=Alloc.@row *[Duration]37; =Value([Alloc.]@row) * Value([Duration]@37]) and nothing worked. .75 of the total 54 days(this is in the Duration cell Row 37 should = 40.5. Thanks in advance for the formula help...I feel like it is simple but can't get it to work and need this formula for 150 different batches
Answers

Hi @Beth Klineman,
this formula worked for me: =[Alloc.]@row * duration$2
There is no need for those brackets here "*[Duration]", but certainly in "Alloc.@row ".
In you second example "[Alloc.]@row" was good, but "[Duration]@37]" could not work.
Hope this helps.
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.

@Stefan that worked. Thank you  now the Duration column is not populating with the number that appears in the alloc. duration. For this project I have included the dependencies section screenshot

@Stefan I have another followup  the formula works but the weighted durations are not equaling out  look at the gap between row 68 and 69 tasks it is almost a week and half of no work? TIA for your assistance copying @Paul Newcome @Debbie Sawyer since I have seen them comment on other formula posts

@Stefan I am really hoping you can help  or refer me to someone that can  the forumla is now no longer working. Basically I need the following
 Task has a total duration (i.e 58 days)  this is different for every batch image one
 We have added an allocation percentage breakdown for each task (i.e Create is .75 of the total days)
 I put the formula provided in the highlighted column and it gives me a 0 now  if I use the following formula =[Alloc.]@row * Duration55 for each cell it gives me the correct breakdown but not a feasible option to have to edit 1000+ rows image 2
 If the alloc. duration is working properly why doesn't the duration column now show the same amount of days in the alloc. cell
 Last  why would there be gaps in the last text if the total duration is calculated correctly but there is a gap of work
1 
2 

Thank you for tagging me  I love formulae like this!
So I wondered if we could utilise the Audio Summary Duration value instead of the Batch 4 (FIB) value? If so we can use the following in Alloc. Duration Calculation:
=[Alloc.]@row * (PARENT(Duration@row))
Would this work for you?

In this part of the thread
Your formula has not worked as you didn't fix the row 64 element in your formula.
I would anticipate in Allocation Duration row 66 your formula looks like this:
=[Alloc.]@row * Duration64
But it should be
=[Alloc.]@row * Duration$64
The $ in front of the row number fixes that row within the formula so that when you copy down all other rows within that group of children, they will all refer to the duration in row 64.
I believe in your screen shot, the row wasn't fixed and therefore the formula isn't using the total duration for the group at each child level.
Hence the broken results in Allocation Duration.
I hope this makes sense!
Kind regards
Debbie

For this part of the thread:
The reason that the Duration column is not reporting the same as the Allocation Duration is that the Duration column is worked simply by the number of days between the Start Date and the End Date.
If you need the Duration to be calculated differently to 'End Date  Start Date' then you will not be able to have dependencies enabled.
There are ways of setting the dates in relation to other rows and then calculating duration using a formula but you cannot have dependencies enabled to achieve this level of control over duration.
I hope this helps.
Kind regards
Debbie

@Debbie Sawyer THANK YOU for saving me with the formula, and the duration Now I need help on the last question  why is there a huge gap in date (row 329 and 330)from the second to last task and last task if my formula is breaking apart the total days per allocation percentage

What is the Predecessor data for row 330?

Sorry the screenshot did not have that see below each task should fall within the full duration

@Debbie Sawyer is there any way to continue to use the duration column? It seems the Alloc. Duration days are tied to the predecessors but I did not set it up that way.... we could feasible get rid of duration but would that impact anything else?

@Debbie Sawyer so I think I now see why we need the duration the total alloc duration needs to update the dates of each task

It is your Predecessor data that is controlling the dates for your final task.
321FF means row 330 cannot finish until row 321 has finished, as therefore the finish date for row 330 is set by the finish date on row 321. If you changed the Predecessor data in row 330 to 329, then row 330 would have a start date of 09/06/23 and a finish date of 09/06/23 too.
Does this make sense?
Kind regards
Debbie

Ahhh  I see, you have inherited this Smartsheet from another. That makes sense.
I'd be happy to jump on a zoom (right now) and explain the way the enabled dependencies are setting your dates and thus your durations.
It might be easier than messaging on this thread!
My email address is debbie.sawyer@smarterbusinessprocesses.com.
I'll monitor that email for the next 15 mins (as it is quite late in the day in UK!) and if you pop an email through I'll connect on Zoom MS Teams (let me know your preference) and we can talk it through! Shouldn't take too long.
Kind regards
Debbie

Sorry Beth, I'm going to need to log out now. Feel free to reach out though and I'd be happy to talk you through this.
Kind regards
Debbie
Help Article Resources
Categories
Check out the Formula Handbook template!