rounding numbers to the nearest dollar amount
 

I need to round currency to the nearest dollar amount

rounding numbers to the nearest dollar amount
 

=ROUND(A1,0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

I need to round currency to the nearest dollar amount

rounding numbers to the nearest dollar amount
 

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?


"Gary''s Student" wrote:

=ROUND(A1,0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

I need to round currency to the nearest dollar amount

rounding numbers to the nearest dollar amount
 

That is not our problem. What cell are you try to round and what is its
contents??
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?


"Gary''s Student" wrote:

=ROUND(A1,0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

I need to round currency to the nearest dollar amount

rounding numbers to the nearest dollar amount
 

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.

"Gary''s Student" wrote:

That is not our problem. What cell are you try to round and what is its
contents??
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?


"Gary''s Student" wrote:

=ROUND(A1,0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

I need to round currency to the nearest dollar amount

rounding numbers to the nearest dollar amount
 

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



"HelpExcel" wrote:

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.

"Gary''s Student" wrote:

That is not our problem. What cell are you try to round and what is its
contents??
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?


"Gary''s Student" wrote:

=ROUND(A1,0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

I need to round currency to the nearest dollar amount

rounding numbers to the nearest dollar amount
 

that worked but is it possible to have it round in the same column of A2?

"Francis" wrote:

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



"HelpExcel" wrote:

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.

"Gary''s Student" wrote:

That is not our problem. What cell are you try to round and what is its
contents??
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?


"Gary''s Student" wrote:

=ROUND(A1,0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

I need to round currency to the nearest dollar amount

rounding numbers to the nearest dollar amount
 

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


--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

that worked but is it possible to have it round in the same column of A2?

"Francis" wrote:

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



"HelpExcel" wrote:

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.

"Gary''s Student" wrote:

That is not our problem. What cell are you try to round and what is its
contents??
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?


"Gary''s Student" wrote:

=ROUND(A1,0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

I need to round currency to the nearest dollar amount

rounding numbers to the nearest dollar amount
 

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?

"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

that worked but is it possible to have it round in the same column of A2?

"Francis" wrote:

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



"HelpExcel" wrote:

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.

"Gary''s Student" wrote:

That is not our problem. What cell are you try to round and what is its
contents??
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?


"Gary''s Student" wrote:

=ROUND(A1,0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

I need to round currency to the nearest dollar amount

rounding numbers to the nearest dollar amount
 

Change the one line to:

t.Value = Application.WorksheetFunction.RoundUp(t.Value, 0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?

"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

that worked but is it possible to have it round in the same column of A2?

"Francis" wrote:

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



"HelpExcel" wrote:

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.

"Gary''s Student" wrote:

That is not our problem. What cell are you try to round and what is its
contents??
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?


"Gary''s Student" wrote:

=ROUND(A1,0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

I need to round currency to the nearest dollar amount

rounding numbers to the nearest dollar amount
 

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?

"Gary''s Student" wrote:

Change the one line to:

t.Value = Application.WorksheetFunction.RoundUp(t.Value, 0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?

"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

that worked but is it possible to have it round in the same column of A2?

"Francis" wrote:

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



"HelpExcel" wrote:

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.

"Gary''s Student" wrote:

That is not our problem. What cell are you try to round and what is its
contents??
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?


"Gary''s Student" wrote:

=ROUND(A1,0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

I need to round currency to the nearest dollar amount

rounding numbers to the nearest dollar amount
 

Ok - I got it to work but then it runs and debugger comes up and the macro no
longer works.

"HelpExcel" wrote:

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?

"Gary''s Student" wrote:

Change the one line to:

t.Value = Application.WorksheetFunction.RoundUp(t.Value, 0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?

"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

that worked but is it possible to have it round in the same column of A2?

"Francis" wrote:

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



"HelpExcel" wrote:

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.

"Gary''s Student" wrote:

That is not our problem. What cell are you try to round and what is its
contents??
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

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?


"Gary''s Student" wrote:

=ROUND(A1,0)
--
Gary''s Student - gsnu200843


"HelpExcel" wrote:

I need to round currency to the nearest dollar amount