Skip to main content
Question

limit numeric digit count when exporting data to excel

  • August 16, 2017
  • 0 replies
  • 104 views
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!
      This topic has been closed for replies.

      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.
      • AmptivaInc
      • Author
      • Quickbase Alumni
      • August 16, 2017
      Thanks for this help!  So for what I'm doing -- does this look to be correct: 

      Round(([NPS Promoter %]-[NPS Detractor %])*100, 0.1)
      Indeed, it does look correct.  But the proof is in the pudding - ie when you export does it look correct.
      • AmptivaInc
      • Author
      • Quickbase Alumni
      • August 16, 2017
      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?
      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?
      • AmptivaInc
      • Author
      • Quickbase Alumni
      • August 16, 2017
      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.
      • MichaelBarrow
      • Quickbase Alumni
      • August 17, 2017
      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.
      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.
      • AmptivaInc
      • Author
      • Quickbase Alumni
      • August 28, 2017
      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!
      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
      Terms & ConditionsAccessibility statement