+ Reply to Thread
Results 1 to 6 of 6

Sum digits in a cell plus the number digits-Ready for this

  1. #1
    Registered User
    Join Date
    04-14-2008
    Posts
    6

    Sum digits in a cell plus the number digits-Ready for this

    It's not complicated it just looks that way.
    I am adding up each individual digit in a cell and then adding the number of digits to the sum of the individual digits. Read it twice if I lost you.
    Step 1 in K6 - K12 put the following:
    11,111,111.11 then
    1,111,111.11 then
    111,111.11 then
    11,111.11
    1,111.11
    111.11
    11.11
    Here is the formula I have so far to handle through row K10:
    =IF(LEN(TEXT(--SUBSTITUTE(ROUND(K6,2)&"",".",""),"0#"))=10,SUM(LEFT(K6,1)+MID(K6,2,1)+MID(K6,3,1)+MID(K6,4,1)+MID(K6,5,1)+MID(K6,6,1)+MID(K6,7,1)+MID(K6,8,1)+MID(K6,10,1))+MID(K6,11,1),IF(LEN(TEXT(--SUBSTITUTE(ROUND(K6,2)&"",".",""),"0#"))=9,SUM(LEFT(K6,1)+MID(K6,2,1)+MID(K6,3,1)+MID(K6,4,1)+MID(K6,5,1)+MID(K6,6,1)+MID(K6,7,1)+MID(K6,9,1)+MID(K6,10,1)),IF(LEN(TEXT(--SUBSTITUTE(ROUND(K6,2)&"",".",""),"0#"))=8,SUM(LEFT(K6,1)+MID(K6,2,1)+MID(K6,3,1)+MID(K6,4,1)+MID(K6,5,1)+MID(K6,6,1)+MID(K6,8,1)+MID(K6,9,1)),IF(LEN(TEXT(--SUBSTITUTE(ROUND(K6,2)&"",".",""),"0#"))=7,SUM(LEFT(K6,1)+MID(K6,2,1)+MID(K6,3,1)+MID(K6,4,1)+MID(K6,5,1)+MID(K6,7,1)+MID(K6,8,1)),IF(LEN(TEXT(--SUBSTITUTE(ROUND(K6,2)&"",".",""),"0#"))=6,SUM(LEFT(K6,1)+MID(K6,2,1)+MID(K6,3,1)+MID(K6,4,1)+MID(K6,6,1)+MID(K6,7,1)),"'TRY AGAIN")))))+LEN(TEXT(--SUBSTITUTE(ROUND(K6,2)&"",".",""),"0#"))
    The problem is once I get to the formula for K10 it no longer works?
    Any ideas why it won't work for K10, K11, and K12

  2. #2
    Registered User
    Join Date
    04-14-2008
    Posts
    6
    Sorry I forgot the resulting answers should be 20, 18, 16, 14, 12, 10, 8

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Sum digits in a cell plus the number digits-Ready for this

    Try this:
    Please Login or Register  to view this content.
    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    I'd like to see a more representative sample of data, presumably they won't always be 1s. Will they always have at most 2 decimal places?

    For your example perhaps you could use this version, quite similar to Ron's suggestion

    =LEN(A1*100)+SUMPRODUCT(MID(A1*100,ROW(INDIRECT("1:"&LEN(A1*100))),1)+0)

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Sum digits in a cell plus the number digits-Ready for this

    Seeing daddylonglegs' post reminded me that
    multiplying by 100 does, indeed, move the decimal point (duh!)
    So the new formula is:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-14-2008
    Posts
    6
    Wow...Both examples are so much simpler than mine. Both formulas work, The sample of data even though it is just one's will always provide the same result. It could be any number really, the point being that it sums the digits and then adds the number (len) of digits to the sum. I have been working on this for a while and finally brought it to this forum. I think some solutions are best tried and failed several times before putting it out to this forum.
    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1