H
HelpExcel
I need to round currency to the nearest dollar amount
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gary''s Student
=ROUND(A1,0)
H
HelpExcel
This is not working. I get a circular reference error. I read in one of the
discussion's that Excel 2000 and up do not round correctly. Are you aware of
this issue?
discussion's that Excel 2000 and up do not round correctly. Are you aware of
this issue?
G
Gary''s Student
That is not our problem. What cell are you try to round and what is its
contents??
contents??
H
HelpExcel
I have a range of cells that are blank right now. I set up the formula like
below and it states that is a circular reference error. Then I try $15.50
and the formula goes away. I just want to round any amount with two decimals
like $15.50 to $16.00 or $12.30 to $13.00.
below and it states that is a circular reference error. Then I try $15.50
and the formula goes away. I just want to round any amount with two decimals
like $15.50 to $16.00 or $12.30 to $13.00.
F
Francis
how about in B2, place this
=ROUNDUP(A2,0)
--
Hope this is helpful
Pls click the Yes button below if this post provide answer you have asked
Thank You
cheers, francis
Am not a greek but an ordinary user trying to assist another
=ROUNDUP(A2,0)
--
Hope this is helpful
Pls click the Yes button below if this post provide answer you have asked
Thank You
cheers, francis
Am not a greek but an ordinary user trying to assist another
H
HelpExcel
that worked but is it possible to have it round in the same column of A2?
G
Gary''s Student
Rounding "in place" is possible, but it needs a macro.
Install the following event macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:A")
If Intersect(r, Target) Is Nothing Then Exit Sub
Set t = Target
Application.EnableEvents = False
t.Value = Application.WorksheetFunction.Round(t.Value, 0)
Application.EnableEvents = True
End Sub
Because it is worksheet code, it is very easy to install and use:
1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window
If you save the workbook, the macro will be saved with it.
To remove the macro:
1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
To learn more about Event Macros (worksheet code), see:
http://www.mvps.org/dmcritchie/excel/event.htm
Install the following event macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:A")
If Intersect(r, Target) Is Nothing Then Exit Sub
Set t = Target
Application.EnableEvents = False
t.Value = Application.WorksheetFunction.Round(t.Value, 0)
Application.EnableEvents = True
End Sub
Because it is worksheet code, it is very easy to install and use:
1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window
If you save the workbook, the macro will be saved with it.
To remove the macro:
1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
To learn more about Event Macros (worksheet code), see:
http://www.mvps.org/dmcritchie/excel/event.htm
H
HelpExcel
I added the macro but I think we need to modify it. I entered in $15.20 and
it took it to $15.00. Can we round all numbers up?
it took it to $15.00. Can we round all numbers up?
G
Gary''s Student
Change the one line to:
t.Value = Application.WorksheetFunction.RoundUp(t.Value, 0)
t.Value = Application.WorksheetFunction.RoundUp(t.Value, 0)
H
HelpExcel
I removed the second column for the actual cost and now it is not working at
all. I removed the macro and then added it it again and it is still not
working. Help?
all. I removed the macro and then added it it again and it is still not
working. Help?
H
HelpExcel
Ok - I got it to work but then it runs and debugger comes up and the macro no
longer works.
longer works.