summing first 3 values or 6 values of a decimal number

[michaelsmith559]

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.

 

[Haluk]

Hi;

For summing the first 3 digits:

=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

 

[istiasztalos]

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

 

[istiasztalos]

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:

=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

 

[palaeontology]

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

 

[Haluk]

This one seems to take the first X digits of the number itself, not just the decimal part

Uh .... you are correct, its my bad.

 

[Haluk]

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

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

 

[Rick Rothstein]

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)

 

[michaelsmith559]

Thanks for all the reply's. I went with Rick Rothstein's.