Hide the #VALUE!

Tags: #<Tag:0x00007efc6ae2f0c8>
#1

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

#2

Hi @mvijaybe2011

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

#3

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

#4

Thank you for sharing the formulas.

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

#5

Hi Any updates ?

#6

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

#7

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
#8

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.

#9