Discussions

 View Only
Expand all | Collapse all

limit numeric digit count when exporting data to excel

  • 1.  limit numeric digit count when exporting data to excel

    Posted 08-16-2017 13:54
    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!


  • 2.  RE: limit numeric digit count when exporting data to excel

    Posted 08-16-2017 13:59
    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.


  • 3.  RE: limit numeric digit count when exporting data to excel

    Posted 08-16-2017 15:08
    Thanks for this help!  So for what I'm doing -- does this look to be correct: 

    Round(([NPS Promoter %]-[NPS Detractor %])*100, 0.1)


  • 4.  RE: limit numeric digit count when exporting data to excel

    Posted 08-16-2017 16:08
    Indeed, it does look correct.  But the proof is in the pudding - ie when you export does it look correct.


  • 5.  RE: limit numeric digit count when exporting data to excel

    Posted 08-16-2017 16:18
    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?


  • 6.  RE: limit numeric digit count when exporting data to excel

    Posted 08-16-2017 16:21
    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?


  • 7.  RE: limit numeric digit count when exporting data to excel

    Posted 08-16-2017 16:56
    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.


  • 8.  RE: limit numeric digit count when exporting data to excel

    Posted 08-16-2017 22:11
    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.


  • 9.  RE: limit numeric digit count when exporting data to excel

    Posted 08-17-2017 11:22
    I wish we could -- it's a client request :(


  • 10.  RE: limit numeric digit count when exporting data to excel

    Posted 08-16-2017 22:35
    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.


  • 11.  RE: limit numeric digit count when exporting data to excel

    Posted 08-17-2017 11:23
    Great, I'll reach out today.  Thanks for the help!  Can't locate your website -- could you send it to me?  


  • 12.  RE: limit numeric digit count when exporting data to excel

    Posted 08-28-2017 16:20
    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!


  • 13.  RE: limit numeric digit count when exporting data to excel

    Posted 08-28-2017 23:09
    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