| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a number's digits
I need to insert check figures on my spreadsheet that equal the sum of a
number's digits. If cell A1 = 12,345.67, is there a formula that in cell A2 would add the digits 1+2+3+4+5+6+7 to equal 28? Thanks for your help. Claudia |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a number's digits
Claudia,
the following *array* formula (needs to be committed with Shift+Ctrl+Enter) will calculate what you ask: =SUM(IF(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1))),VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1)))) HTH Kostis Vezerides |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a number's digits
Hi Claudia,
I got this formula from these groups a while ago. I can't remember who posted it but it has a Bob Phillips look to it. =SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) It will handle the sum of digits in A1 but it doesn't allow for the comma separator and decimal point in your example. With a bit of data manipulation you should be able to make it work. HTH Martin |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a number's digits
=SUMPRODUCT(--(MID(SUBSTITUTE(A1,".",""),ROW(INDIRECT("1:"&LEN(S UBSTITUTE(A1
,".","")))),1))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Claudia" wrote in message ... I need to insert check figures on my spreadsheet that equal the sum of a number's digits. If cell A1 = 12,345.67, is there a formula that in cell A2 would add the digits 1+2+3+4+5+6+7 to equal 28? Thanks for your help. Claudia |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a number's digits
Yep!
Thought it was you Bob. And it now has another bit of concise Phillips genius added to it. Regards Martin |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a number's digits
It works great. Thank you!
"Bob Phillips" wrote: =SUMPRODUCT(--(MID(SUBSTITUTE(A1,".",""),ROW(INDIRECT("1:"&LEN(S UBSTITUTE(A1 ,".","")))),1))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Claudia" wrote in message ... I need to insert check figures on my spreadsheet that equal the sum of a number's digits. If cell A1 = 12,345.67, is there a formula that in cell A2 would add the digits 1+2+3+4+5+6+7 to equal 28? Thanks for your help. Claudia |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a number's digits
Bob,
can always count on you for a new idea... Regards, Kostis Bob Phillips wrote: =SUMPRODUCT(--(MID(SUBSTITUTE(A1,".",""),ROW(INDIRECT("1:"&LEN(S UBSTITUTE(A1 ,".","")))),1))) -- HTH Bob Phillips |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of a number's digits
Hi Kostis,
Didn't notice you in there. Got the details, digesting it now thanks. Unfortunately time is marching on. Regards Bob "vezerid" wrote in message ups.com... Bob, can always count on you for a new idea... Regards, Kostis Bob Phillips wrote: =SUMPRODUCT(--(MID(SUBSTITUTE(A1,".",""),ROW(INDIRECT("1:"&LEN(S UBSTITUTE(A1 ,".","")))),1))) -- HTH Bob Phillips |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| in excel, how to limit digits of negative hexadecimal numbers? | Excel Discussion (Misc queries) | |||
| counting digits, not numbers | Excel Worksheet Functions | |||
| Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
| Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
| Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Linear Mode
