Complex Formula Help Needed

cabeaudoin

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 roll-up 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 roll-up 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.

cabeaudoin

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 0-100%.

=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.

Summer

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)

cabeaudoin

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

cabeaudoin

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 0-100%.

=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.

Summer

@cabeaudoin I am so glad you were able to figure it out! That's the best feeling!