Custom Numeric Format (Excel 2003/2002)

Topic

New Reply

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 D6:D19 & 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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Hans,

thanks! I really needed that.

Reply | Quote

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.

Reply | Quote

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

Dim dFracPart as Double
    dFracPart = MyCell.Value - Int(MyCell.Value)

Reply | Quote

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

Dim dFracPart as Double
    dFracPart = MyCell.Value - Int(MyCell.Value)

Reply | Quote

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

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

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

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

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.

Reply | Quote

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.

Reply | Quote

Hans,

thanks! I really needed that.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote