Decimals

%3CLINGO-SUB%20id%3D%22lingo-sub-2134351%22%20slang%3D%22en-US%22%3EDecimals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2134351%22%20slang%3D%22en-US%22%3E%3CP%3EOk%2C%20all%20I%20need%20is%20a%20formula%20on%20how%20to%20calculate%20ONLY%20decimals%20in%20a%20number.%20For%20example%2C%201%3CSTRONG%3E.45%3C%2FSTRONG%3E%20%2B%203%3CSTRONG%3E.34%3C%2FSTRONG%3E.%20I%20need%20to%20know%20what%20%3CSTRONG%3E.45%3C%2FSTRONG%3E%20%2B%20%3CSTRONG%3E.34%3C%2FSTRONG%3E%20in%20that%20equation%20adds%20up%20too.%20I%20don't%20want%20the%20whole%20number%20on%20the%20left%20side%20of%20the%20decimal%20point.%20%3CSTRONG%3EI%20just%20need%20the%20%3CEM%3E%3CU%3Esum%3C%2FU%3E%3C%2FEM%3E%20of%20%3CU%3E%3CEM%3Eall%3C%2FEM%3E%3C%2FU%3E%20of%20the%20%3CU%3E%3CEM%3Enumbers%3C%2FEM%3E%3C%2FU%3E%20on%20the%20%3CU%3E%3CEM%3Eright%20side%3C%2FEM%3E%3C%2FU%3E%20of%20the%20%3CEM%3E%3CU%3Edecimal%20point%3C%2FU%3E%3C%2FEM%3E.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESo%2C%20to%20clarify%20what%20I'm%20needing%2C%201.45%20%2B%203.34%20should%20equal%200.79.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20searching%20for%20hours%20to%20find%20any%20help%20and%20I%20can't%20find%20anything%20to%20find%20the%20solution%2C%20so%20if%20anyone%20knows%20how%20to%20do%20this%20and%20could%20give%20me%20a%20hand%20that%20would%20be%20much%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2134351%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2134516%22%20slang%3D%22en-US%22%3ERe%3A%20Decimals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2134516%22%20slang%3D%22en-US%22%3E%3CP%3EOk%2C%20so%20far%20no%20good.%20I%20appreciate%20the%20help%20though%20but%20it's%20always%20off%20by%20like%20.1%20or%20something%20close%20to%20that.%3C%2FP%3E%3CP%3EI%20can%20send%20you%20a%20screenshot%20of%20what%20I'm%20working%20with%20so%20that%20way%20you%20have%20a%20better%20picture%20of%20what%20I'm%20asking.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F966497%22%20target%3D%22_blank%22%3E%40bierman96%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2134532%22%20slang%3D%22en-US%22%3ERe%3A%20Decimals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2134532%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F966497%22%20target%3D%22_blank%22%3E%40bierman96%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bierman96_1-1613346171302.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F254886iA32245B645E5EBA2%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bierman96_1-1613346171302.png%22%20alt%3D%22bierman96_1-1613346171302.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESo%20this%20here%20is%20what%20I'm%20working%20on.%20I%20want%20the%20Total%20Used%20to%20be%20able%20to%20change%20daily%20and%20not%20at%20the%20end%20of%20the%20week%20like%20I%20currently%20have%20it.%20The%20numbers%20that%20are%20to%20the%20right%20of%20it%20is%20how%20much%20I've%20used%20of%20said%20material%20at%20work%2C%20I%20just%20minused%20Monday's%20count%20with%20(If%20Worked%20Saturday).%20As%20I've%20mentioned%20before%20though%2C%20I%20want%20that%20number%20to%20change%20daily%20since%20those%20numbers%20will%20change%20everyday.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20this%20makes%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2134936%22%20slang%3D%22en-US%22%3ERe%3A%20Decimals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2134936%22%20slang%3D%22en-US%22%3EI'm%20afraid%20I%20don't%20follow.%20I%20can%20see%20the%20total%20used%20is%20Monday%20-%20If%20Worked%20Saturday%2C%20and%20I%20think%20you%20want%20the%20total%20to%20be%20Monday%20minus%20whatever%20the%20last%20value%20is%20in%20the%20column%20(from%20Tuesday%20through%20%22if%20worked%20Saturday%22).%3CBR%20%2F%3E%3CBR%20%2F%3EBut%2C%20I've%20no%20idea%20where%20you%20are%20'just%20adding%20the%20decimals'.%20Are%20you%20sure%20the%20actual%20cell%20values%20are%20to%20two%20decimals%3F%20If%20you%20change%20the%20format%20to%20increase%20the%20number%20of%20decimals%2C%20is%20the%20value%20actually%20to%20a%20higher%20precision%20than%20displayed%3F%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20the%20workbook%20does%20not%20contain%20sensitive%20data%20or%20if%20the%20sensitive%20information%20can%20be%20deleted%2C%20can%20you%20upload%20it%20(hit%20'reply'%20instead%20of%20'quick%20reply'%20and%20you%20should%20see%20an%20upload%20link)%3F%3C%2FLINGO-BODY%3E
New Contributor

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!

9 Replies
Try: =SUMPRODUCT(MOD(A1:A2,1))

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 

bierman96_1-1613346171302.png

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.

 

I'm afraid I don't follow. I can see the total used is Monday - If Worked Saturday, and I think you want the total to be Monday minus whatever the last value is in the column (from Tuesday through "if worked Saturday").

But, I've no idea where you are 'just adding the decimals'. Are you sure the actual cell values are to two decimals? If you change the format to increase the number of decimals, is the value actually to a higher precision than displayed?

If the workbook does not contain sensitive data or if the sensitive information can be deleted, can you upload it (hit 'reply' instead of 'quick reply' and you should see an upload link)?

@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 

 

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.

 

@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).

Screenshot 2021-02-15 152225.pngScreenshot 2021-02-15 152328.png

@bierman96 

 

Here is an example file with the matrix formula

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

@bierman96 

For such model

image.png

total could be

=(SUM(B$3:INDEX($B$3:$B$8,WEEKDAY($A$1,2)))-B$9)*(WEEKDAY($A$1,2)<>7)