Fix for expression giving 0% or missing decimal part

Steve
Participant V

Trying to divide two numbers to get a percentage but always getting 0?

Trying to divide two numbers but losing the numbers after the decimal point?

TRY THIS

Convert one or both of the values in your division expression to a Decimal value, either by: (1) wrapping the value in the expression with the DECIMAL() function; (2) multiplying the value in the expression by 1.0; or (3) changing the columnโ€™s type to Decimal if suitable to the app.

FOR EXAMPLE

If computing a percentage from two Number column values:

([Correct Answer Count] / DECIMAL([Question Count]))

or:

([Correct Answer Count] / ([Question Count] * 1.0))

THE DETAILS

The Number column type represents integers, which are whole numbers, numbers without a fractional component. A Number value has no decimal point.

When one Number value is divided by another Number value, AppSheet assumes you want the result to also by a Number value. If the result has in a fractional component (something after the decimal point), that fractional component is discarded entirely, leaving only the whole number component (the part before the decimal point).

If one or both of the values in a division operation is a Decimal value, AppSheet assumes you want the result to be a Decimal value as well, and will preserve any fractional component in the result.

The DECIMAL() function can be used to convert a Number value to a Decimal value. Multiplying a Number value by 1.0 (a Decimal value) has the same effect.

28 30 4,395
30 REPLIES 30

Mike_A
Participant V

Thanks for the explanation and tip how to work around this @Steve! Since Appsheet knows we are dividing two โ€œnumbersโ€, and that there is a decimal remainder, would be a lot less confusing to return the decimal automatically. Maybe a request for the backlog?

Cool! I had found another work around for this but the approach here is better. Thanks!

@Steve, When I saw this today I thought it was interesting but I wasnโ€™t facing the particular issue at the time. Unexpectedly, however, it just came in handy. I was trying to use CEILING() to round up the result of one number being divided by another. It wasnโ€™t working (it appeared to be rounding down) so I used the decimal() trick you introduced and now itโ€™s working. Thanks!

Hi @Steve! Iโ€™m coming back to this after some time. I thought Iโ€™d share my approach to a related problem: How to build a text expression such as โ€œ3.2 widgets per dayโ€ where 3.2 is the product of an โ€œ[A]/[B]โ€ kind of expression rounded to the nearst 10th.

Approach 1

Use two (virtual) columns.

Column 1

Make the expression

[A]/decimal([B])

and then set the column type and decimal digits as follows:

Column 2

concatenate([Column 1]," widgets per day")

Approach 2

Do it all in one virtual column:

 concatenate(
       [A]/[B],
       ".",
       right(  
            round(
                ([A]*10) / decimal( [B] )
                )   
       ,1),
       " widgets per day"
       )

The combination of DECIMAL and ROUND are necessary, unless Iโ€™m mistaken, in order to get the same results as the two column strategy.

Now, neither approach is particularly easy or intuitive, which leads me to the following question: Shouldnโ€™t AppSheet have a ROUND() expression that works like the Google sheetsโ€™ ROUND function?

2X_c_c02d64e1c0a174df4598fae689dde984e75d53b3.png

I looked but couldnโ€™t find a feature request for this. If I can verify that my thought that such an expression is needed seems to be correct, I think Iโ€™ll repost this as a feature request.

Any thoughts?

in Excel

1234567.33/1.17 returns 1055185.75213675

in AppSheetโ€™s virtual column with Text type:

1234567.33/1.17 returns 1055185.7521367521367521367521

LEFT(1234567.33/1.17, FIND(".", 1234567.33/1.17)-1) &"."& LEFT(SUBSTITUTE(1234567.33/1.17, LEFT(1234567.33/1.17, FIND(".", 1234567.33/1.17)), โ€œโ€) & โ€œ0000000โ€, 1) returns 1055185.7 (should be 1055185.8)

LEFT(1234567.33/1.17, FIND(".", 1234567.33/1.17)-1) &"."& LEFT(SUBSTITUTE(1234567.33/1.17, LEFT(1234567.33/1.17, FIND(".", 1234567.33/1.17)), โ€œโ€) & โ€œ0000000โ€, 2) returns 1055185.75

LEFT(1234567.33/1.17, FIND(".", 1234567.33/1.17)-1) &"."& LEFT(SUBSTITUTE(1234567.33/1.17, LEFT(1234567.33/1.17, FIND(".", 1234567.33/1.17)), โ€œโ€) & โ€œ0000000โ€, 3) returns 1055185.752

Rounding of number may not be mathematically correct every time.

How about this:

(ROUND((1234567.33 / 1.17) * 10.0) / 10.0)

That returns 1,055,185.80

Just trying to have more than 2 decimal points with separators like 1,055,185.752 but finally gave up

When you test an expression in the editor, it just shows two decimal points. If, however, you set the column type to decimal and the number of decimal digits to 3, you can get it to display correctly inside of an app. This discrepancy between the test function and actual display is a problem in my view.

Hey Swoopy, Iโ€™m you in the future !

Iโ€™ve finally solved it with expression, you check it out and never give up again mate

This helped me see that your approach is a better solution to my problem:

concatenate(
(ROUND((5 / decimal(3)) * 10.0) / 10.0),
" widgets per day")

This yields

1.7 widgets per day

The problem is that, as @Swoopy pointed out, this approach wonโ€™t give you the commas.

concatenate(
(ROUND((1234567.33 / 1.17) * 1000.0) / 1000.0)
)

produces

1055185.752

The number of decimals is fine but there are no commas. Hmm. To get that in AppSheet I think I need to rely on the formatting capability in a decimal column:

2X_d_d858cf1428f28d0d3e154588ea97f1f6de199162.png

Am I right about that?

To get the commas, just wrap the result with TEXT():

TEXT((ROUND((1234567.33 / 1.17) * 10.0) / 10.0))

Thanks! Actually, I went to the documentation on TEXT(), thinking that it might work, but didnโ€™t see anything there. I should have just tried it!

With your skills, Steve, rounding is not a big deal but I think itโ€™s pretty challenging for most of us. So, I still think that a more robust ROUND() expression (as I described above) would help us ordinary folks.

text(ROUND((1234567.33 / decimal(1.17)) * 1000.0) / 1000.0)

produces

2X_c_c6a14448f54b0c9e655c5547511ddaafc55e8ca9.png

Commas are right but only two decimals.

concatenate(ROUND((1234567.33 / decimal(1.17)) * 1000.0) / 1000.0)

produces

2X_d_d78b7f2f7824fff2e35ca8c115e6c1aba58eee69.png

It really is hard to get what @Swoopy was after. I can image a way to get both the commas and the three decimals in a concatenated expression, but itโ€™s a little complicated.

Stupidly, impulsively, I solved the problem:

concatenate(
text(floor(ROUND((1234567.33 / decimal(1.17)) * 1000.0) / 1000.0)),".",
(
(ROUND((1234567.33 / decimal(1.17)) * 1000.0) / 1000.0)
-floor(ROUND((1234567.33 / decimal(1.17)) * 1000.0) / 1000.0))*1000
)

2X_a_a191c76304efb30021aaf9f31b3989d753e76e3a.png

I can see that the expression is not as elegant as it might be but it works.

Here you go!
Friend, let go to bed now!

This thread may already be too long but the topic has come up in my app again and so Iโ€™d like to write a little report on another aspect of this problem Iโ€™ve been dealing with.

I have some text expressions that produce results in the following format:

96% (145/150)

Hereโ€™s the expression:

concatenate(([Days studied]*100)/[Days since start],"% (",[Days studied],"/",[Days since start],")")

Today I realized that the rounding is not correct. In my spreadsheet, 145/150 is .9666666

So, what I really wanted was โ€œ97% (145/150)โ€. Hereโ€™s what I did to correct things:

concatenate(round(([Days studied]*100)/decimal([Days since start])),"% (",[Days studied],"/",[Days since start],")")

I was able to fix it but I donโ€™t fully understand why I needed to combine DECIMAL() with ROUND(). And, this complexity seems to be at odds with AppSheetโ€™s mission of making things easier for us.

My two cents.

And @Kirk_Masden , to answer your 2 year old post here ( ๐Ÿ˜ž
Youโ€™re doing division with integers. Integer division is not โ€œdo decimal division, then round to the nearest integerโ€. Integer division is โ€œhow many times can I put D into Nโ€, period. There is no rounding, there are no decimals. 150 can go into 14500, 96 times.

To get a rounded result from decimal division, you need to convert your integers to decimals, then use round, as you have done. I personally would have just changed your โ€œ100โ€ to โ€œ100.0โ€, instead of using DECIMAL(), but itโ€™s the same result.

This may be an answer to a problem Iโ€™ve been having in my app. I have an expression that works well after a sync but if I do anything on the app it goes weird until the sync is completed. Hereโ€™s what it looks like normally:

And hereโ€™s what happens when data is waiting to be or in the process of being synced:

The following expression is causing the problem:

if([Session no copy]<>lookup(โ€œSession1โ€,โ€œSettingsโ€,โ€œKeyโ€,โ€œNumberโ€),โ€œCalculating . . .โ€,
concatenate(
if([Sessions]=[Days since start],
โ€œ1 session per day (โ€,
concatenate([Sessions]/[Days since start],".",right(round(100+([Sessions]*100)/decimal([Days since start])),2)
," sessions per day ("))
,[Sessions],"/",[Days since start],")"))

I know that this part is the heart of the problem:

concatenate([Sessions]/[Days since start],".",right(round(100+([Sessions]*100)/decimal([Days since start])),2)

Iโ€™ll try to digest what you have taught me and see if I can find a better solution. In the meantime, I wanted to thank you for your comment which, even though it is in response to something from two years ago, is a continuing problem for me.

P.S. Even though this is probably my fault for writing a weird expression, it would be nice if the AppSheet platform could โ€œcover for meโ€ and not go crazy during the sync. Even though nothing that directly affects this particular calculation is changed, it still goes weird until the sync finishes.

Youโ€™re just wanting number rounded to 2 decimal places right?

ROUND( 100.0 * [Sessions] / [Days since start] ) / 100.0

You fixed my problem. The strange behavior no longer occurs! Many thanks!! (And, now I know how to round to a given decimal place. )

Thanks for this @Steve really helped me

Swoopy
Participant V

2X_9_91bdc30efbd25745d29a9da6e3600aa9a7e33324.png 2X_9_96776f88c216b35da9c1c9060e4a7652a1607772.png 2X_e_ede6a3b4170282262f015d5bb2e42517c0f0872d.png 2X_e_e09054c875a00fab494be5769cec3de348b0bd9b.png

Here is my solution.

1.Create a virtual column DecimalSeparator (Decimal-type) to preserve decimal points as you need such as 3 points, 4 points, etc. Put your expression or math-calculation here.

2.Create another virtual column (Kirk_MasdenRequest in this sample) using Text-type and use the following expression

IF(FIND(".", [DecimalSeparator])=0,
   TEXT(ROUND([DecimalSeparator]))
   ,
   TRIM(
      LEFT(RIGHT("         "&LEFT([DecimalSeparator], FIND(".", [DecimalSeparator])-1), 9), 3)&
      IF(ISBLANK(TRIM(LEFT(RIGHT("         "&LEFT([DecimalSeparator], FIND(".", [DecimalSeparator])-1), 9), 3))),"",",")&
      MID(RIGHT("         "&LEFT([DecimalSeparator], FIND(".", [DecimalSeparator])-1), 9),4,3)&
      IF(ISBLANK(TRIM(MID(RIGHT("         "&LEFT([DecimalSeparator], FIND(".", [DecimalSeparator])-1), 9),4,3))),"",",")&
      RIGHT(RIGHT("         "&LEFT([DecimalSeparator], FIND(".", [DecimalSeparator])-1), 9),3)&
      IF(ISBLANK(TRIM(RIGHT(RIGHT("         "&LEFT([DecimalSeparator], FIND(".", [DecimalSeparator])-1), 9),3))),"",".")&
      SUBSTITUTE([DecimalSeparator], LEFT([DecimalSeparator], FIND(".", [DecimalSeparator])), "")
   )
)
& " widgets per day (OR ANY TEXT MESSAGE)"

This supports up to 999,999,999.99999~

Later on, you can freely adjust your required decimal point in DecimalSeparator as needed. No need to modify any expressions.

Wow! Glad you could solve it!

Iโ€™ve refined the expression to be working with all cases (have decimal points and no decimal point). That means you can FREELY adjust your required decimal points in DecimalSeparator as needed.

NEVER GIVE UPโ€ฆNEVER SURRENDER !

JPAlpano
Participant V

Hi @Steve

I have tried this and it still shows different data in Detail View and in Form View.

IF( ISBLANK([Team Member Name]), 0.0,
    DECIMAL(
			HOUR( TODAY() - [Hire Date] ) / (365.25 * 24)
		) 
  )

This is for computing a tenure (similar to computing age), but we want it to be in Decimal value.

Detail view shows 10.00, but Form View Shows 10.77.

Iโ€™ve done some experimenting with your situation here, but I have no answer for it yet. I do have 2 questions and 1 suggestion though.

  1. What is the column type here?
  2. What option do you have set here (and does changing the option affect your result) ?
  3. The DECIMAL() expression shouldnโ€™t be needed here, and may be having a weird affect. Iโ€™d suggest removing it. You may also try to multiple the HOUR() portion by 1.0, though I donโ€™t think it will actually affect anything.

Hi Marc,

Thanks for your response.

1). The column type is Decimal. Iโ€™ve selected 2 as the decimal places with increments of 0.1.

2). The selection is Consistent.

3). Iโ€™ve tried the x 1.0 way as described by Steve. Still the same.

@JPAlpano It doesnโ€™t seem to me like youโ€™re doing anything wrong math-wise. Your statement of

โ€ฆmakes me think something else weird might be going on. Perhaps describe what else youโ€™re doing here? And it might be best as a new topic.

deltai
Participant III

Thanks for this @Steve really helped me a lot. ๐Ÿ˜ƒ

SASINDHAR
Participant I

If you're experiencing issues with losing decimal values when dividing two numbers in AppSheet, you can follow the provided instructions to ensure the result preserves decimal components. Use the `DECIMAL()` function or multiply by `1.0` to convert one or both of the values to Decimal type, like this:            ([Correct Answer Count] / DECIMAL([Question Count]))  or ([Correct Answer Count] / ([Question Count] * 1.0)).                  This will help AppSheet recognize that you want the result to be a Decimal value and retain any fractional components.

Top Labels in this Space