Hide the #VALUE!

Tags: #<Tag:0x00007f0b03a7cd98>

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).

Thank you it worked but

  1. Still cell background color becomes red for invalid values
  2. Decimal Format => format: ‘0,0.0’ is not applied in the formula column

I do not see the background color change in my demo. Do you add anything to the demo or are you able to replicate the red background using my demo? I guess that you are using numeric cell type as you’ve mentioned formatting. Then let me know if this works for you well https://jsfiddle.net/r2xsyj8d/1/

I’ve used the cells method to define that the formula cell is a text cell type. But still, it looks like a number.