Results 1 to 2 of 2

Why does Excel add "extra" numbers in the 10-16 decimal place holders?

This is a discussion on Why does Excel add "extra" numbers in the 10-16 decimal place holders? within the Excel Questions forums, part of the Question Forums category; Greetings all! This is probably an easy question for the experienced, but could someone tell me why: When I dimension ...

  1. #1
    New Member
    Join Date
    Nov 2009
    Location
    Wisconsin, U.S.A.
    Posts
    24

    Default Why does Excel add "extra" numbers in the 10-16 decimal place holders?

    Greetings all!

    This is probably an easy question for the experienced, but could someone tell me why:
    When I dimension a numeric decimal value as a single or double in VBA code (such as 5.05, etc.), why does excel turn it into 5.0499999999999998?

    I've tried using the round function as cleverly as I can, including changing the number from 5.05 to 5.051 and then rounding to 2 decimal places, but it doesn't make a difference.
    I'm thinking that the issue is with excel because I'm using the value(s) in the spreadsheet to populate fields on a Word mail merge main document and even when the Excel cell displays the correct value in the cell (5.05), the value in the Word merge field ends up 5.0499999999999998. Not to mention, sometimes even though the correct value is in the cell, when it is selected as the active cell the value that appears in the formula bar is not 5.05 but 5.049999999999999998.

    Anyone have any ideas as to why this is happening? I have other hard coded values within the code and they work just fine, but they're positive, whole, real numbers.

    Thanks in advance for your time!

    --Patrick

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,115

    Default Re: Why does Excel add "extra" numbers in the 10-16 decimal place holders?

    Excel has trouble handling decimals, because numbers in Excel are generally in Base 2, so what you get is the nearest representation.
    To get accurate decimals you can use the Decimal data type in VBA (declare it as a Variant, then use the CDec function) or you can use Currency.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

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
  •  


DMCA.com