up vote 0 down vote favorite
share [g+] share [fb]

I would like to change the format predefined with Excel 2007 for million in the previous versoin I could do that but havent found a way to do this.

For example I have this:

1,235,000,00

and I want this:

1,200,000.00

link|improve this question
4  
Do you want to change the thousands separator from '.' to ','? Or do you want to truncate the value 1235000 -> 1200000? – Daisetsu Feb 10 '11 at 0:16
feedback

1 Answer

up vote 1 down vote

Excel doesn't support rounding to significant digits as a number format, you have to calculate it using formulas: ROUND, ROUNDUP, and ROUNDDOWN mainly.

e.g. =ROUND(A1,1-INT(LOG10(ABS(A1)))) will round A1 to 2 significant digits

e.g.2 =ROUND(A1,A2-1-INT(LOG10(ABS(A1)))) will round A1 to A2 significant digits

link|improve this answer
but it can't use this formating for millions 1,560,500.00 – Alejandro Garcia Feb 10 '11 at 16:12
I'm not sure what you're asking here. I've adjusted the answer so it's more general and will round to any number of significant digits. If you are asking about commas and decimal places once you've rounded, then you just use normal cell formatting to achieve that. – Rhys Gibson Feb 13 '11 at 7:34
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.