Complex Formula Help Needed
On a Gantt chart, I've added a column % Progress Expected and I have it calculating % progress expected per day for each line item, based on the duration; see formula below.
=(TODAY()  Start@row) * ([ADMIN: % EXPECTED DAILY PROGRESS]@row) * 1.1
The problem I'm trying to solve for is I need it to show 0% progress expected if the start date has not yet occurred (right now it shows a negative #), and I want it to stop once we either complete the task (% Complete and we mark it 100%) or the % Progress Expected reaches 100%, whichever occurs first.
I did solve this thru conditional formatting but it severely skews the rollup averages which is ultimately what I'm needing to display on our dashboard. It looks great for each line item but is completely wrong for the rollup data.
In case it matters, the % process expected per day is in a hidden column. I'd love to get rid of that and embed it in the formula if possible but that is not necessarily something I need to solve for today.
Best Answer

I think I've figured it out. Instead of nesting multiple formulas (which was getting exceedingly confusing) I ended up telling it to cap the % to anything between 0100%.
=MAX(0, MIN((TODAY()  Start@row) * ([ADMIN: % EXPECTED DAILY PROGRESS]@row) * 1.1, 1))
My test is working, fingers crossed it still looks right when I apply it to the real spreadsheet!
Shoutout to ExcelJet for sending me in this direction.
Answers

Hi @cabeaudoin ,
I think adding a simple IF statement to the beginning of your formula may be what you're looking for.
=IF(Start@row > TODAY(), 0,
If the Start date is greater than today
True = 0
False = (TODAY()  Start@row) * ([ADMIN: % EXPECTED DAILY PROGRESS]@row) * 1.1)
So the full formula is
=IF(Start@row > TODAY(), 0, (TODAY()  Start@row) * ([ADMIN: % EXPECTED DAILY PROGRESS]@row) * 1.1)

@Summer Thank you, unfortunately that is throwing an "unparseable" but I think this is putting me down the right path. I might be back!

I think I've figured it out. Instead of nesting multiple formulas (which was getting exceedingly confusing) I ended up telling it to cap the % to anything between 0100%.
=MAX(0, MIN((TODAY()  Start@row) * ([ADMIN: % EXPECTED DAILY PROGRESS]@row) * 1.1, 1))
My test is working, fingers crossed it still looks right when I apply it to the real spreadsheet!
Shoutout to ExcelJet for sending me in this direction.

@cabeaudoin I am so glad you were able to figure it out! That's the best feeling!
Help Article Resources
Categories
Check out the Formula Handbook template!