can i round in excel and have the result appe

Microsoft Excel 2003 (full product)
December 10, 2010 at 09:50:18
Specs: Windows XP
I have several columns of prices i need to round to the nearest 5. I know how to do this using a formula in a differnt column, but i'd like to know if i can do it just in the actual cell where the original number is. For example, if Cells A1 to A10 have random numbers in them, i want to round them to the nearest 5 without doing it in a seperate coulmn and pasting them back to the original cell.

See More: can i round in excel and have the result appe

Report •

#1
December 10, 2010 at 10:33:37
How did the "random numbers" get into A1:A10?

Are they the result of a formula, a manual entry, a macro, cut & paste, something else?

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 10, 2010 at 11:05:58
The numbers are a manual entry.

Report •

#3
December 10, 2010 at 11:19:07
The only way to enter a number in a cell and have Excel automatically perform an operation on that same cell is with a Worksheet_Change macro written in VBA.

Now that we know that, we need a little more information before we could offer any code.

re: "For example, if Cells A1 to A10..."

Since the code needs to act a specific range of cells, such as a column or a row or a specific range such as A1:A10, you'll need to be more specific than "For example...". Where exactly are you entering these numbers?

re: "i need to round to the nearest 5"

Give us some examples of your prices and what they should round to. "To the nearest 5" could mean a number of things:

e.g. Does rounding $123.57 "to the nearest 5" mean $123.55 or $125 or something else?

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
December 10, 2010 at 13:29:38
Ok, the exact cells are C1158 to C1204. And then I need to repeat for columns D, e, f, g, h. The numbers are all whole numbers. So 1921 would become 1920 and 2128 would become 2130.
There are several more blocks of cells that need the same thing.

Report •

#5
December 10, 2010 at 13:50:36
re: "the exact cells are C1158 to C1204. And then I need to repeat for columns D, e, f, g, h."

Right click the sheet tab for the sheet in which you want this to happen and choose View Code.

Paste this code in the pane that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("C1158:H1204")) Is Nothing Then
   Target = Round(Target / 5, 0) * 5
  End If
End Sub

re: "There are several more blocks of cells that need the same thing."

I guess we'll have to leave that part to you since VBA can't interpret what "several more blocks of cells" means.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#6
December 10, 2010 at 13:59:22
Okay i pasted that in, do i have to do something to make it take effect?

Report •

#7
December 10, 2010 at 14:18:22
Yeah, enter a value in any cell within the target range of C1158:H1204.

or...

Maybe I missed something:

Are these numbers already in the cells and you want to round them or do want the rounding to take effect as soon as you manually enter a number into any cell in the range?

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#8
December 10, 2010 at 14:21:18
The numbers are in the cells already.

Report •

#9
December 10, 2010 at 18:31:17
Since you have multiple ranges to deal with, I'll offer some code that "rounds" any and all selected cells.

Place the code in the VBA window, select a range of cells and click the green Run arrow at the top of the VBA editor.

You can also press F5.

If you need to do this often, you can attach the code to a button in your workbook. You can find the steps to do that via your favorite search engine.

I suggest that you try the code in a backup copy of your workbook since macros cannot be undone.

Option Explicit
Sub Rounder()
Dim myCell As Range
For Each myCell In Selection.Cells
   Range(myCell.Address) = Round(myCell / 5, 0) * 5
  Next
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#10
December 11, 2010 at 09:29:14
Okay that works now........
Thanks for your help I appreciate it.

Report •

Ask Question