Fix for expression giving 0% or missing decimal pa...

Steve · 10-11-2019 08:12 AM

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.

Mike_A

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?

Kirk_Masden

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

Kirk_Masden

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

Kirk_Masden

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?

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?

Swoopy

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.

Steve

How about this:

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

Swoopy

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

Kirk_Masden

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.

Swoopy

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

Kirk_Masden

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:

Am I right about that?

Steve

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

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

Kirk_Masden

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.

Kirk_Masden

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

produces

Commas are right but only two decimals.

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

produces

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.

Kirk_Masden

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
)

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

Koichi_Tsuji

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

Kirk_Masden

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.

Marc_Dillon

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.

Kirk_Masden

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.

Marc_Dillon

You’re just wanting number rounded to 2 decimal places right?

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

Kirk_Masden

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

Zmudio

Thanks for this @Steve really helped me

Swoopy

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.

Kirk_Masden

Wow! Glad you could solve it!

Swoopy

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

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.

Marc_Dillon

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.

What is the column type here?
What option do you have set here (and does changing the option affect your result) ?
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.

JPAlpano

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.

Marc_Dillon

@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

Thanks for this @Steve really helped me a lot. 😃

SASINDHAR

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.

Ing_Fred_Fiifi_

thank you sir