Different rounding formula

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi,


    How can I do this:


    In cell A1 is 5.00 in B1 will be 5.00


    5.00 remain 5.00
    5.19 becomes 5.5 (everything after the point and range between 5.01 and 5.49)
    5.50 remain 5.50
    5.83 becomes 4.00 (everything after the point and ranges between 5.51 and 5.99)


    I made a long formula with IF, MOD and INT. Can somebody made formula for that?

    Re: Different rounding formula


    Perhaps you mean?


    =IF(A1<5,"",IF(A1=5,5,IF(A1<=5.5,5.5,IF(A1<=5.99,4,""))))


    or


    =LOOKUP(A1,{0,5,5.01,5.51,6},{"",5,5.5,4,""})

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Different rounding formula


    Hi,


    Thank you for replay.


    In cell A1 could be any number and I need a formula to return in B1 that "special" number :
    If number in A1 is 125.17 (everything between 125.01 and 125.49) in B1 will be 125.5
    If number in A1 is 125.50 in B1 will be 125.50 remain same number
    If number in A1 is 125.69 (everything between 125.51 and 125.99) in B1 will be 126
    If number in A1 is 125 Or 125.00 in B1 will be 125 remain same number


    Hope now is clear.

    Re: Different rounding formula


    So you are only interested in changing any number that ends with a 5 before the decimal?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Different rounding formula


    No,


    I need a formula to to like this:
    If a number (in cell A1) has no decimal in B1 to put that number (Eg A1=199 => B1=199)
    If decimal are between 01 and 49 in B1 to put that number and decimal must be 5 (Eg A1=3.23 => B1=3.5)
    If number in A1 is say 2311.5 in B1 it will be same number (2311.5)
    If decimal are between 51 and 99 in B1 it will be number from A1 increased with 1 (Eg A1=338.62 => B1= 339)

    Re: Different rounding formula


    Try:


    =CEILING(A1,0.5)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login