Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Numeric Format (Excel 2003/2002)

    I'm stumped. I've written a macro to apply a numeric format based on a cell's value. In the attached file, the value entered in cell D3 is used to calculate the range D619 & F6:F19. Formatting should be applied as follows:
    Values less than 1 & values with decimal places - 0.0#
    Whole positive numbers - 0, no decimal places.

    The macro is working fine except in cell F19 when some multiples of 12 are entered in D3. For example when D3 is 12 or 24, F19 shows a decimal place, which it shouldn't. However, when D3 is 36 no decimal place appears, which is correct. All 3 values return a whole positive # with no decimal places. It doesn't appear to be a rounding error & I believe my macro logic to be correct, but I can't see where the problem is. Any help would be much appreciated. thanks,
    P.S. the code is :

    Range("f17:f19").Select
    Dim MyCell
    For Each MyCell In Selection
    If MyCell.Value < 1 Then ' #'s less than 1
    MyCell.NumberFormat = "0.0#"
    ElseIf MyCell.Value / Int(MyCell) = 1 Then 'whole #'s, no decimals
    MyCell.NumberFormat = "0"
    Else
    MyCell.NumberFormat = "0.0#" ' whole #'s with decimals
    End If
    Next

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Custom Numeric Format (Excel 2003/2002)

    It's due to tiny rounding errors. Excel stores numbers in binary format, while they are displayed as decimal numbers. The conversion between decimal and binary is exact, resulting in very small rounding errors. Normally, Excel suppresses these, but in this situation, they affect the calculations. The result of MyCell.Value / Int(MyCell) is not excactly 1 for cell F19, there is a difference of 2.22044604925031*10^-16 or about 0.0000000000000002. If you replace the line

    ElseIf MyCell.Value / Int(MyCell) = 1

    by

    ElseIf MyCell.Value Mod 1 = 0

    you should be safe.

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Numeric Format (Excel 2003/2002)

    Hans,

    Thanks so much. That seems to have done it. I suspected it may have been some rounding, but when I displayed all 15 decimal places they were all 0. What does this line actually saying.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Custom Numeric Format (Excel 2003/2002)

    The Mod function computes the remainder of the first number after dividing by the second number. MyCell.Value Mod 1 is the remainder of the cell value after dividing by 1, i.e. the fractional part. If this is 0, the cell value was a whole number.

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Numeric Format (Excel 2003/2002)

    Hans,

    thanks! I really needed that.

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Numeric Format (Excel 2003/2002)

    I thought I had it licked, but......I understand the logic, but in practice it just isn't working! As recommended I changed the line to read: MyCell.Value Mod 1=0. But when I run the macro in Step mode with a watch, a cell containing the value 2.4 MyCell.value mod 1 evaluates to 0, when it should in fact be .4. As a result it's not applying the correct numeric format. Even when I change the line to read MyCell.Value mod 1 >0 It's still not applying the correct format. I don't know what to do here. I'm hoping there's just some little thing I've missed. Any suggestions??? Thanks.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Numeric Format (Excel 2003/2002)

    The MOD function returns an interger result, therefore it will not do what you are trying to do. Try this:

    <pre>Dim dFracPart as Double
    dFracPart = MyCell.Value - Int(MyCell.Value)
    </pre>

    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Custom Numeric Format (Excel 2003/2002)

    Hmm, apparently MOD acts differently from what I expected. Try this:

    <code>ElseIf Abs(MyCell.Value - Round(MyCell.Value)) < 0.000000001 Then</code>

    This version compares the cell value to the value rounded to a whole number, and if the difference is very small, assumes that the number itself was a whole number. The 0.000000001 is to take small rounding errors into account.

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Numeric Format (Excel 2003/2002)

    That one did it. Thanks!!!!!!!!!!!

Posting Permissions

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