Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    533

    Unanswered: Formula to return the decimal of a Value

    Is there another formula that will return just the fraction value of a decimal number. I came up with this:

    =A1-INT(A1)

    If A1 has 5.3 this would return .3
    I've seen another way of doing this but don't remember what it is.
    ~

    Bill

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Your formula won't produce the correct result if A1 contains a negative number.

    If negative numbers are not a concern then you can simplify it to:
    Code:
    =MOD(A1,1)
    If negative numbers are a concern then you could use a variation such as:
    Code:
    =ABS(MOD(A1,SIGN(A1)))
    Hope that helps...

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Colin,

    This works great in a formula.
    =MOD(A1, 1)

    In VB this does not work due to the rounding of values by the Mod Operator. I was looking for some options that would work well in VB.

    val = Range(A1) Mod 1

    The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result.

    I'm pasting some values from a pivot table into another sheet and want to set the decimal to 1 place if it has a decimal or 0 decimals if it is a whole number. The original formula I posted is doing the trick. I was curious about optional ways of doing this.

    Thanks for the suggestions.
    ~

    Bill

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Bill,

    There are some differences between the VBA Mod operator and the Excel Mod worksheet function.

    If you want to, you can indirectly call the Excel Mod worksheet function in VBA by evaluating the formula expression. For example:
    Code:
    Debug.Print CStr(Sheet1.Evaluate("=MOD(A1,1)")
    Hope that helps...

Tags for this Thread

Posting Permissions

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