summing first 3 values or 6 values of a decimal number

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
881
Office Version
  1. 2013
  2. 2007
I am looking for a formula that would sum the first 3 or the first 6 numbers of the decimal part of a number. Assume decimal number is in cell A1.

Thanks.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi;

For summing the first 3 digits:

Code:
=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:[COLOR=#ff0000][B]3[/B][/COLOR]")),1))

If you want to sum the first 6 digits, replace 3 by 6
 
Last edited:
Upvote 0
one example, with array formula (add with ctrl+shift+enter), your number is in A1, and in a2 you have the number of decimals you want to add up

=SUM(VALUE(MID(MID(A1-TRUNC(A1),3,A2),ROW(INDIRECT("1:"&A2)),1)))
 
Upvote 0
This one seems to take the first X digits of the number itself, not just the decimal part

Hi;

For summing the first 3 digits:

Code:
=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:[COLOR=#ff0000][B]3[/B][/COLOR]")),1))

If you want to sum the first 6 digits, replace 3 by 6
 
Upvote 0
If you don't like those above, you could use this formula ... it does NOT need to be entered as an array formula ... ie: you do not need to press ctrl shift enter to make it work ...

=SUMPRODUCT(MID(RIGHT(A1,LEN(A1)-FIND(".",A1)),ROW(OFFSET($A$1,,,3)),1)+0)

If you want the first 6 decimals added, replace the 3 with a 6

Kind regards,

Chris
 
Upvote 0
I'm trying to correct the formula according to your version of Excel, so i dont know if its working or not ...

Code:
=SUMPRODUCT(--MID(SUBSTITUTE(A1,INT(A1) & ".",""),ROW(INDIRECT("1:[B][COLOR=#ff0000]3[/COLOR][/B]")),1))
 
Last edited:
Upvote 0
If you don't mind separate (normally entered) formulas for each of your requests...

First 3 decimal digits:
-------------------------------
=SUM(MID(1000*MOD(A1,1),{1,2,3},1)+0)


First 6 decimal digits
-------------------------------
=SUM(MID(1000000*MOD(A1,1),{1,2,3,4,5,6},1)+0)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top