Formula issue: Can't divide by zero even there are no zeros in the data

#1

Hi Infinity team,

I’m trying to set up a metrics table for tracking my goals. I have three integer fields, “StartValue”, “CurrentValue” and “GoalValue”. None of them are empty or zero. However, when I want to create a formula for tracking the progress “CurrentValue/(GoalValue-StartValue)” it says “can’t divide by zero”. I tried it also with DIVIDE and MINUS functions instead of / and - but didn’t help. By the way, the result was set up to b shown as a percentage but I tried other result formats as well but it didn’t seem to have anything to do with the eerror popping up. If I left the brackets away (which made the result incorrect of course), the error no longer appeared, so my think it may be something up with them. Again - none of my data values were 0 or empty. I had only one row where StartValue = 15, CurrentValue=40 and GoalValue=250, no commas, no negatives or anything else that could mess this up.

Thands in advance for looking into this,

Saskia

0 Likes

#2

Hi @saskia :wave:

This was indeed an interesting case, we had to test a few different things in order to get the result.

When you work with formulas, in most cases you have to select the formula you want to work with (like Multiply, Divide, SUM etc.), in your case there were no formulas selected and that’s the reason why it did not function as intended.

We did brainstorm a little and we believe that the formula we came up with will actually help you get the result you are looking for, this is the formula:

MULTIPLY(CurrentValue, 100) / (SUBSTITUTE(GoalValue,StartValue))

Let us know if this works for you, thank you.

0 Likes