decimal value derived through formula acting as text, how to make it as value

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.

    EXcel 2007 and above

    I have random value say, 3254.70 in cell A9



    I want to extract only the decimal part, so, in cell B9 (can try either of the formula, as both yield same result)
    EITHER
    =A9-TRUNC(A9)



    OR



    =MOD(A9,1)



    both would bring the decimal value 0.7 or 0.70 BUT when i am testing it IN Cell B10, with IF formula, like =IF(B9=0.7,7,0), it shows false results, and returns 0 (return value on non fulfilling conditions)



    I tried using VALUE function also which works with INT / TRUNC function (but not with TRUNC or MOD), but no desired result.


    What formula i should use to get only decimal value, and test it with IF function, to ensure certain other work.

    Re: decimal value derived through formula acting as text, how to make it as value


    Looks familiar ... ;)


    EITHER

    =ROUND(A9-TRUNC(A9),1)


    OR


    =ROUND(MOD(A9,1),1)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: decimal value derived through formula acting as text, how to make it as value


    AliGW, thanks that was perfect and familiar answer. You are my Santa this season, but No duplicate. Trust, appreciate.

    Re: decimal value derived through formula acting as text, how to make it as value


    Yes, it's a duplicate of the post on Excel Forum. Cross-posting is frowned upon IF you do NOT provide a link. ;)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: decimal value derived through formula acting as text, how to make it as value


    yes, i dont know why it happens, and i am apology for that. When i posted there [ http://www.excelforum.com/showthread.php?t=1167361 ] , and since then, forum (website) does not load on my machine, so, i requested here. I tried opening in separate TAB, and search if draft was posted or not, and i could not see the thread, so presumed request was not made, After couple of hours, Excelforum, still not loading on my machine, and i was badly looking for the solution so with no bad intention it was posted here. Offence is offence, and i apologise for the same. Earlier also, this happened and it was here only. NExt time, i will wait for more time. I sincerely apologise, again.

    Re: decimal value derived through formula acting as text, how to make it as value


    Quote

    when i am testing it IN Cell B10, with IF formula, like =IF(B9=0.7,7,0), it shows false results, and returns 0 (return value on non fulfilling conditions)


    I tried using VALUE function also which works with INT / TRUNC function (but not with TRUNC or MOD), but no desired result.


    With the INT function you referenced:
    =IF(ROUND(A9-INT(A9),1)=0.7,7,0)

Participate now!

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

Register Yourself Login