Test NULL or empty cell in formula returns #VALUE error

Tags: #<Tag:0x00007f8b28c1b578>
#1

Hello,

I would like to assign a cell a formula returning a calculation involving another cell value, and leaving the cell empty if the computation is not possible (e.g. if the other cell is empty / not a number / error).

Is this possible ??

I have tried a lot of things with Excel primitives but it seems like some of them are not recognized by Handsontable (return #NAME? error), and the other attempts keep returning #VALUE! error.

Here is an example with many of the formulas I have tried : http://jsfiddle.net/259dkjxv

Thanks in advance for your help.
Jijido

#2

Hi @Jijido

here’s a demo with the numeric and null values http://jsfiddle.net/handsoncode/1c29mLow/

Let me know if it works for you as well.

#3

Thanks @aleksandra_budnik.

I have to admit I don’t really understand why the error occured when the cell was empty because it seems like it was the “else” part of my formula that was wrong, but it seems to work with your formula.

If you know the answer, I’d be glad to hear it.
Else I think you can close this topic now :slight_smile:

Jijido

#4

There was a syntax error

[null, '=IF(ISBLANK(A1), "", A1*UR)'],   //previous 
[null, '=IF(ISBLANK(A1); ""; "=A1*UR")'] // fixed

Updated demo http://jsfiddle.net/handsoncode/nq2rbu0x/

#5