Problem with Numbers in View! My app links t...

AppSheet AppSheet Q&A
1

Problem with Numbers in View!

My app links to two tables (tabs) in an Excel Worksheet.

One is for the inputs which shows good and the app edits just fine.

The second sheet has the results of complex formulas in the workbook and I just want to see those results.

So I created a view for the data, and the results.

The results - are read only, and the columns are formatted as numbers.

But the numbers shown in the view are always greater than the actual numbers returned in the spreadsheet.

I have verified that the app has synced and the spreadsheet does have those numbers stored in it and they still show wrong. The spreadsheet rounds the numbers down to nearest 25 increments.

On the view - Results number 7275 from the spreadsheet shows in the app view as 7350.

6525 shows as 6625, 4375 shows as 4400.

It looks like always increments of 25 greater.

On one column the result is zero, but it shows in the app as ‘$VALUE!’.

I can’t see anything else that would be manipulating the data.

Can anyone tell me why the data is not displaying as it shows in the spreadsheet?

2

@Charlie_Fulks Hi Charlie, could you give us the names of the relevant app, table, and column so we can investigate?

3

Sure! APP:

AlertSense Pricing Tool

Tables: RESULTS View:

PRICES Columns:

OPT 1:,

OPT 2:, OPT 3: … OPT 8:

4

@Charlie_Fulks Hi Charlie, after some investigation, I believe the problem occurs because the computed values of the cells have been rounded. Most of the affected cells have rather complicated Excel formulas that are made up of many smaller steps or calculations. In our server, it appears the intermediate results generated from these steps may have been rounded, so when the intermediate results are put together to compute the final values, the rounding will become significant.

I will need to look deeper to verify this, but a quick fix for the problem is to convert the current Excel files (.xlsx files) to Google sheets. When the files are Google sheets, the cell formulas will be performed by Google, which should correctly handle the rounding.

5

Thank you so much for looking at this.

I will figure out a workaround.

Thanks!