Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Formula for rounding in excel nearest 5

  • 31-08-2010 9:34pm
    #1
    rilly99
    Registered Users, Registered Users 2 Posts: 221 ✭✭


    :confused:I have calculated a list of prices euros & cents using various inputs and formulas etc

    I now want to round this column of numbers up the nearest 10 euros (eg 8763.90 to 8770.00)

    using excel 2007

    Any help appreciated


Comments

  • #2
    bhickey
    Registered Users, Registered Users 2 Posts: 1,340 ✭✭✭bhickey


    =ROUND(number,-1)+10


  • #3
    rockal
    Registered Users, Registered Users 2 Posts: 757 ✭✭✭rockal


    The above formula would round an amount of 8765.93 to 8780.00
    A better option is to use
    =ROUNDUP(number,-1)


  • #4
    Dulchie
    Registered Users, Registered Users 2 Posts: 209 ✭✭Dulchie


    Try =ceiling(number,step) i.e. =ceiling(8763.90,10.00)

    if you need to use for a series on values

    =ceiling(a1,10.00) and copy the formula down the column.

    sample

    data result
    999 1000
    1000 1000
    1001 1010
    1002 1010
    1003 1010
    1004 1010
    1005 1010
    1006 1010
    1007 1010
    1008 1010
    1009 1010
    1010 1010
    1011 1020
    1012 1020
    1013 1020


  • #5
    bhickey
    Registered Users, Registered Users 2 Posts: 1,340 ✭✭✭bhickey


    rockal wrote: »
    The above formula would round an amount of 8765.93 to 8780.00

    Doh! Sorry I meant '=ROUND(number,-1)'. Dunno where I got the '+10' from. I never knew there was a ROUNDUP function too. Looks like there a few ways to do the same thing.


  • #6
    rilly99
    Registered Users, Registered Users 2 Posts: 221 ✭✭rilly99


    Cheers eveyone for the very useful formulas


  • Advertisement
Advertisement