Rounding numbers to quarters after entry

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I need to enter numbers into cells and have the numbers
converted to the nearest quarter (in decimal format) in
that same cell - for example, if I enter the number 2.38
into cell A1, after I type "2.38" and hit enter I need the
number in A1 to be converted to 2.50. Any ideas? I can
use a function such as MROUND(A1,0.25), but I don't know
how to apply a formula to the same cell I'm entering the
data into; likewise, I could play around with some
formatting such that it's displayed per my needs but the
number itself isn't changed. Any help or suggestions
would be much appreciated...

Thanks,

Chris
 
I need to enter numbers into cells and have the numbers
converted to the nearest quarter (in decimal format) in
that same cell - for example, if I enter the number 2.38
into cell A1, after I type "2.38" and hit enter I need the
number in A1 to be converted to 2.50. Any ideas? I can
use a function such as MROUND(A1,0.25), but I don't know
how to apply a formula to the same cell I'm entering the
data into; likewise, I could play around with some
formatting such that it's displayed per my needs but the
number itself isn't changed. Any help or suggestions
would be much appreciated...

Thanks,

Chris
Click to expand...

You cannot "apply a formula to the same cell". You need a VBA routine called
an event macro.

Try this:

Right click on the worksheet tab and select View Code.

Paste the following into the window that opens:

===========================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range

Application.EnableEvents = False

Set AOI = [A1:A100] 'or whatever cells you want

If Intersect(Target, AOI) Is Nothing Then GoTo Bye
If Not IsNumeric(Target.Value) Then GoTo Bye

Target.Value = Application.WorksheetFunction.Round(Target.Value * 4, 0) / 4

Bye: Application.EnableEvents = True
End Sub
===========================================

See if that does what you want.


--ron
 
Chris said:
likewise, I could play around with some
formatting such that it's displayed per my needs but the
number itself isn't changed.
Click to expand...


What happens if you have 'precision as displayed' set? (It's on the
calculation tab of options)

Beware, though. This affects all your calculations. Also, it actually
changes the data in the cells to match the format. For example set a cell to
display 1 decimal place and enter 13.4 - no problem. Now change the format
to 0 decimal places and back to 1 again. The number has become 13.0.

I don't use 'precision as displayed' for the above reason, but it may suit
your purpose. If in doubt, Ron's VBA code is a safer bet.

Geoff
 
Back
Top