Calculating Gross Profit Formula with Divide by Zero error

[elizabethv]

Hi there,

My company utilizes Monday.com in order to calculate our Gross Profit percentage.

The relevant columns effectively are:

Revenue | Gross Profit | GP % | CC 2%

The current formula I have is ({Actual GP}+{CC 2%})/{Actual Revenue}

Currently, sometimes the board produces a red exclamation mark when attempting to divide by zero, and other times it produces a ‘negative infinity’ error, as pictured below:

The issue is that the ‘negative infinity’ errors prevents us from being able to look at the averages for the whole column, as any information appears as that ‘negative infinity’ symbol.

I’ve attempted a few different fixes based on other topics of similar nature on this board, like:

IF(({Actual Revenue}+0) <> 0, DIVIDE({Actual GP}+{CC 2%}), {Actual Revenue}), 0)

But this generally results in the whole column being red exclamation points.

Any help would be appreciated!

[JHconsultancy (Joseph Honig)]

Hi,

I think you get the error becuse you are missing a opening bracket after the Divide function.

Your formula should be IF(({Actual Revenue}+0) <> 0, DIVIDE(({Actual GP}+{CC 2%}), {Actual Revenue}), 0)

Hope this helps

[GCavin (Gilles Cavin - Reinventing Formulas in monday.com)]

To fix the red icon, try:

IF({Actual Revenue} <> 0, DIVIDE(SUM({Actual GP},{CC 2%}),{Actual Revenue}),0)

I can’t reproduce the infinity, but you may want to try adding the MAX function in order to get a minimal value of 0 (choose another minimum if you allow the result to be negative).

MAX(0 , IF({Actual Revenue} <> 0, DIVIDE(SUM({Actual GP},{CC 2%}),{Actual Revenue}),0))