Ron thanks so much for your help on this matter. I have a slightly
different rounding issue, but I don't know how to modify your macro to
achieve my needs.

WHAT I WANT:

I have a cell which contains the formula: C3/2. When that formula returns a
number such as 9.32, I want it to round DOWN to 9. When that formula returns
a number such as 9.512, I want it to round UP to 10. All in the same cell.

Can you help me with this? I did load the Data Analysis ADD IN but it is
very confusing to me.

Thanks in advance!!

"Ron Rosenfeld" wrote:

On Thu, 22 Jul 2004 16:04:01 -0700, "Michelle"
wrote:

Is there a function (or other method) that we can use to round currency to the nearest 5 cents, ie $27.28 would round up to $27.30, and $30.01 would round to $30.00?

Thank you,

If I understand you correctly, and I've read some of your responses, you want
to enter a number in a cell, and have it round -- in that cell -- to the
nearest five cents.

That can be done with an event macro. To enter the macro, right click on the
worksheet tab, select View Code, and paste the code below into the window that
opens.

Set AOI equal to the range in which you want this effect to occur. In the
example, it occurs in Column A.

Any number which you enter in Column A will be rounded to the nearest 0.05.

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

Set AOI = [A:A]

Application.EnableEvents = False

If Intersect(Target, AOI) Is Nothing Then GoTo DONE

For Each c In Target
If Not Intersect(c, AOI) Is Nothing Then
If IsNumeric(c) And Not IsEmpty(c) Then
c.Value = Application.WorksheetFunction.Round(c.Value / 0.05, 0) *
0.05
End If
End If
Next c

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


--ron