Fix calculation

You can see the web app, but the input cell remain empty? Calculations seem wrong? Here a checklist of common issues that result into malfunctioning apps.

  • Remove all #REF, #DIV/O from the spreadsheet
  • Check for number as text detection
  • Numeric input cells should contain a default value

Numeric cells with default values

Why is it so important to pre-fill a numeric value with a default value (usually 0)? First, you have to know a few things:

  • By default in Excel, an empty cell is considered to be a string,
  • When you type something inside a cell, the spreadsheet software will dynamically update the type of the cell according to what you entered: a number, a date or a string,
  • Once converter by Appizy, your web app will lose the capacity to update the input type. It is frozen.

The following mini-calculator is made with Appizy. All the explanations are in the paragraphs below.

1. Addition with empty cells

A

B

A+B

2. Addition with default values

A

B

A+B

3. SUM function

A

B

SUM(A :B)

1. Addition with empty cells

The first example does work in Excel but not once converted. It is a basic addition of two cells, but the cells are left empty. In Excel, when you type something in the cell, the Software understands it is a number and get the addition correctly.

When the same file gets converted, the cells are string (because they are empty). Thus, when Javascript computes A + B, it concatenates the string contained in A and the string contained in B. This is also because “+” is the concatenation operator in JavaScript. Try to enter 1 and 2, you will get "1" + "2" = "12" rather than 1 + 2 = 3. You can also notice that the input is aligned on the left (like a string in Excel) rather than on the right (like a number).

2. Addition with default values

In the second example, we provided a default value in both cells. In this case, Appizy converts the cells as a number and everything works as expected!

3. SUM function

If you replay the first example with the SUM function instead of the “+” operator it works! What is the trick? Well, the SUM function as a built-in capacity to parse a string as a number. Knowing that the function is making a sum, it prevents error by converting "1" into 1. The output is correct even if the cells are strings (content is left-aligned).

Final word

We encourage you to fill all numeric input (displayed or hidden), with a default value. Appizy will understand: “This cell is a number”. If you don’t like all these 0 written everywhere make use of the Clear zero input setting in the panel Content and layout. It will remove the 0 but keep the numeric type. Check the Startup Capital sample for further experimentation.

Web apps created with Appizy use the library Formula.js to reproduce the spreadsheet function into JavaScript. Some functions are implemented but behave in a slightly different way. It can be the source of malfunctioning web-app.

ISBLANK

Commonly used to check if a cell has been filled. Given the formula =IF(ISBLANK(A1), "Blank", "Not blank") we recommend:

  • =IF(A1>=0, ..., ...) to check if a numeric value is filled,
  • =(A1<>", ..., ...) to check if a string value is filled.

IFERROR

The IFERROR function returns a custom value when a formula generates an error. This normally happens when the user input is wrong.

Error handling is different in JavaScript than in Excel. In this case, we recommend you avoid making calculations with wrong inputs instead of catching an error add the end of the calculation chain.