Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel formula (2003 Service pak2)

    Good Morning All, I am in need of a formula that would round the answer in Line 7, columns B,C,D & E to always end in 9. Examples: B7 shows 0.66. I need it to show 0.69, C7 shows 2.38. I need to always round up to 2.39. Thanks for any assistance.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel formula (2003 Service pak2)

    What do you want 0.00 to be? 0.00 or 0.09?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula (2003 Service pak2)

    Hi Rory, thanks for the response. 0.00 would be 0.00

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel formula (2003 Service pak2)

    Try the attached and see if it works for you.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula (2003 Service pak2)

    Hi Rory
    Works perfectly. I attempted to use roundup, with no sucess. I do not understand how your formula works, but thanks it works great!

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel formula (2003 Service pak2)

    Because you are using 2 decimal places, if you round up to 1 decimal place and then subtract 0.01 you will always get 9 at the end. The other bit of the formula makes sure this only happens if the total is not 0.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel formula (2003 Service pak2)

    Thanks again Rory for your help and the explanation. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel formula (2003 Service pak2)

    Glad to oblige!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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