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[email protected]Tried writing this in different ways, and keep getting same odd value. Any idea and help would be great appreciated. Thank you

Nick Korna

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

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 KornaI 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