Rounding Currency Amounts

[ACG]

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!

[Juan Pablo González]

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)

[ACG]

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

[Juan Pablo González]

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)