Change values to the right of a decimal point

  • Hi All.


    I have a value in cell A1 which is for example 140.25


    This value represents a weight in lbs and oz


    However, however, to make it easier for the user, I would like to see 140.4
    This being a conversion of .25 of a lb to 4 ounces


    The user would easier understand 140lb 4oz


    Is this possible?


    Thank you

  • Re: Change values to the right of a decimal point


    You could do something like this:


    =INT(A2)&"lb "&INT(MOD(A2,1)*16)&"oz"

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Change values to the right of a decimal point


    Hi Luke


    Appreciate the response.


    Your suggestion works fine to the point of converting a known weight and a decimal oz, however this falls down if a user enters 140.13, where the .13 equates to 13oz rather than a decimal round of 2oz


    I guess I need to decide how the input is to be entered and work from there first.


    Is there a suggestion similar to your previous, where the user enters 140.13 as a true lb/oz weight where the output would be 140lb 13oz


    I can then decide which way to proceeed?


    Thank you

  • Re: Change values to the right of a decimal point


    You'd need a second piece of info, as either way you need someone of knowing if the input was just lbs, or lbs + oz. Let's say there was some indication in B2, you could do:


    =INT(A2)&"lb "&INT(MOD(A2,1)*IF(A2="Mixed", 100, 16))&"oz"


    So, if user inputs a mixed value (lbs + oz), then we change 140.15 into 140lbs 15oz. If user selects "single" (or whatever else you pick), formula outputs 140lbs 2oz (if you want 2.4 oz, remove the INT function on latter part)


    Edit: For clarity, I'd probably setup your data input to have two cells clearly marked, one for "Pounds" and the other for "Ounces", and then user can choose one or the other, or both. Then your unit conversions will be a lot easier.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Change values to the right of a decimal point


    Nice work and Im almost there


    I have used some of your thoughts and a few of my own and trying different methods to acheive the correct outcome.


    One last thing, is there a way to add a 2 cells which are round numbers and add the second cell after a decimal point?


    I.E.
    A1 = 100
    A2 = 15


    A3 = 100.15


    Limitations...
    The number in A2 cannot be entered as .15 for this is derived from another calculation
    The number cannot be concatenated as this value is used in a chart and offset functions


    Thank you

  • Re: Change values to the right of a decimal point


    Hello,


    What about =A1+A2/100 ... :wink:


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Change values to the right of a decimal point


    Hi Thanks for the reply.


    Not really, the number in A2 needs to be a shown as per A2
    If the number is 6 then your suggestion makes this .06 which is incorrect for my requirement


    Appreciate the help

  • Re: Change values to the right of a decimal point


    OK ...then


    To be tested in cell A3


    =A1+(A2/(1&REPT(0,LEN(A2))))

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Change values to the right of a decimal point


    Quote from jl2509;791326

    Awesome


    Thats works great


    Much appreciated


    Glad you could fix your problem ...:wink:


    Thanks for ... your thanks ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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