Rounding numbers up or down
 

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

One way:

=ROUND(C3/2,0)

In article ,
boyshanks wrote:

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.

Try this

=Round(C3/2,0)

Charlie

"boyshanks" wrote in message
...
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

On Sat, 4 Dec 2004 07:13:04 -0800, boyshanks
wrote:

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

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.

"Ron Rosenfeld" wrote:

On Sat, 4 Dec 2004 07:13:04 -0800, boyshanks
wrote:

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

On Fri, 10 Jun 2005 12:54:01 -0700, "T" wrote:

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.

How do the numbers get into cells B3:F35? Manual entry or formulas?

If they are the results of formulas, then modify the formula to be:

=ROUND(your_formula/5,0)*5

If they are entered manually, one at a time, then the modification of
"Michelle's" event formula should work:

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

Set AOI = [B:F] ' or [B3:F53]

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 / 5, 0) * 5
End If
End If
Next c

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

Post back with some more info.

Best,

--ron

Ron, they are formulas for the most part - I'd like to automate the whole
thing with formulas. I'll try to modify the formula to see if it works.
Thanks for the rapid reply!
T

"Ron Rosenfeld" wrote:

On Fri, 10 Jun 2005 12:54:01 -0700, "T" wrote:

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.

How do the numbers get into cells B3:F35? Manual entry or formulas?

If they are the results of formulas, then modify the formula to be:

=ROUND(your_formula/5,0)*5

If they are entered manually, one at a time, then the modification of
"Michelle's" event formula should work:

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

Set AOI = [B:F] ' or [B3:F53]

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 / 5, 0) * 5
End If
End If
Next c

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

Post back with some more info.

Best,

--ron

Ron, the formula is working beautifully, but it doesn't stay a formula in the
cell. I use this spreadsheet over and over and don't want to have to "type"
the formula over and over - any suggestions?
T

"Ron Rosenfeld" wrote:

On Fri, 10 Jun 2005 12:54:01 -0700, "T" wrote:

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.

How do the numbers get into cells B3:F35? Manual entry or formulas?

If they are the results of formulas, then modify the formula to be:

=ROUND(your_formula/5,0)*5

If they are entered manually, one at a time, then the modification of
"Michelle's" event formula should work:

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

Set AOI = [B:F] ' or [B3:F53]

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 / 5, 0) * 5
End If
End If
Next c

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

Post back with some more info.

Best,

--ron

Sorry for bothering you on this one - I was working on the spreadsheet with
the macro in it and that's why the formula wasn't overwriting the cells.
Thanks again for your help!

"T" wrote:

Ron, the formula is working beautifully, but it doesn't stay a formula in the
cell. I use this spreadsheet over and over and don't want to have to "type"
the formula over and over - any suggestions?
T

"Ron Rosenfeld" wrote:

On Fri, 10 Jun 2005 12:54:01 -0700, "T" wrote:

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.

How do the numbers get into cells B3:F35? Manual entry or formulas?

If they are the results of formulas, then modify the formula to be:

=ROUND(your_formula/5,0)*5

If they are entered manually, one at a time, then the modification of
"Michelle's" event formula should work:

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

Set AOI = [B:F] ' or [B3:F53]

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 / 5, 0) * 5
End If
End If
Next c

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

Post back with some more info.

Best,

--ron

On Fri, 10 Jun 2005 14:50:03 -0700, "T" wrote:

Sorry for bothering you on this one - I was working on the spreadsheet with
the macro in it and that's why the formula wasn't overwriting the cells.
Thanks again for your help!

"T" wrote:

Ron, the formula is working beautifully, but it doesn't stay a formula in the
cell. I use this spreadsheet over and over and don't want to have to "type"
the formula over and over - any suggestions?

Glad it's working for you. Thank you for the feedback.


--ron

Thank you, The Macro To round to Nearest 5 Cents Works On New Entries But
Not On Existing Cells With Formulars
can you Sugest Anything Else


Regards

Giorgos

Thank You,

I will try This nd get Back to you In a couple Days

"
Thank you,

"The Formular You gave me MROUND(a1/0,05/0)80.05)
Works fine but it erases all formulars in selected cells
which I don't want is there another Way?

Rounding numbers up or down
 

Ron

Thanks for posting this macro. This was just what I needed. Works perfectly.
Thanks again!

"Ron Rosenfeld" wrote:

On Fri, 10 Jun 2005 14:50:03 -0700, "T" wrote:

Sorry for bothering you on this one - I was working on the spreadsheet with
the macro in it and that's why the formula wasn't overwriting the cells.
Thanks again for your help!

"T" wrote:

Ron, the formula is working beautifully, but it doesn't stay a formula in the
cell. I use this spreadsheet over and over and don't want to have to "type"
the formula over and over - any suggestions?

Glad it's working for you. Thank you for the feedback.


--ron