Rounding Currency Amounts

ACG

New Member
Joined
Feb 17, 2002
Messages
10
I currently have two sheets in my workbook. On my "detail" sheet I have a column for a percent of increase and another column that is for "total increase". The percent of increase is formatted as a percent and the total increase is formatted as a currency. My formula in the total increase is simply =sum(l3*s3). My amount in the "total increase" column does not permit decimals, therefore it rounds to the nearest dollar, which is what I want. I also have a summary sheet where I show the total amount from the "total increase" column, this amount is off by $3 and I believe it is due to rounding. When I add the total increase by hand in the detail sheet I get 112146; however, On the summary sheet I just have a fomula of =sum('sheet2'!t3:t51) and the total there is 112143.

Does anyone know of a way that I could fix this somehow so that my numbers correspond?

Thank you!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
First, change your first formula to:

=L3*S3

There's no need to use SUM there.

and, rounding it to the nearest dollar would make it

=ROUND(L3*S3,0)
 
Upvote 0
This does help but I also have another formula in another cell that is having the same problem, the formula there is:

=IF(U4=TRUE,IF((L4-N4)>0,L4-N4,0),T4)

So I don't know how to throw a "round" in there or in another cell that is also having the same problem:

=IF(U4=TRUE,T4-V4,0)

Sorry my Excel skills are not the best...

Thanks!
AC
 
Upvote 0
On 2002-02-20 05:52, ACG wrote:
This does help but I also have another formula in another cell that is having the same problem, the formula there is:

=IF(U4=TRUE,IF((L4-N4)>0,L4-N4,0),T4)

So I don't know how to throw a "round" in there or in another cell that is also having the same problem:

=IF(U4=TRUE,T4-V4,0)

Sorry my Excel skills are not the best...

Thanks!
AC

Change this:

=IF(U4=TRUE,IF((L4-N4)>0,L4-N4,0),T4)

to

=IF(U4,ROUND((L4-N4)>0)*(L4-N4),0),T4)

and

=IF(U4=TRUE,T4-V4,0)

to this

=ROUND(T4*(T4-V4),0)
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,460
Members
448,573
Latest member
BEDE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top