Rounding issue.

[demopro]

I have a column that is in this format: 16,683.43 I need to put this in a Thousand format such as, 16.7 in place of 16,683.43

Any thought on how I can do this.

Thanks,
Demopro

 

[AnotherHiggins]

As a worksheet formula (no VBA):
[tab]=ROUND(A1/1000, 1)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[PHV]

Round(yourColumn / 1000, 1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[demopro]

Thanks for the quick response. Your suggestions work well but if the dollar amount is greater than the 16,683.43 that I gave in my example it does not work. My fault.

I found another solution to this. I went to format cells and then to numbers, custom and typed this in.

#,###0.0,;[Red]-#,###0.0,


Again, thanks for your help.

Demopro

 

[strongm]

Of course, both of the above solutions actually end up with a different value in a new cell.

What you could do is use a generally little known cell formatting trick. Try applying this custom cell format:

##.#,

 

[macropod]

hi all,

I'd be inclined to use a Custom format defined as:
0.0,
Using the '##.#,'Custom format proposed by strongm will lead to the loss of the leading 0 where the value is less than 950. You can also precede the '0.0,' with a '$' symbol if you like, plus spaces to insert some padding between the '$' and the number. For example:
$ 0.0,

Cheers

[MS MVP - Word]

 

[strongm]

I think demopro managed to sneak in a variant of your better solution, macropod, just before I posted ...