Hide the #VALUE!

Tags: #<Tag:0x00007f3436e42d40>

I have used formula for a column (ex: (A*100)/B ) with division. but i should allow null values in the cells as well. when the cells are empty, formula colum is showing #VALUE!. Instead I want to keep it empty.

Please advice

Hi @mvijaybe2011

You can hide this error via cell renderer or add IF to the formula to return null.

Hi,
I tried below formulas but no luck

  1. “=IF((($B” + cIndex + " * 1000) / $C" + cIndex + “) == ‘#VALUE!’, ‘s’, ‘E’)”
    this works for first load, then when we edit the values formula not applied

  2. “=IF(AND(ISNUMBER($B” + cIndex + “), ISNUMBER($C” + cIndex + “)), ($B” + cIndex + " * 1000) / $C" + cIndex + “, ‘’)”
    No luck

  3. “=IF(ISNUMBER($B” + cIndex + “), ‘s’, ‘n’)”
    this also works for first load, then when we edit the values formula not applied

Thank you for sharing the formulas.

I will debug it and come back to you after the weekend.

Hi Any updates ?

Hi @mvijaybe2011

here is a demo that I propose to use https://jsfiddle.net/cs8dgyuq/
This is the cleanest way to achieve the goal and it works when you change the value to something else (the formula is correctly recalculated).