[Solved] Add numbers in the same cell

Discuss the spreadsheet application

[Solved] Add numbers in the same cell

Postby Kacem » Thu Aug 20, 2015 8:47 am

If there is formula to add numbers in the same cell. Example cell B1 258 formula that could bring me the total 15, because 2+5+8=13 thanks in advance for any help
Last edited by Hagar Delest on Sat Sep 05, 2015 11:52 am, edited 1 time in total.
Reason: tagged [Solved].
open office 4.1.1 windows 10
Kacem
 
Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

Re: add numbers in the same cell

Postby RusselB » Thu Aug 20, 2015 9:24 am

You'd have to guarantee that the numbers used in the addition are always single digits.
Evren then it's not simple
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5101
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: add numbers in the same cell

Postby RoryOF » Thu Aug 20, 2015 9:30 am

Also, what do you intend should happen? Taking your example, the extra number is 2. What happens to the 2? Is it to be inserted before/after the existing string of numbers (not possible) or is it to be placed in another cell? Will the limiting number (15) always be the same?
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 28981
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: add numbers in the same cell

Postby Kacem » Thu Aug 20, 2015 9:54 am

Thanks for the replies. It will be 3 numbers single digit, any numbers from 1....9. As in the example. I want to automat them because there are a long columns. another example 113=5 418=13. Thanks in any way.
open office 4.1.1 windows 10
Kacem
 
Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

Re: add numbers in the same cell

Postby Zizi64 » Thu Aug 20, 2015 11:06 am

When that 3 digit integer number is located in cell A1, put the next formula into the B1:

My first idea:
Code: Select all   Expand viewCollapse view
=LEFT(TEXT(A1;"000");1)+RIGHT(TEXT(A1;"000");1)+RIGHT(LEFT(TEXT(A1;"000");2);1)
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: add numbers in the same cell

Postby karolus » Thu Aug 20, 2015 1:30 pm

Hallo
Code: Select all   Expand viewCollapse view
=SUM(VALUE(MID(A1;ROW($A$1:$A$3);1)))

as Matrixformula
AOO4, Libreoffice - 5.1 … 5.3.2.2 on Linux Mint17
User avatar
karolus
Volunteer
 
Posts: 851
Joined: Sat Jul 02, 2011 9:47 am

Re: add numbers in the same cell

Postby Lupp » Thu Aug 20, 2015 1:35 pm

What you want to get is the "cross sum". This is a well defined function in ("lower") number theory used for statements concerning divisibility of numbers represented in a g-adic system, mostly the decimal system. As we well are able to decide the divisibilty for a three-digit-number directly by mental arithmetics, we might be interested to get the cross sum for numbers of arbitrary length.
Having a nonnegative integer of arbitrary length in A1 again this is achieved by
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(VALUE(MID(A1;ROW(OFFSET(A1;0;0;LEN(A1);1));1)))
independet of whether the number in A1 is actually a number or a text consisting of decimal digits and representing the number. If the number is, however, too large to be handled as an integer by the arithmetic engine of Calc/TheComputer, it must be text. For the TEXT 12345678987654321002468013579 e.g. we get the correct cross sum 126 while the "same" number entered without precautions to get it as text will produce an error 502.

You may call the construct I used here a virtual for-statement.

Editing
Just found the correct answer by karolus. Of course, he is also capable of breking the condition of "three digits exactly".
(Using SUMPRODUCT() allows for omitting the explicit marking the expression for matrix evaluation.)
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2489
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: add numbers in the same cell

Postby Kacem » Thu Aug 20, 2015 1:48 pm

thanks zizi64 for your answer didn't work. I don't have text only 3 numbers column and want the sum of the digit ex: 112 equal to 4; 321 equal to 6
open office 4.1.1 windows 10
Kacem
 
Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

Re: add numbers in the same cell

Postby Zizi64 » Thu Aug 20, 2015 1:59 pm

Sorry, maybe I misunderstood your problem.
Do you have three cell with one digit in each cell
Code: Select all   Expand viewCollapse view
| 1 | 2 | 3 |

or do you have one cell with a three digit number (or three character legth string of numeric characters)?
Code: Select all   Expand viewCollapse view
| 123 |


((The word 'TEXT' - in my example - is the name of a function for converting a number to formatted string. I am using English function names.))
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: add numbers in the same cell

Postby MrProgrammer » Thu Aug 20, 2015 5:58 pm

Kacem wrote:If there is formula to add numbers in the same cell. Example cell B1 258 formula that could bring me the total 15
Kacem wrote:It will be 3 numbers single digit, any numbers from 1....9.
Assuming that your cell is A1, all of these work for me based on my understanding of the situation:
=A1-INT(A1/100)*9-INT(A1/10)*9
=A1-9*SUMPRODUCT(INT(A1/{10;100}))
=LEFT(TEXT(A1;"000");1)+RIGHT(TEXT(A1;"000");1)+RIGHT(LEFT(TEXT(A1;"000");2);1) ← from Zizi64
=SUM(VALUE(MID(A1;ROW($A$1:$A$3);1))) ← from karolus
=SUMPRODUCT(VALUE(MID(A1;ROW(OFFSET(A1;0;0;LEN(A1);1));1))) ← from Lupp
where the green formula must be entered as an Array formula.

Kacem wrote:thanks zizi64 for your answer didn't work.
"didn't work" is not helpful in the forum because it tells us what did not happen. Please never use that phrase in a post. We need to know exactly what actions you took, what did happen, and what you expected to happen. Attaching a document demonstrating the problem is almost always helpful for us and will get your problem solved more quickly. If you typed a formula, pressed Enter, and got #VALUE!, tell us that. Then attach your document so we can see what's wrong. Often "it didn't work" means "it didn't do what I expected" and the real problem is that the program is behaving correctly but your expectations are incorrect. To help you solve the problem we need to know your expectations and your ultimate goal.

How to attach a document
Remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself.

Read section 1. Types of data in Ten concepts that every Calc user should know. You need to tell us if your cell contains a number or text.
Last edited by MrProgrammer on Tue Sep 15, 2015 6:04 am, edited 1 time in total.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3715
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: add numbers in the same cell

Postby Kacem » Thu Aug 20, 2015 8:29 pm

Maybe I didn't make myself understood. I am a real beginners in spreadsheet, please don't blame if I don't express myself the right way. In cell
A1 I have 123 in B1 I want 6 (result)
A2 222 B2 6
A3 215 B3 8
..
..
My formula in B1 should give what I was expecting 6. I tried all the formulas sent to me but not the right answer. Thanks at all of you trying to help me.
open office 4.1.1 windows 10
Kacem
 
Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

Re: add numbers in the same cell

Postby Zizi64 » Thu Aug 20, 2015 8:34 pm

Please upload your example .ods file with your typed formulas here
I can not imagine, what is wrong in your file, but my formula works for me, as you expected (I not tried the other suggested formulas).

digits.ods
(8.9 KiB) Downloaded 27 times
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: add numbers in the same cell

Postby Kacem » Thu Aug 20, 2015 9:13 pm

Hi Zizi64 thanks a lot for the help here a screen capture with the error message.
Attachments
capture d'ecran.png
open office 4.1.1 windows 10
Kacem
 
Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

Re: add numbers in the same cell

Postby Zizi64 » Thu Aug 20, 2015 9:37 pm

The string of my example formula has contained English function names. but your locale setting is not english. Use the localized function names.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: add numbers in the same cell

Postby Kacem » Thu Aug 20, 2015 9:51 pm

I got it.Sorted out. Thanks a lot for the help much nedeed. God Bless you
open office 4.1.1 windows 10
Kacem
 
Posts: 19
Joined: Thu Aug 20, 2015 8:41 am

Re: Add numbers in the same cell

Postby CaliRay » Mon May 27, 2019 12:35 am

I have the same basic request as the original but there seems to be a twist. I have two numbers, 111943 and 1101943. Both add up to 19. The the first number sums correctly at 19 but the second one results in 18.

Numbers are in cells A3 and A4. Formulas in cells B3 and B4.

$B3
Code: Select all   Expand viewCollapse view
=INT(MOD($A3/100000;10))+INT(MOD($A3/10000;10))+INT(MOD($A3/1000;10))+INT(MOD($A3/100;10))+INT(MOD($A3/10;10))+INT(MOD($A3;10))

$B4
Code: Select all   Expand viewCollapse view
=INT(MOD($A4/100000;10))+INT(MOD($A4/10000;10))+INT(MOD($A4/1000;10))+INT(MOD($A4/100;10))+INT(MOD($A4/10;10))+INT(MOD($A4;10))


Tried variations of the B3 formula in B4 due to the number in B4 being 7 digits instead of 6 but nothing gave the correct sum. Is the zero the problem? If so, can you explain what happens when a zero is found in the number?

Thank you for your advice.
Attachments
sum error in b4.ods
(8.79 KiB) Downloaded 8 times
OOo 4.1.3 on Windows 10 Home
CaliRay
 
Posts: 70
Joined: Sun Apr 26, 2009 6:20 pm

Re: [Solved] Add numbers in the same cell

Postby MrProgrammer » Mon May 27, 2019 4:51 am

CaliRay for B4 wrote:=INT(MOD($A4/100000;10))+INT(MOD($A4/10000;10))+INT(MOD($A4/1000;10))
+INT(MOD($A4/100;10))+INT(MOD($A4/10;10))+INT(MOD($A4;10))
1101943 has seven digits so you need seven terms:
=INT(MOD($A4/1E6;10))+INT(MOD($A4/1E5;10))+INT(MOD($A4/1E4;10))
+INT(MOD($A4/1E3;10))+INT(MOD($A4/1E2;10))+INT(MOD($A4/1E1;10))+INT(MOD($A4;10))


I prefer: =A4-9*SUMPRODUCT(INT(A4/{1E1;1E2;1E3;1E4;1E5;1E6}))
Or put 1E1 through 1E6 in six cells, X1:X6, and use =A4-9*SUMPRODUCT(INT(A4/$X$1:$X$6))

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3715
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] Add numbers in the same cell

Postby CaliRay » Tue May 28, 2019 7:14 pm

Thanks for the clarification and the working formula.
OOo 4.1.3 on Windows 10 Home
CaliRay
 
Posts: 70
Joined: Sun Apr 26, 2009 6:20 pm

Re: [Solved] Add numbers in the same cell

Postby Key WiteWolf » Sat Jun 15, 2019 1:29 am

Greetings and salutations.
Like the previous two users who asked, I also need help with a formula that will add numbers within the same cell. But the problem is, most of the formulas presented here seem to be based on the number of digits being a constant. Exactly 3 digits for the first user, and it looks like 6 and 7 digits for the second. The problem is, I need a formula that will add the numbers in a cell together individually when the number of digits varies. Because it will keep changing, and I need the calculation to keep up with that. Just to clarify, this is like the other two, in that I am wanting a single cell with something like 123 to return a result of 6 in the next cell. For anyone wanting to give examples, the first single cell in my current project for this is B2, with the result in C2. But I would love if someone explains what the parts of the formula do if an example is given. Since none of the other formulas here broke down the interaction of the parts making it up, and they were all so different from each other, I could not figure out how to modify it for my needs (though I did try for a while). Thank you in advance for your time.
OpenOffice 4.1.6 Windows 7 64 bit.
Key WiteWolf
 
Posts: 3
Joined: Sat Jun 15, 2019 1:15 am

Re: [Solved] Add numbers in the same cell

Postby Zizi64 » Sat Jun 15, 2019 6:27 am

The problem is, I need a formula that will add the numbers in a cell together individually when the number of digits varies.

Try to write your own macro function for this task. You can convert the passed integer number to string format, then you can enumerate the number of the characters. Then a loop (FOR-NEXT or DO-WHILE) can get each characters one-by-one from the text, and it can convert them back to numetic value, finally it will add them.

AddNumbers.ods
(10.02 KiB) Downloaded 5 times


The code of the macro function:

Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

Option Explicit

Function AddNumbers(iTheNumber as long) as Integer

Dim iSum as long
Dim iLenght as integer
Dim i as integer
Dim iOneNumber as integer
Dim sTheNumber as string
Dim sOneChar as string

   sTheNumber = Str(Abs(iTheNumber)) 'Supress the "-" sign when the input is a negative number
   iLenght = Len(sTheNumber)
   iSum = 0
   For i = 2 to iLenght ' from 2:  Because the converted string has one plus character for the "+" sign, but the "+" is not appeared, it is a whitespace)
      sOneChar = Mid(sTheNumber,i,1)
      iOneNumber = Val(sOneChar)
      iSum = iSum + iOneNumber
   Next i   
   
AddNumbers = iSum
end function
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Add numbers in the same cell

Postby robleyd » Sat Jun 15, 2019 9:59 am

This seems to work based on Lupp's response above; adapt it as you need.
Attachments
numerology.ods
(8.59 KiB) Downloaded 9 times
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2759
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Add numbers in the same cell

Postby Key WiteWolf » Sat Jun 15, 2019 2:52 pm

robleyd wrote:This seems to work based on Lupp's response above; adapt it as you need.


While this won't help in this specific case (because the number of digits keeps changing), this is very helpful for future projects where I actually *would* know the number of digits ahead of time, or where they are a fixed length. So thank you. For more "normal" projects, this is a very useful and handy list/guide.

Zizi64 wrote:Try to write your own macro function for this task. You can convert the passed integer number to string format, then you can enumerate the number of the characters. Then a loop (FOR-NEXT or DO-WHILE) can get each characters one-by-one from the text, and it can convert them back to numetic value, finally it will add them.


Well that's cool. I wasn't aware Calc actually allowed this level of control/alteration to the functions. But then I haven't seen many people talk about using macros in it so far. At least not ones this detailed. Thank you very much. I think this is what I needed. I am pretty sure I can work with it now and alter if needed for future projects. I really appreciate the explanation.
OpenOffice 4.1.6 Windows 7 64 bit.
Key WiteWolf
 
Posts: 3
Joined: Sat Jun 15, 2019 1:15 am

Re: [Solved] Add numbers in the same cell

Postby robleyd » Sat Jun 15, 2019 3:01 pm

While this won't help in this specific case (because the number of digits keeps changing)


You may not have noticed, but the formula is the same for each row and is capable of handling varying length numbers. I simply entered a number of rows with different length numbers by way of example to demonstrate the capabilities of the formula.. Try, for example, changing A1 to a different number of digits and see what happens.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2759
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Add numbers in the same cell

Postby Key WiteWolf » Sat Jun 15, 2019 4:24 pm

robleyd wrote:
While this won't help in this specific case (because the number of digits keeps changing)


You may not have noticed, but the formula is the same for each row and is capable of handling varying length numbers. I simply entered a number of rows with different length numbers by way of example to demonstrate the capabilities of the formula.. Try, for example, changing A1 to a different number of digits and see what happens.


Ah okay, I see where I misunderstood. My apologies. Yes, this works. Thank you. Though it is good to know I can also create macros, this method is probably faster for my current situation. Thanks for pointing it out again.
OpenOffice 4.1.6 Windows 7 64 bit.
Key WiteWolf
 
Posts: 3
Joined: Sat Jun 15, 2019 1:15 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests