Fix for expression giving 0% or missing decimal part

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.

8 Likes

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?

1 Like

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

1 Like

@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!

2 Likes

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?

Screen Shot 2020-02-25 at 18.27.47

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.

1 Like

How about this:

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

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 :pensive:

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:

Screen Shot 2020-03-03 at 23.53.25

Am I right about that?

1 Like

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.

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

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

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! :sweat:

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.

1 Like

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

produces

Screen Shot 2020-03-04 at 00.20.55

Commas are right but only two decimals.

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

produces

Screen Shot 2020-03-04 at 00.21.29

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
)

Screen Shot 2020-03-04 at 00.48.56

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

4 Likes

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

2 Likes

captured2 captured3 capture capture

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. :beers: :beer: :beer: :beer: :beers:

3 Likes

Wow! Glad you could solve it!

1 Like

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 !

1 Like

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 :wink:

3 Likes

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. :wink:

2 Likes