Results 1 to 9 of 9

Thread: Very large numbers automatically rounded down to nearest thousand?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Very large numbers automatically rounded down to nearest thousand?

    Good morning,
    I'm working in MS Excel for Office 365 (MSO 16.0.10730.20344) 64-bit.

    I'm copying large numbers over from a table to a new sheet. They're all numbers stored as text.
    I can convert them into numbers. They're all different numbers stored as text, but when I convert them back to numbers, they're all suddenly the exact same number... Even if I just write out the number as a number, it changes into a new number.

    When converting from number stored as text back to a number, since the digit is so large, it creates an exponent which is fine but when I format that as a number, it simply rounds down to the nearest thousand making all of the numbers I have equal to the same number. Even if I just write out the number without the preceding double zeros as 100728680042858447, excel immediately changes it to 100728680042858000. It's rounding down to the nearest thousand?

    Number Stored as Text ex 1: 00100728680042858447
    Number Stored as Text ex 2: 00100728680042858384
    Number Stored as Text ex 3: 00100728680042858004When converted back to a number they all show: 100728680042858000

    I'm trying to perform a COUNTIFS with these values, but huge chunks of values are being counted as the same value when they're not the same value at all. Excel is just rounding them all down removing the digits that're making them unique values.

    Any way to turn this off or get around it?

    Thank you!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Very large numbers automatically rounded down to nearest thousand?

    Xl only displays numbers to 15 significant digits, so you I think will need to keep them as text.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Very large numbers automatically rounded down to nearest thousand?

    Dang. If that's the case, I should be able to truncate the first 5 digits or so and use that number instead?
    Thanks for the quick reply.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Very large numbers automatically rounded down to nearest thousand?

    That should work as long as the first 5 digits will always be same.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Very large numbers automatically rounded down to nearest thousand?

    What is your count trying to accomplish? Can you keep the text representation of the numbers and use that in the countifs?

  6. #6
    New Member
    Join Date
    Apr 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Very large numbers automatically rounded down to nearest thousand?

    Quote Originally Posted by Fluff View Post
    That should work as long as the first 5 digits will always be same.
    Yep. The first 8 or so digits will be the remaining the same for a very long time, but reducing it down the maximum 15 length would ensure a safer approach and shouldn't have any issues. Thanks!

    [QUOTE=kweaver]What is your count trying to accomplish? Can you keep the text representation of the numbers and use that in the countifs?[/QUOTE}

    I do actually need to use numbers, or rather want to. I have a large list of numbers that I'm trying to count the number of unique values based on a few different criteria without having to use arrays or pivot tables. I do actually have the same value listed about 5 times, but because apparently excel stop at the first 15 unique characters of a number then rounds it down, it's showing that I have more like 70 of the same value counted instead of the 5 I'm expecting.

  7. #7
    New Member
    Join Date
    Apr 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Very large numbers automatically rounded down to nearest thousand?

    Actually, I'm just going to split the large number into two columns. One for the first 5 digits and a second for the remaining 15 digits. If need-be, I'll just reference the first 5 digits if I ever need to, but for my application I don't think I will.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Very large numbers automatically rounded down to nearest thousand?

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Very large numbers automatically rounded down to nearest thousand?

    Quote Originally Posted by WSBirch View Post
    Actually, I'm just going to split the large number into two columns. One for the first 5 digits and a second for the remaining 15 digits. If need-be, I'll just reference the first 5 digits if I ever need to, but for my application I don't think I will.
    That sounds like a good move.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •