[Solved] Simple but non-standard calculation
-
- Posts: 6
- Joined: Thu Aug 16, 2018 9:14 pm
[Solved] Simple but non-standard calculation
I've been searching an answer to the following question for 3 days and have finally relinquished my pride and given in. So, I'm here mining your combined brilliance for help.
I've a column of just 1s and 0s. I need to add up the 1s. BUT, each time a 0 rears its head the sum must revert to zero and start again. So:
1011111 would need to have an answer of 5
11111111011 this would need an answer of 2
111101110110 this would be 0
Can anyone think of a way that this can be done?
Many Thanks LJLJ
PS: I've entered the numbers in a row just for ease of posting. In my sheet they run down a single column.
I've a column of just 1s and 0s. I need to add up the 1s. BUT, each time a 0 rears its head the sum must revert to zero and start again. So:
1011111 would need to have an answer of 5
11111111011 this would need an answer of 2
111101110110 this would be 0
Can anyone think of a way that this can be done?
Many Thanks LJLJ
PS: I've entered the numbers in a row just for ease of posting. In my sheet they run down a single column.
Last edited by MrProgrammer on Fri Jan 01, 2021 2:56 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.5
windows 10
windows 10
Re: Simple but non-standard calculation
With the string of digits in A1:
The "1*$" part is a regular expression (regex). For this to work you have to enable the use of regular expressions in formulas.
Select menu item Tools - Options. Expand the Calc branch in the left pane and select Calculate. Tick the "Use regular expressions ..." item.
If you don't know how to use regular expressions, look it up in the help system to get an overview of possibilities.
Note that some functions do not support regex even with this option enabled. SEARCH() does, but the similar function FIND() does not.
Code: Select all
=LEN(A1)-SEARCH(A1;"1*$")+1
Select menu item Tools - Options. Expand the Calc branch in the left pane and select Calculate. Tick the "Use regular expressions ..." item.
If you don't know how to use regular expressions, look it up in the help system to get an overview of possibilities.
Note that some functions do not support regex even with this option enabled. SEARCH() does, but the similar function FIND() does not.
-
- Posts: 68
- Joined: Wed Apr 19, 2017 9:22 pm
Re: Simple but non-standard calculation
use find(), and then right () to trim off the leftmost portion before and including the 0.
"Stack" a series of cells that use the cell above / to the left of them to do the same thing iteratively.
Use a number of stacked cells that exceeds the number of zeros to be encountered.
If no zero, just return the contents unchanged.
Then you have a penultimate result of just 1's to be counted.
"Stack" a series of cells that use the cell above / to the left of them to do the same thing iteratively.
Use a number of stacked cells that exceeds the number of zeros to be encountered.
If no zero, just return the contents unchanged.
Then you have a penultimate result of just 1's to be counted.
OpenOffice 4.1.1 on Windows 7
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Simple but non-standard calculation
Hi, and welcome to the forum.
[Tutorial] Ten concepts that every Calc user should know
I believe other volunteers have misinterpreted your post. You should have attached a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Also, you did not indicate if the "numbers" in the column are numbers or text. A different solution is needed if your 0 and 1 are text. An attachment would have shown us if the values are text or numeric. 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.LongJohnnyLongjohns wrote:PS: I've entered the numbers in a row just for ease of posting. In my sheet they run down a single column.
[Tutorial] Ten concepts that every Calc user should know
Last edited by MrProgrammer on Fri Aug 17, 2018 8:22 pm, edited 1 time in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Simple but non-standard calculation
Use the adjacent B column to sum the "previous B column value plus A column value" if An=1, and set to zero if An=0. eg =IF(A2=1;(B1+A2);0) after putting B1=0.
Without the IF just multiply the running sum in B by the A column value as in =(B1+A2)*A2 - that sets the running sum back to zero at zero without changing it at 1.
Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
Without the IF just multiply the running sum in B by the A column value as in =(B1+A2)*A2 - that sets the running sum back to zero at zero without changing it at 1.
Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
LO 6.4.4.2, Windows 10 Home 64 bit
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
-
- Posts: 68
- Joined: Wed Apr 19, 2017 9:22 pm
Re: Simple but non-standard calculation
Hi,
See if this is what you're asking for:
See if this is what you're asking for:
- Attachments
-
- Count_Ones.ods
- Count contiguous ones after last zero
- (13.52 KiB) Downloaded 86 times
OpenOffice 4.1.1 on Windows 7
Re: Simple but non-standard calculation
For John_ha's code of , imo, is superior to most of the rest of the suggestions, as it will work even with any numbers, with the reset being at 0.
Code: Select all
=(B1+A2)*A2
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
-
- Posts: 6
- Joined: Thu Aug 16, 2018 9:14 pm
Re: Simple but non-standard calculation
All the information is here and no more is needed. It is a very simple one column of numbers as clearly expressed. If you do not understand very simple plain English then I don't think having a screenshot will help you. It's very clear and simple what I asked. You could not have a more basic list of two numbers in a single column. You also seem to be saying 'some people' when in fact you mean YOU. It's quite fine, the person who actually answered with an intelligent response understood clearly and his answer will almost certainly lead to the solution. He didn't need or desire any more info and it's worrying that you do. Thank you for your quite unnecessary reply anyhow.MrProgrammer wrote:Hi, and welcome to the forum.
I believe other volunteers have misinterpreted your post. You should have attached a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Also, you did not indicate if the "numbers" in the column are numbers or text. A different solution is needed if your 0 and 1 are text. An attachment would have shown us if the values are text or numeric. 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.LongJohnnyLongjohns wrote:PS: I've entered the numbers in a row just for ease of posting. In my sheet they run down a single column.
[Tutorial] Ten concepts that every Calc user should know
OpenOffice 4.1.5
windows 10
windows 10
-
- Posts: 6
- Joined: Thu Aug 16, 2018 9:14 pm
Re: Simple but non-standard calculation
Thank you very much RusselB, with your help and the help of others here we're almost there. Once I'm done I'll mark the subject as closed and thank you all properly.RusselB wrote:For John_ha's code of, imo, is superior to most of the rest of the suggestions, as it will work even with any numbers, with the reset being at 0.Code: Select all
=(B1+A2)*A2
OpenOffice 4.1.5
windows 10
windows 10
-
- Posts: 6
- Joined: Thu Aug 16, 2018 9:14 pm
Re: Simple but non-standard calculation
Thank you very much Bald Eagle, I'll get back to you sometime tomorrow when I've had time to test your idea. But my tremendous thanks even for trying.Bald Eagle wrote:Hi,
See if this is what you're asking for:
OpenOffice 4.1.5
windows 10
windows 10
-
- Posts: 6
- Joined: Thu Aug 16, 2018 9:14 pm
Re: Simple but non-standard calculation
Thank you so much John. i've not had time to test it yet but your results look like it's what i'm after. i'll give it a go and then come back and thank you properly regardless.John_Ha wrote:Use the adjacent B column to sum the "previous B column value plus A column value" if An=1, and set to zero if An=0. eg =IF(A2=1;(B1+A2);0) after putting B1=0.
Without the IF just multiply the running sum in B by the A column value as in =(B1+A2)*A2 - that sets the running sum back to zero at zero without changing it at 1.
Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
OpenOffice 4.1.5
windows 10
windows 10
-
- Posts: 6
- Joined: Thu Aug 16, 2018 9:14 pm
Re: Simple but non-standard calculation
Keme, your ide has been the only one I've been able to test as yet but it seems very promising from my first attempts. I'll let you know for sure tomorrow when I've had time to test it out properly.keme wrote:With the string of digits in A1:The "1*$" part is a regular expression (regex). For this to work you have to enable the use of regular expressions in formulas.Code: Select all
=LEN(A1)-SEARCH(A1;"1*$")+1
Select menu item Tools - Options. Expand the Calc branch in the left pane and select Calculate. Tick the "Use regular expressions ..." item.
If you don't know how to use regular expressions, look it up in the help system to get an overview of possibilities.
Note that some functions do not support regex even with this option enabled. SEARCH() does, but the similar function FIND() does not.
OpenOffice 4.1.5
windows 10
windows 10
Re: Simple but non-standard calculation
Mr. Programmer was the first one who interpreted your question correctly, so how can you call his answer unnecessary?LongJohnnyLongjohns wrote:All the information is here and no more is needed. It is a very simple one column of numbers as clearly expressed. If you do not understand very simple plain English then I don't think having a screenshot will help you. It's very clear and simple what I asked. You could not have a more basic list of two numbers in a single column. You also seem to be saying 'some people' when in fact you mean YOU. It's quite fine, the person who actually answered with an intelligent response understood clearly and his answer will almost certainly lead to the solution. He didn't need or desire any more info and it's worrying that you do. Thank you for your quite unnecessary reply anyhow.
AOO 4.1.15 & LO 24.2.2 on Windows 10
Re: Simple but non-standard calculation
I think a big part of the problem between what was given as an example in the original post, and what the OP actually had, is the fact that the numbers were presented in a row manner, when they're actually in a column... as stated in the original post.
MrProgrammer did not ask for a screenshot, but suggested attaching a sample of your actual spreadsheet. In fact, most, if not all, posts to the forum, whether it be for Calc, Writer, Base, Draw, or whatever, would make the job of finding (and giving) answers a lot easier, as that way the helper(s) have documentation that they can actually test with, without having to make guesses as to some details that may not have been included or not clear in the post(s).
I think, @Alex1, that the OP meant that he thought that the request for the attachment was unnecessary, not that MrProgrammer's answer was unnecessary
MrProgrammer did not ask for a screenshot, but suggested attaching a sample of your actual spreadsheet. In fact, most, if not all, posts to the forum, whether it be for Calc, Writer, Base, Draw, or whatever, would make the job of finding (and giving) answers a lot easier, as that way the helper(s) have documentation that they can actually test with, without having to make guesses as to some details that may not have been included or not clear in the post(s).
I think, @Alex1, that the OP meant that he thought that the request for the attachment was unnecessary, not that MrProgrammer's answer was unnecessary
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
Re: Simple but non-standard calculation
I note that MrProgrammer also provided an attachment in his post which appears to meet the requirements of the original question.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Simple but non-standard calculation
Ah, but it is probably based on my misunderstanding you. I didn't read the last part of your request properly, so I assumed that you needed to count the trailing 1's in a single string of digits. My suggestion is not relevant for a running count/sum down a column of digits.LongJohnnyLongjohns wrote:...
Keme, your ide has been the only one I've been able to test as yet but it seems very promising from my first attempts. I'll let you know for sure tomorrow when I've had time to test it out properly.
Other suggestions have given a solution to a running column sum, and my misunerstanding was noted by MrProgrammer, so I didn't think more about editing my suggestion to warn about my mistake. Sorry!