Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 29th 06, 10:58 PM posted to microsoft.public.excel.worksheet.functions
dantee
 
Posts: n/a
Default adding digits of a number

Hello.

Does anyone knows a function or formula that would add the digits of a
number and spit out a result?

Example.... take the number 1092836103274 (all by itself in a cell)

add the 1st, 3rd, 5th digits and so on to get 1+9+8+6+0+2+4=30

or add the 2nd, 4th, 6th digits and get the result of 0+2+3+1+3+7=16

curious
dantee.

  #2   Report Post  
Old March 29th 06, 11:06 PM posted to microsoft.public.excel.worksheet.functions
tony h
 
Posts: n/a
Default adding digits of a number


=MID(A6,1,1)+MID(A6,2,1) and such like

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=527838

  #3   Report Post  
Old March 29th 06, 11:08 PM posted to microsoft.public.excel.worksheet.functions
David Billigmeier
 
Posts: n/a
Default adding digits of a number

Assume cell A1, need to enter these formulas with CTRL+SHIFT+ENTER as they
are array formulas:

To add the odd numbers:
=SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 1,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))

To add the even numbers:
=SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 0,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))
--
Regards,
Dave


"dantee" wrote:

Hello.

Does anyone knows a function or formula that would add the digits of a
number and spit out a result?

Example.... take the number 1092836103274 (all by itself in a cell)

add the 1st, 3rd, 5th digits and so on to get 1+9+8+6+0+2+4=30

or add the 2nd, 4th, 6th digits and get the result of 0+2+3+1+3+7=16

curious
dantee.

  #4   Report Post  
Old March 29th 06, 11:11 PM posted to microsoft.public.excel.worksheet.functions
Lotus123
 
Posts: n/a
Default adding digits of a number


Several ways to approach this...depending on how complex the data series
is. I'll assume the data series is uniform for this example...aka,
every number you want to use this one will have 6 digits. Number is in
cell A1..formula in A2.

=VALUE(MID(A1,1,1))+VALUE(MID(A1,2,1))+VALUE(MID(A 1,3,1))+VALUE(MID(A1,4,1))+VALUE(MID(A1,5,1))+VALU E(MID(A1,6,1))

This formula could be expanded for a larger number by adding to the
end. If you attempt to use this formula on a number smaller than 6
digits you will get an error...which gets into uniformity. If your
data is different, you can also add a check on each MID to see the
value is there, ISERROR()...if it is an error, don't add it.


--
Lotus123
------------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611
View this thread: http://www.excelforum.com/showthread...hreadid=527838

  #5   Report Post  
Old March 29th 06, 11:18 PM posted to microsoft.public.excel.worksheet.functions
Lotus123
 
Posts: n/a
Default adding digits of a number


wish I knew arrays better

David Billigmeier Wrote:
Assume cell A1, need to enter these formulas with CTRL+SHIFT+ENTER as
they
are array formulas:

To add the odd numbers:
=SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 1,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))

To add the even numbers:
=SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 0,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))
--
Regards,
Dave



--
Lotus123
------------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611
View this thread: http://www.excelforum.com/showthread...hreadid=527838



  #6   Report Post  
Old March 29th 06, 11:23 PM posted to microsoft.public.excel.worksheet.functions
David Billigmeier
 
Posts: n/a
Default adding digits of a number

Slight update, you don't have to enter these with CTRL+SHIFT+ENTER. Also
note my formulas don't depend on the length of your number, you can have as
long or as short a number as you would like:

Even positions:
=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)=0),--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

Odd positions:
=SUMPRODUCT(--(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)=1),--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
--
Regards,
Dave


"David Billigmeier" wrote:

Assume cell A1, need to enter these formulas with CTRL+SHIFT+ENTER as they
are array formulas:

To add the odd numbers:
=SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 1,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))

To add the even numbers:
=SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 0,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))
--
Regards,
Dave


"dantee" wrote:

Hello.

Does anyone knows a function or formula that would add the digits of a
number and spit out a result?

Example.... take the number 1092836103274 (all by itself in a cell)

add the 1st, 3rd, 5th digits and so on to get 1+9+8+6+0+2+4=30

or add the 2nd, 4th, 6th digits and get the result of 0+2+3+1+3+7=16

curious
dantee.

  #7   Report Post  
Old March 30th 06, 12:30 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default adding digits of a number

=SUMPRODUCT(--(MID(A11,{1,3,5,7,9,11,13},1)))

Just change {1,2,3,5,7,9,11} to each digit that you want to count

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"dantee" wrote in message
...
Hello.

Does anyone knows a function or formula that would add the digits of a
number and spit out a result?

Example.... take the number 1092836103274 (all by itself in a cell)

add the 1st, 3rd, 5th digits and so on to get 1+9+8+6+0+2+4=30

or add the 2nd, 4th, 6th digits and get the result of 0+2+3+1+3+7=16

curious
dantee.



  #8   Report Post  
Old March 30th 06, 07:11 AM posted to microsoft.public.excel.worksheet.functions
dantee
 
Posts: n/a
Default adding digits of a number

Wow. This is better than searching through books or maybe even the MS Excel
help. Thanks guys.

Earlier today "dantee" wrote:

Hello.

Does anyone knows a function or formula that would add the digits of a
number and spit out a result?

Example.... take the number 1092836103274 (all by itself in a cell)

add the 1st, 3rd, 5th digits and so on to get 1+9+8+6+0+2+4=30

or add the 2nd, 4th, 6th digits and get the result of 0+2+3+1+3+7=16

curious
dantee.

  #9   Report Post  
Old March 30th 06, 11:05 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default adding digits of a number


"dantee" wrote in message
...
Wow. This is better than searching through books or maybe even the MS

Excel
help.


maybe ?? definitely <vbg




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding digits in a single cell stevo Excel Worksheet Functions 2 February 14th 06 12:58 PM
calculate using last four digits of number in cell Andy Falkner Excel Worksheet Functions 3 February 10th 06 06:30 PM
Adding a 0 to a four digit number jermsalerms Excel Discussion (Misc queries) 5 January 13th 06 11:50 PM
How to set a column to accept only a specified number of digits ashvik Excel Worksheet Functions 1 September 26th 05 11:11 AM
Least number of digits in Y-axis labels Charley Kyd Charts and Charting in Excel 9 February 6th 05 04:03 PM


All times are GMT +1. The time now is 09:27 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017