Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Rounding Currency Amounts

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)


    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •