The formula is returning an error

Platform discussions
, , ,
1

Hello. I need help with the formula below. I’ve tried several ways to make the formula enter the first condition, returning the value 100 when the Baseline and Alvo columns are equal. However, I end up receiving the error “Invalid parameter.” The funniest thing is that when I execute only the first condition in isolation, it works. Could someone assist me? Thank you in advance.
I emphasize that the columns Baseline, Alvo, and Resultado Atual are of numeric type.

A

The formula is the one described below, and it is in the column % de Atingimento:

IF(
	{Baseline}={Alvo},
      100,
   	    IF(
    	    {Resultado Atual}=0,
	      	  ROUND(0/({Alvo}-{Baseline})*100,0),
		        ROUND(({Resultado Atual}-{Baseline})/({Alvo}-{Baseline})*100,0)
           )
  )
2

Hi Carlos,

The problem is when Baseline = Alvo, {Alvo}-{Baseline} = 0 and you can’t divide by 0. I know you believe you ruled out this possibility with your IF({Alvo}={Baseline}, 100…) but it is not the case. See the 2nd paragraph of this article A Simple Way to Handle Empty Columns and Avoid Errors in your Formulas | mdBoosters Blog, should you be interested in why.

Now, if you want to fix your formula, replace

ROUND(({Resultado Atual}-{Baseline})/({Alvo}-{Baseline})*100,0)

by

ROUND(DIVIDE({Resultado atual}-{Baseline},IF({Alvo}={Baseline},1,{Alvo}-{Baseline}))*100,0)

I am not sure what you’re trying to achieve with

ROUND(0/({Alvo}-{Baseline})*100,0)

It will raise an error as well, but besides that, it would in all cases result to 0 (0 divided by x, will always result to 0)

So I would suggest replacing it with 0.

As a whole, here is a formula that works (if baseline and alvo are filled in!)

IF({Baseline}={Alvo},100,
   	    IF(
    	    {Resultado Atual}=0,0,
		        ROUND(DIVIDE({Resultado atual}-{Baseline},IF({Alvo}={Baseline},1,{Alvo}-{Baseline}))*100,0)
           )
  )

If you have several formulas like this, you may want to look at a 3rd-party app called Advanced Formula Booster which makes it so much easier to write formulas like this and presents numerous other advantages as well.

In the Advanced Formula Booster, you can use as many lines as you want to write your formula and you can stop the formula at any point. For instance, you could say IF({Baseline}={Alvo),STOP()) and then you’ll know if the formula reaches the lines below this one, the 2 values are not equal.