limit numeric digit count when exporting data to excel

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
I have several numeric formula fields that have been set up to display only one digit after the decimal.  The issue is that when exported to excel, all of these fields are displaying two digits after the decimal.  Is there a simple way to control this so it remains consistent as one digit after the decimal like it is within the quickbase reports?

1.1 vs. 1.12

thanks!
Photo of Pro Survey Solutions

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
Like Excel, Quick Base calculates to many decimal places and  in the display format you can set it to only show say 1 decimal place.  But under the covers it retains all the decimals in a calculation.

If you want to round a calculated field to one decimal place you would use a format like this

Round([Hours] * [Rate],0.01)

That would, for example, round to the nearest .01 - penny)

You could also do, for example 

Round([Qty Produced] / [Minutes]),0.1)

to round to the nearest tenth.
Thanks for this help!  So for what I'm doing -- does this look to be correct: 

Round(([NPS Promoter %]-[NPS Detractor %])*100, 0.1)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
Indeed, it does look correct.  But the proof is in the pudding - ie when you export does it look correct.
I just exported it and it does not look correct... still exporting all of the decimals.  Any other ideas on what might be off when looking at my formula?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
The round looks correct.  What does it look like in Quick Base when you set that field to have blank for the number of decimals.  is it correct in Quick Base?
When it's set to be blank for number of decimals it shows all of the numbers again after the decimal, which varies from record to record... so this is functioning correctly.  It's just the export that's still not doing it.
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
This has always annoyed me when exporting. The simplest thing might be to just live with the issue, and then open up the Excel sheet and highlight the chunk of numbers that need to go back to 1 digit and make it so with Excel formatting.
I wish we could -- it's a client request :(
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
Something is strange here.  Contact me via the Contact into on my website and I will have a quick look if you like.  It is simply not possible that the Round function is not rounding.
Great, I'll reach out today.  Thanks for the help!  Can't locate your website -- could you send it to me?  
(Edited)
Hi again -- Was out last week but still working this issue.  Could you send me your contact info so I can touch base to try to work through this one?  Thank so much!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
Sorry for the slow response today but I have nebeen unable to sign on to the Forum today on most of my devices. I can be reached via the gmail address on my website. QuickBaseCoach.com