Forum Home
Press F1
Thread ID: 26451	2002-10-27 21:30:00	Excel 2000 number formats	rugila (214)	Press F1

Post ID	Timestamp	Content	User
93594	2002-10-27 21:30:00	Hail all you Excel experts. I want to format numbers to the nearest 100 or 1,000 or 1,000,000 (the latter isn't my bank balance unfortunately) or whatever. It's easy enough to round numbers but this changes the numbers themselves and not just the way they are displayed. Excel is OK for rounding number displays (ie formatting them) AFTER the decimal point, eg it can show 3.14159265 as 3.14 without changing the underlying number. But how can you get it to show 314,159,265 as 314,000,000 without changing the underlying number? It's not too much trouble to write a macro to do this, but writing and keeping track of piles of macros to do the things that Excel should do but doesn't can be a bit if a bind. Am I missing something in the Excell formatting?	rugila (214)
93595	2002-10-27 21:38:00	Rugila, Does the Excel function ROUNDDOWN do the trick for you... e.g. ROUNDDOWN(314567890,-6) gives 314000000 Cheers, Babe.	Babe Ruth (416)
93596	2002-10-28 03:05:00	I think the round function would be better if you wanted to go to the NEAREST million, eg =ROUND(314567890,-6) This would give 315000000	Oxie (1318)
93597	2002-10-28 03:41:00	Nope, I can't find anything either. You could simply have a cloumn with the real values, and a column with the display values, and hide the column with the real values. If you cre always rounding to the same no. of sig fig.s, then you could use: =ROUND(314567890,3-LEN(INT(314567890))) to give 315000000. (Three sig. fig.) Or =ROUND(314567890,5-LEN(INT(314567890))) to give 314570000 (5 sig fig). G P	Graham Petrie (449)
93598	2002-10-28 05:26:00	What about a column with the real values and another with the round function? In Graham's example above it would then become: =ROUND(A1,3-LEN(INT(A1))) If the length varies you could try an IF statement along the lines of =IF(LEN(A1)=3,(ROUND(A1,1-LEN(INT(A1)))),(IF(LEN(A1)=4,(ROUND(A1,2-LEN(INT(A1))))))) This works for 3 or 4 numbers. Each new variation is a false for the previous one. The number of brackets required is the hardest part of working this out!	Heather P (163)
93599	2002-10-28 06:09:00	Thanks for comments and suggestions. However the round function changes the number itself, not just the way it displays. Keeping two sets of numbers, one original and the other rounded, may be OK for some purposes but it still requires two sets of numbers, not just one which can be dispalyed in different ways. I find writing a macro to do rounding etc. generally much neater and more flexible than using the "IF" function, especially in extensive spreadsheet work. Someone asked once on this forum how to (say) add disjunct or randomly scattered numbers in Excel. Easy enough with macros (eg. colour cells to be added yellow, and select yellow cells, (or whichever colour code ) with the macro, or select by formatting as bold, or different font size or whatever). Do I take it then that nobody knows of anything in the built-in Excel formatting that will just change the display (of the one number) without changing the number itself when displaying to the nearest 1000 etc?	rugila (214)
1