Formula to set % to not exceed 100

Christine Cao

Hello SS Community,

Trying to write a formula to calculate % Complete off Days Completed versus going off project plan % Complete. This is for situations where PMs don't manage a project plan of tasks but simply track budget for a duration of time.

Days Completed =(NETWORKDAYS([Project Start Date]@row, TODAY()))

NetDays=IFERROR(NETWORKDAYS([Project Start Date]@row, [Target End Date]@row), " NO DATA")

This below formula is the problem:

=IFERROR(MAX([Days Completed]@row / NetDays@row, 100), " NEED DATES")

When I used this formula I get 10,000% instead of 100%

What's wrong with this formula?

Thank you

Christine

KPH

The MAX function returns the highest number (or latest date). It does not restrict a value to a maximum value.

=MAX(7000,100) would return 7000 as that is the highest number passed to the function.

=MAX(70,100) would return 100.

If you want to return a value that never exceeds 100% then you need an IF function instead. Something like this:

=IF(([Days Completed]@row / NetDays@row) > 1,1,([Days Completed]@row / NetDays@row))

This looks at days complete divided by net days. If this is greater than 1, it returns 1. If this is not greater than 1, it returns days complete divided by net days.

KPH

Hi Christine

There is nothing really that wrong with your formula. It is due to the column that holds the formula. And really, how they are working together.

The formula will return the answer 100.

However, if the column is a % column - which is set by the % icon on the tool bar, or automatically if you enter % into a cell - the number 100 will display as 10,000%.

You can test this by typing =100 into your cell. I’m pretty sure you will see 10,000% as the value.

You don’t really want it to be a max of 100. Your calculation for date completed divided by net days will return 0.5 for 50%, 0.25 for 25%, etc. So what you really want is 1 for 100%.

If you change your formula to use 1 rather than 100, 1 will be the max returned, which will display as 100%.

Does that make sense?

Christine Cao

@KPH

Hello, Sorry for the delay. I got pulled into other efforts so didn't have a chance to follow-up on this. Anyway, I did modify the formula like you suggested:

=IFERROR(MAX([Days Completed]@row / NetDays@row, 1), " NEED DATES")

With this, the % returned as 103% and still didn't max it at 100% as seen below. Very odd.

KPH

The MAX function returns the highest number (or latest date). It does not restrict a value to a maximum value.

=MAX(7000,100) would return 7000 as that is the highest number passed to the function.

=MAX(70,100) would return 100.

If you want to return a value that never exceeds 100% then you need an IF function instead. Something like this:

=IF(([Days Completed]@row / NetDays@row) > 1,1,([Days Completed]@row / NetDays@row))

This looks at days complete divided by net days. If this is greater than 1, it returns 1. If this is not greater than 1, it returns days complete divided by net days.

Christine Cao

@KPH

That worked exactly as intended. Thank you so much. These formulas a tricky and learning as I go. Thanks again

Christine

KPH

Glad I could help.