Using format for rounding to the neasest hundreds place

  • Thread starter Thread starter Tony @ Work
  • Start date Start date
T

Tony @ Work

Is there a format that I can enter which will
automatically format the number that I type in to the
nearest hundreds place? ie. 124 -> 100, 551-> 600, 1234->
1200.
 
=MROUND(A1,100)

If the MROUND function is not available: Tools>Add-ins, check Analysis
Toolpak

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
I now realize that doesn't answer your question completely, because the
result is not in the same cell. But can you deal with it?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Supposing D8 containing the figure u wanted to round, then type th
following to round them.

=ROUND(D8/100,0)*100

Play with dividing 1000, 10000 for bigger numbers.

Hope that help
 
Tony
I put together an event macro that will round number to the nearest hudred when you enter them. This will actually round them, not just the display. I haven't come up with any way to display the number as rounded

1) Open a new workboo
2) right click on the sheet tab and select View Cod
3) Delete what is already there and paste in the following

Private Sub Worksheet_Change(ByVal Target As Excel.Range
'Round entry to nearest hundre
xxx = ActiveCell.Offset(-1, 0
xxx = Int((xxx + 50) / 100) * 10
ActiveCell.Offset(-1, 0).FormulaR1C1 = xx
End Su

Now when you enter a number it will automatically round to the hundreds. This will effect any number you enter onto the sheet. It also assumes that your cursor moves down one cell when you hit enter. And worst of all, it craps out if you type text

I don't do much in the way of VB programming but I figured this might get you started

Good Luck
Mark Graesse
(e-mail address removed)

----- Tony @ Work wrote: ----

Is there a format that I can enter which will
automatically format the number that I type in to the
nearest hundreds place? ie. 124 -> 100, 551-> 600, 1234->
1200
 
Back
Top