Thread: Custom Numeric Format (Excel 2003/2002)

1. 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. 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. 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. 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. Re: Custom Numeric Format (Excel 2003/2002)

Hans,

thanks! I really needed that.

6. 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. 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>

8. 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. 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
•