Forum Discussion

bierman96's avatar
bierman96
Copper Contributor
Feb 14, 2021

Decimals

Ok, all I need is a formula on how to calculate ONLY decimals in a number. For example, 1.45 + 3.34. I need to know what .45 + .34 in that equation adds up too. I don't want the whole number on the left side of the decimal point. I just need the sum of all of the numbers on the right side of the decimal point.

So, to clarify what I'm needing, 1.45 + 3.34 should equal 0.79.

 

I've been searching for hours to find any help and I can't find anything to find the solution, so if anyone knows how to do this and could give me a hand that would be much appreciated!

excel
Formulas and Functions

9 Replies

  • MGNaji's avatar
    MGNaji
    Copper Contributor
    Feb 15, 2021

    bierman96 

    Hi,

    use ROUNDDOWN(N2;0) function to get the integer part (left part) of the number then you can subtract from original number to get the decimal part (right part).

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Feb 15, 2021

    bierman96 

     

    Matrixformel in German:
    {=SUMME(A1:A3)-SUMME(GANZZAHL(A1:A3))}

     

    Matrixformel in English:
    {=SUM(A1:A3)-SUM(INT(A1:A3))}

     

    In any case, end the input with the key combination [Ctrl] + [Shift] + [Enter], because this is the only way Excel recognizes the matrix formula.

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 15, 2021

    bierman96 

    I can see no connection between your description in the original post and what you show on the image.  Neither do I like having to copy the numbers, please include data in a computer readable form.

    If you are using Excel 365, it will have dynamic arrays and you could use somewhat different formulae.  Assuming your data were named 'rawdata' then the array representing decimal parts to 2s.f. would be

    = MOD(100*rawdata, 100)

    To sum by rows or columns could involve the MMULT function and, if you choose, LET

    = LET(
  dpart, MOD(100*rawdata,100),
  MMULT({1,1,1,1,1,1,1},dpart) )

     

  • bierman96's avatar
    bierman96
    Copper Contributor
    Feb 14, 2021

    Ok, so far no good. I appreciate the help though but it's always off by like .1 or something close to that.

    I can send you a screenshot of what I'm working with so that way you have a better picture of what I'm asking.bierman96 

    • bierman96's avatar
      bierman96
      Copper Contributor
      Feb 14, 2021

      bierman96 

      So this here is what I'm working on. I want the Total Used to be able to change daily and not at the end of the week like I currently have it. The numbers that are to the right of it is how much I've used of said material at work, I just minused Monday's count with (If Worked Saturday). As I've mentioned before though, I want that number to change daily since those numbers will change everyday.

       

      Hopefully this makes sense.

       

Resources