Rounding to 10 in Cell Formating

[kevbiff]

I'd be grateful if anybody could help...............

I am aware that numbers can be rounded using various formulas, or indeed they can also be rounded in the format cell fuction to the nearest 1,000. However, does anybody know of a way a range of numbers can be formatted using the "format cell" custom option, to round to the nearest 10, i.e 1,621,012 becomes 1,621,010 or 289 becomes 290. I would wish the original values to remain the same, but the veiw only to be altered.........

Hope that makes sense and somebody can help

[Fotis1991]

If you like to use a formula, then..this does the job.

=ROUND(A1,-1)

[kevbiff]

Hi fotis1991 and thanks for your reply.

I was aware of the rounding formula and the others available like MROUND etc but I need to be able to change a range of cells and not create a new cal etc

[MrShorty]

When I've looked for this, I have been unable to find it. I'm using an older version of Excel, so it is technically possible that MS has added it to later versions, but I doubt it. If I'm wrong, someone will step in and correct me. The only way I'm aware of to get exactly what you want is to use a helper column with one of the ROUND functions.

Excel's formatting knows how to round a number to a fixed position to the right of the decimal point. Formatting approaches I have used for this involve ways of displaying the value with the decimal point moved to the left. One approach is to use the comma (,) code in your number format. The comma causes the value to display with the decimal moved 3 places to the left. So a format code of "0.00," will display your sample value as 1621.01 effectively meaning "one thousand six hundred twenty-one point zero one thousand units". The other approach I have used is to use a scientific or engineering format (I know, only us scientists and engineers will like or even understand it). "0.00000E+0" displays this number as 1.62101E+6. Variations on those two themes is how I accomplish what you are asking for.

[kevbiff]

Well MrShorty, thats impressive stuff. Sadly, I think you may be right and what I'm after is not possible. I needed to try and avoid further columns etc as not to confuse other users etc.......looks like I may have no option :-(