Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    rounding cents in spreadsheet (Excel 2000)

    I have 1500 prices and i need a formula to round the cents to .47 or .97
    Example:
    8.84 would round to-8.97
    7.99 ...... 7.97
    11.99 ..... 11.97
    6.20 ..... 6.47
    2.99.... 2.97
    5.35 .... 5.47
    There will be a whole spread sheet of these. I work in a store and if i don't figure out a formula that will do this, I will be hand punching each one!
    I would dearly appreciate help.

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: rounding cents in spreadsheet (Excel 2000)

    Hi & welcome to the Lounge

    The exact method you are using to round is not clear, as 6.20 rounds to 6.47 you are not rounding to the nearest .47 or .97

    With the following formula cents from .00 to .49 are rounded to .47 and from .50 to .99 rounded to .97
    <code>=ROUNDUP(A1*2+0.01,0)/2-0.03</code>

    You can adjust the point where it rounds to .47 or .97 by changing the "<code>A1*2+0.01</code>" part of the formula. Instead of +0.01 add or subtract the amount necessary to achieve your desired result. +0.01 is probably the maximum you would want to add and you may need to subtract amounts to achieve the desired result.

    If you did intend to round to the nearest .47 or .97 then you can use the following formula instead, for it to work the Analysis ToolPak add-in must be installed

    <code>=MROUND(A1+0.03,0.5)-0.03</code>

  3. #3
    New Lounger
    Join Date
    Aug 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rounding cents in spreadsheet (Excel 2000)

    tony55
    Thank you sooooooo much. I just changed 2045 prices using the first formula you gave me. I don't really understand how or why it works, but it did and I thank you again!


    BFV

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: rounding cents in spreadsheet (Excel 2000)

    Hi BFV

    I am pleased it worked for you.

Posting Permissions

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