i have number in cell lets say --



1) 1,234,567, how can i change this number to 1,200,000

2) if i want change the values from 1,234,567 to 1.2 since (i am putting a comment --- "all values in million), can i do that?

Regards

Umesh Banga

Hi umesh,

Do you want to change the value, or just what's displayed? Are the numbers rounded to the nearest 0.1, rounded up, or rounded down?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I want to change just whats displayed.

I think numbers are rounded up so - if number is 162.50 or above it is showing 163 and if the number is 162.49 or below - it showing 162.

however my numbers are in millions; and for the purposes of chart and legend in the chart - I want to convert the number (at least for display purposes) from $1,623,536 to $1,623 in the chart and the legends of the chart.

I have tried using custom number format - with $#, it then changes the number from $1,623,536 to $1623. I do not know why it is not showing the comma after 1 to make the number appear like $1,623.

Please advice.

Regards

Umesh Banga

Hi umesh,

Try a custom format as:
$0,000,

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi umesh,


You're not going the be able to get a display of just 1.2, or 1,200,000 without at least using a formula to change the value (eg =ROUND(numberORreference,-5) or =ROUND(numberORreference/1000000,1) either in the same cell or in another cell) .

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,

it worked for me thanks. Actually i entered $#,###, instead of using 0's and I got the result I wanted. I will try using the tip related to "round" too.

Thanks a lot.