Formula not computing correctly?

[JMacFeegle]

An app formula is not computing correctly in the app interface although the results are correct in the formula preview and I can't find out what's happening.

Columns (all in the same table):

[Price gross] | [Price net] | [Tax in %]

[Price net]=[Price gross]/(1+[Tax in %])

The expression result is correct when I test it in the expression assistant, yet in the app [Price net] is filled with the value of [Price gross]?!

EDIT: I created a virtual column that calculates the tax amount in €, and it DOES pupulate correctly, but it takes a moment (until sync of changed price is complete) - I am under the impression that virt. columns and app formulas should compute immediately? Changing the app formula for [Price net] to [Price gross]-[Tax amount in €] also leaves me with the wrong  result (value of [Price gross] )!

[1minManager]

Virtual columns can behave a bid odd.  If your in a detail view they show the result of the formula at the last sync.  But in form view they do the calculation 'live'.  So its possible to have [a]=1 & [b]=2 and [c]=[a]+[b]=3 after a sync in a detail view.  You then go in the form view and change [a] to 5, [c] then says 7.  You click save and yet the detail view still shows [c]=3.

Not sure if this is the cause of your issue?

[JMacFeegle]

I only added the virt. column to test some things. The underlying issue is that the formula 

[Price net]=[Price gross]/(1+[Tax in %])

appears to have the correct result when I test it in the expression assistant but in the app it is populated with [Price gross] with no tax deducted. No virtual columns at play here, although as described when I use the virt. column (that contains the calculation of the tax amount) the result is still wrong.

[1minManager]

Is it because [Price gross]/(1+[Tax in %]) is not allowed if (1+[Tax in %]) is zero?

Maybe giving us the figures and results your getting might help.

[JMacFeegle]

Example calculation:

[Price gross]=107€
[Tax in %]=7%

Expected result of 
[Price net]=[Price gross]/(1+[Tax in %]) = 107 / (1+7%) = 107 / 1,07
would be [Price net]=100€.
The app "calculates" it to[Price net]=107€ (which is wrong), but only in the app itself, when I run a preview of the calculation in the expression assistant it results in the correct 100€ for the same row!

[Tax in %] is always either 19%, 7% or 0%, so (1+[Tax in %]) is never zero.

[1minManager · Accepted solution]

Logically then for 107/(1+[tax]) to equal 107, [tax] must equal 0.  Or according to this, [tax] would be 0 if [tax] was blank.  Is this a posibility?

https://support.google.com/appsheet/answer/11510515?hl=en

Personally I never use the 'test' part in the formula.  So I can't explain why they might be different.

I'd suggest changing all the columns to simple 2 digit decimals and do it 'manually' to try and work out what it is.  I can't see anything obvious but Appsheet doesn't get simple stuff like adding percentages wrong.

[JMacFeegle]

I changed the tax column to decimals, and lo and behold, it works now. This makes ZERO sense, but I'll take the win.

Thank you!

[WillowMobileSys]

Number types will always return a whole number.  The result of any Number calculation is either truncated or rounded to a whole number.  Division, which almost always results in a non-whole number result is no different.  If Number types are used the result is truncated or rounded.

If decimal precision is important, then to avoid the reduction to a whole number, you need to do one of three things:

1. Define the columns involved as Decimal (not always an option)
2. Use Decimal constants - use "1.0" instead of just "1"
3. Use the DECIMAL() function to force the value into a Decimal such as: 

107 / DECIMAL([Some Number Type])

 I hope this helps!

[gregdiana1]

I did this and it worked ok?

Both prices are of type PRICE and tax is of type  PERCENT

[JMacFeegle]

This is exactly how it was set up in my app.

I changed the tax in % to decimals as suggested by @1minManager and it works now. Don't ask me why or why it didn't before. Changed it back to percent AND IT STILL WORKS.

[gregdiana1]

Yes but the format in the spreadsheet and in the app may be different.

In the spreadsheet the prices are in decimal and then I changed to $US currency and tax is in %

In the app both are prices and tax %.

The decimal solution works but is not necessarily correct. One must ensure that there is compatibility between the spreadsheet and APPSHEET format.

The issue of using 1.0 and not 1 is a JavaScript trick type issue but with % being used should not apply as my example has shown.