Undo Removal of Trailing Zeros

[NetFlash]

Hello Guys,

I've stumbled upon a "feature" in Excel i can't exclude.
1000 0.188
2000 -0.401
3000 0.000
4000 -1.102

When you set the display format for numeric 2digits, Excel changes the Display not the underlying Value
Display Value
0.19 0.19
-0.40 -0.4
0.00 0
-1.10 -1.1

How do i STOP excel from removing trailing zeros.
I tried Tools>Options>Calculations>Precision as Displayed.
This works well with numbers not ending on 0. But numbers ending with 0 still get truncated.

I do Not want to convert the values to text. They have to stay numeric as they have to be rounded off automatically, and used to generate code.
so lets say if i fill in 0.40267 -> rouds off to 0.40
And what i get now is 0.4 which is unusable for the generated code.

Any ideas??

 

[NetFlash]

Hmm Please check.

Can i write out this

colC = Format(Abs(cellData.Offset(, 1)), "0.0") & IIf(cellData.Offset(, 1) < 0, "-", "")

To this:

        colC = Format(Abs(cellData.Offset(, 1)), "0.00")
        If (cellData.Offset(, 1) < 0) Then
            colC = Abs(cellData.Offset(, 1)) & "-"
        Else
            colC = cellData.Offset(, 1)
        End If

I've tested it and it doesn't work. i does not format to 0.00.
Please help me find the loose screws....

 

[pgc01]

Hmm Please check.

Can i write out this

colC = Format(Abs(cellData.Offset(, 1)), "0.0") & IIf(cellData.Offset(, 1) < 0, "-", "")

To this:

        colC = Format(Abs(cellData.Offset(, 1)), "0.00")
        If (cellData.Offset(, 1) < 0) Then
            colC = Abs(cellData.Offset(, 1)) & "-"
        Else
            colC = cellData.Offset(, 1)
        End If

I've tested it and it doesn't work. i does not format to 0.00.
Please help me find the loose screws....

Hi

If you want like in the second snippet, try:

        colC = Format(Abs(cellData.Offset(, 1)), "0.00")
        If (cellData.Offset(, 1) < 0) Then colC = colC & "-"

 

[NetFlash]

I totally understand.

Thanks again for the speedy response.