This may sound weird to some but those that know numerology will easily understand it.
I wanted to know if there is the possibility to create a formula that will sum the numbers in a single cell. i.e If i have a cell with 27 it should return 9 summing 2+7 or if i had a 51 it would return a 6
Also if possible should i have a 2 it should return a 2 and should i have a 123 it should return a 6
Did my english pass the message?
The following array formula (confirm with Ctrl+Shift+Enter) will add up to 5 digit. Change the "00000" to the maximum number of digits you want to add as well as the 5 in RIGHT(..., 5)
Please Login or Register to view this content.
Returns an error in the formulaThanks for the quick reply though
Can you post an example of your sheet. The formula will work as you asked.
This one will add the digits of a number of any length. It is an Array formula as well.
Please Login or Register to view this content.
Last edited by mdbct; 03-24-2010 at 11:43 PM.
With 12345 in cell A1, the formula gives m: 15. Did you remember to adjust the A1 to reflect the actual cell you are using? Did you "Ctrl+Shift+Enter", not just enter after typing the formula?
Ben Van Johnson
=MOD(A1 - 1, 9) + 1
Or, for large numbers, to overcome an Excel bug,
=A1 - FLOOR(A1 - 1, 9)
Entia non sunt multiplicanda sine necessitate
Yes and yes... still I get an error in formulaOriginally Posted by protonLeah
The new formula gives me an error as well. What am I doing wrong
Both return a 'You've enetered too few arguments for this function' error
I got something working with:
It does sum both numbers of a single cell, however when the mentioned cell has a single number (like 2) it dobles it and it should return it as it is (adding 0+2 and not 2+2)Please Login or Register to view this content.
Any thoughts?
Did you change the commas to semi colons in the formula you were provided? shg's formulas will return the sum of the sum of the numbers until it is a one digit number. For example, 99 will evaluate to 9 which would be like 9+9=18 1+8 = 9
Try
don't forget that it's an array formula and that you need to use [ctrl] + [shift] + [enter] to confirmPlease Login or Register to view this content.
Put 123 in A1 and =A1 - FLOOR(A1 - 1, 9) in B1. The number of arfuments is fine.
shg,
The OP is using a non US version of Excel so your formula is evaluating to (changing regional settings to US):
=123 - FLOOR(123-1.9) or =123 - FLOOR(121.1). Because his decimal separator is a comma, not a period the FLOOR function has only one argument..
mdbct, thank you, I had not considered that at all.
=A1 - FLOOR(A1 - 1; 9)
Awesome...it worked
cool man i wanted exactly the same thing
i want to apply the same formula to all the numbers of a column. how is that done?!
skb_rt,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
=mod(a1-1,9)+1 works with excel 2010 starter but not with excel 2007, shows #num! In excel 2007
=SUM(VALUE(MID(A1,ROW($A$1:OFFSET($A$1,LEN(A1)-1,0)),1)))
This works perfectly for numbers in the cell. We can copy paste and get the result. Thanks.
Last edited by Chandrasekhar Dash; 10-23-2013 at 11:36 PM.
array formula
=SUM(IFERROR(--MID(A1,ROW(1:100),1),""))
Interesting, it's working nicely
Thanks & Regards
Last edited by jd16; 11-09-2013 at 06:14 AM.
Thanks
Regards
Last edited by jd16; 11-09-2013 at 06:13 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks