Formula Calculation with a zero value denominator
I'm trying to create a formula to calculate the # Hours per remaining week based off the total HOURS remaining. Calculation takes into consideration the calculated remaining # of weeks on a project. Logic should be: Take Remaining hours / # of remaining weeks. If remaining weeks is < 1, simply return the amount of remaining hours.
The current formula below I have is calculating correctly when the Remaining (Weeks) is 1 or more. However, once the Remaining (Weeks) is less than 1, the returned value is incorrect and I cannot figure out WHY it returns this odd 1125 value.
Formula: =IF([Column2]9 > 0, [Column3]@row / [Column2]9, MAX([Column3]@row, 0))
In this example, # of remaining weeks is 3.3. Therefore, the Remaining Per Week is 97.8 (321.5/3.3)> Correct calculation
Second Example: Here, using the same formula but when the Remaining (Weeks) is < 1, it keeps returning a value of "1125" which I cannot figure out why. What I want it to return is the Remaining Hours of = 321.5  the value in Column3@row. Tried writing this in different ways, and keep getting same odd value. Any idea and help would be great appreciated. Thank you
Best Answer

Hi @Christine Cao,
It's down to your formula and rounding. What's happening is that you have 0.3 of a week (which I'm guessing is actually 2 days  0.2857..., rounded to 0.3). If you calculate 321.5/(2/7), you end up with the 1125.3 (to 1 dp).
You can get round this by altering your formula to do what you're actually after:
=IF([Column2]9 >= 1, [Column3]@row / [Column2]9, MAX([Column3]@row, 0))
This should make it so if weeks remaining is equal to or greater than 1 it will do the calculation, and otherwise take the MAX of either 0 or column 3.
Give this a try and hopefully it should resolve your issue!
Answers

Hi @Christine Cao,
It's down to your formula and rounding. What's happening is that you have 0.3 of a week (which I'm guessing is actually 2 days  0.2857..., rounded to 0.3). If you calculate 321.5/(2/7), you end up with the 1125.3 (to 1 dp).
You can get round this by altering your formula to do what you're actually after:
=IF([Column2]9 >= 1, [Column3]@row / [Column2]9, MAX([Column3]@row, 0))
This should make it so if weeks remaining is equal to or greater than 1 it will do the calculation, and otherwise take the MAX of either 0 or column 3.
Give this a try and hopefully it should resolve your issue!

@Nick Korna I didn't think about how the system calculated the partial week. However, your suggested formula worked!! Thank you so very much. This community is awesome! Cheers  Christine
Help Article Resources
Categories
Check out the Formula Handbook template!