Formula to set % to not exceed 100
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
Best Answer

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

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?

Hello, Sorry for the delay. I got pulled into other efforts so didn't have a chance to followup 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.

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.

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

Glad I could help.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 302 Events
 33 Webinars
 7.3K Forum Archives