Can I prevent rounding with the Format command?

[USAMax]

I need to return a value with five decimal places.

If a value is 1.999999, the Format command returns 2.
var = 1.999999
debug.print Format(var, "#.00000")

Returns 2

I need it to return 1.99999

Mathematically I can change the value but there has got to be a better way. Can rounding be disabled?

This value can be multiplied by as much as a million so it does mater. I know 2 is closer to the original value but this is what they want.

Thank you.

 

[JoeMo]

I think your format line is short one zero after the decimal place. Try:

debug.print Format(var, "#.000000")

which matches the number of decimal places in your variable.

 

[Rick Rothstein]

I need to return a value with five decimal places.

If a value is 1.999999, the Format command returns 2.
var = 1.999999
debug.print Format(var, "#.00000")

Returns 2

I need it to return 1.99999

I don't think you should be using Format for what you are trying to accomplish. I think you should be using this to get five decimal places without rounding...

Debug.Print Int(100000*Value)/100000

 

[shg]

I think JoeMo nailed the problem.

 

[joeu2004]

[.... deleted by me ....]

 

[Rick Rothstein]

I think JoeMo nailed the problem.

The OP said...

"I need to return a value with five decimal places.
.....
var = 1.999999
debug.print Format(var, "#.00000")

Returns 2
....
I need it to return 1.99999"

 

[shg]

Misread the question, sorry, Rick.

 

[Rick Rothstein]

Misread the question, sorry, Rick.

No problem... I still think the only solution to the OP's request is what I posted in Message #3 .

 

[Marcelo Branco]

As my Excel uses comma as decimal separator this worked for me
Debug.Print Mid(var, 1, InStr(1, var, ",") + 5)

So i think this should work for who uses dot as decimal separator
Debug.Print Mid(var, 1, InStr(1, var, ".") + 5)

M.

 

[Marcelo Branco]

Or

Debug.Print Mid(var, 1, InStr(1, var, Application.DecimalSeparator) + 5)

M.