Rounding numbers up or down

[Guest]

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,

 

[Chip Pearson]

Michelle,

You can use the MROUND function. E.g.,

=MROUND(A1,0.05)

MROUND is part of the Analysis Tool Pak, so you need to have this
add in loaded (go to the Tools menu, choose Add Ins, and select
the ATP).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

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?

 

[Guest]

Just to add to my question - $25.06 would round down to $25.05.

 

[AlfD]

Hi!

And if you don't have access to the Analysis ToolPak you can use

=ROUND(A1/0.05,0)*0.05

Might need some formatting to show trailing zeroes. It will tend t
give £23.2 where you would prefer £23.20 - you can us
format>cells>currency and select 2 d.p.

Al

 

[Guest]

Thank you, that's very helpful, but is there any way of rounding them in the same cell (without having to create another column).

Thanks,

 

[Chip Pearson]

Michelle,

Sorry, but that is not possible.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Thank you, that's very helpful, but is there any way of

rounding them in the same cell (without having to create another
column).

 

[Ron Rosenfeld]

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

 

[Guest]

Thanks Ron, but when I tried it, it came up up with a syntax compile error in the statement
c.Value = Application.WorksheetFunction.Round(c.Value / 0.05, 0) *
0.05

Any ideas?

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

 

[Peo Sjoblom]

It should be on the same line

c.Value = Application.WorksheetFunction.Round(c.Value / 0.05, 0) * 0.05

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Thanks Ron, but when I tried it, it came up up with a syntax compile error in the statement
c.Value = Application.WorksheetFunction.Round(c.Value / 0.05, 0) *
0.05

Any ideas?

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

 

[Ron Rosenfeld]

Thanks Ron, but when I tried it, it came up up with a syntax compile error in the statement
c.Value = Application.WorksheetFunction.Round(c.Value / 0.05, 0) *
0.05

That should all be on the same line.


--ron

 

[Guest]

Thank you, that works well.

It should be on the same line

c.Value = Application.WorksheetFunction.Round(c.Value / 0.05, 0) * 0.05

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Thanks Ron, but when I tried it, it came up up with a syntax compile error in the statement
c.Value = Application.WorksheetFunction.Round(c.Value / 0.05, 0) *
0.05

Any ideas?

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

 

[cobien]

Hi,

I entered cell value = Mround(1.02,1)
And i get #VALUE! error.

I've read examples on the net and tried many different combination,
still got the same error.

Can someone help ?

Thank

 

[JE McGimpsey]

Should you, by any chance, be using the European syntax:

=MROUND(1,02; 1)

???

 

[Guest]

Chip -

Is it possible to have the MROUND function within a cell which already has a
formula in it, such as ("=b2")?

Thanks -
John

 

[Jerry W. Lewis]

You can change the formula to read =MROUND(B2,0.5)

Jerry

 

[Guest]

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!!

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

 

[JE McGimpsey]

One way:

=ROUND(C3/2,0)

 

[Charlie O'Neill]

Try this

=Round(C3/2,0)

Charlie

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!!

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

 

[Ron Rosenfeld]

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!!

In this instance, there is no need to use A VBA macro. Merely modify your
formula to:

=ROUND(C3/2,0)




--ron

 

[Guest]

All, I have a similar rounding situation as Michelle. I attempted the macro
you gave her, unfortunately it did not work.

I have ranges of numbers in columns B:F (I edited the macro to say B:F
instead of A:A), my numbers are not dollars and cents, just dollars. I want
them to round to the nearest 5 or 0 (I edited the macro from 0.05 to just 5).
The data is in columns/rows A3:F35 (if that makes any difference).

Here is an example of what I want to see:
184380 212040 239695 267350 295010

from:
184381 212038 239695 267352 295009

when I ran the "Michelle" macro, it changed nothing, what did I do wrong?
Or is there a better way to go about changing these ranges?

Thanks.