Divide by Zero Error - help needed

Platform discussions
1

I have searched the community and tried the IF & AND solutions mentioned but i still get what i think are divide by Zero Errors for the formulas below;

First Formula - To show the percentage difference between two values (i have removed the IF & AND from the formula)

ROUNDUP(({Agreed Claim Value}-{Total Claim Value})/{Total Claim Value}*100,0)

Where Total Claim Value is another formula as shown below. I think it is this formula that is giving me the problem as even with the IF & AND statements (removed here) the result always shows ‘0’ (i want the result to be blank if the cells in the formula have no entries / are blank. I think this will then fix the formula above)

Second Formula - Sum of all the items to show the ‘Total Claim Value’

(({Hours A}+{Hours B}+{Hours C}+{Hours D})*{Hourly Rate})+({Equipment & Material Costs}*1.1)+({Third Party Costs}*1.1)+{Miscellaneous Costs}

Thank you.

2

Hi Leigh,

Not sure why you removed the Ifs as it makes difficult to understand what your problem is. I’ll give it a show anyway.

To avoid errors, try replacing {Total Claim Value} in the divider by

IF({Total Claim Value}="0",1,{Total Claim Value})

So, you end up with:

ROUNDUP(({Agreed Claim Value}-{Total Claim Value})/IF({Total Claim Value}="0",1,{Total Claim Value})*100,0)

Which will not generate any error, but will get you a 0 if values are not filled in.

Since you don’t want zeros, all you have to do is add an IF statement like this:

IF(code_above=0,"",code_above)

i.e,:

IF(ROUNDUP(({Agreed Claim Value}-{Total Claim Value})/IF({Total Claim Value}="0",1,{Total Claim Value})*100,0)=0,"",ROUNDUP(({Agreed Claim Value}-{Total Claim Value})/IF({Total Claim Value}="0",1,{Total Claim Value})*100,0))

Hope it helps.

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

3

Hi Gilles,

Thanks for your input, I removed the IF & AND Statements as they weren’t giving me the expected answer so went back to basics.

I have tried both of the formulas you kindly shared but the result is the same as mine, when the, when ‘Total Claim Value’ is 0 i get an error “One of the parameters is invalid”.

I think the solution lies in fixing the second formula in my original post so that when all the cells are blank, and therefore ‘Total Claim Value’ should also be blank, i.e. not showing 0.

The following variation of the (second) formula also results in the cell being populated with “0” even when all the cells are blank/empty, so im baffled;

IF(
AND(
ISBLANK({Hours A}),
ISBLANK({Hours B}),
ISBLANK({Hours C}),
ISBLANK({Hours D}),
ISBLANK({Hourly Rate}),
ISBLANK({Equipment & Material Costs}),
ISBLANK({Third Party Costs}),
ISBLANK({Miscellaneous Costs})
),
“”,
(({Hours A} + {Hours B} + {Hours C} + {Hours D}) * {Hourly Rate}) + ({Equipment & Material Costs} * 1.1) + ({Third Party Costs} * 1.1) + {Miscellaneous Costs}
)

4

To resolve the issue you’re experiencing with the formula, you can use logical functions to handle cases where the cells are blank. Here’s how you can adjust your formulas:

First Formula

To show the percentage difference between two values and ensure the result is blank when cells are empty, use the following formula:

IF(AND({Agreed Claim Value}<>“”,{Total Claim Value}<>“”), ROUNDUP(({Agreed Claim Value}-{Total Claim Value})/{Total Claim Value}*100,0), “”)

Second Formula

To compute the ‘Total Claim Value’ ensuring it shows blank when relevant cells are empty, you can use:

IF(OR({Hours A}<>“”,{Hours B}<>“”,{Hours C}<>“”,{Hours D}<>“”,{Hourly Rate}<>“”,{Equipment & Material Costs}<>“”,{Third Party Costs}<>“”,{Miscellaneous Costs}<>“”),
(({Hours A}+{Hours B}+{Hours C}+{Hours D})*{Hourly Rate})+
({Equipment & Material Costs}*1.1)+
({Third Party Costs}*1.1)+
{Miscellaneous Costs},
“”)

These formulas include IF and AND/OR functions to check if the required fields are not blank before performing calculations, ensuring that the result will be blank if any of the cells are empty.

Hope it works out for you.
Christina