[Solved] Simple but non-standard calculation

[LongJohnnyLongjohns]

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.

[keme]

With the string of digits in A1:

Code: Select all

=LEN(A1)-SEARCH(A1;"1*$")+1

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.

[Bald Eagle]

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.

[MrProgrammer]

Hi, and welcome to the forum.

PS: I've entered the numbers in a row just for ease of posting. In my sheet they run down a single column.

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.

201808170921.ods

(10.95 KiB) Downloaded 89 times

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

[John_Ha]

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.

sum.ods

(9.28 KiB) Downloaded 89 times

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.

[Bald Eagle]

Hi,
See if this is what you're asking for:

[RusselB]

For John_ha's code of

Code: Select all

=(B1+A2)*A2

, 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.

[LongJohnnyLongjohns]

Hi, and welcome to the forum.

PS: I've entered the numbers in a row just for ease of posting. In my sheet they run down a single column.

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.

201808170921.ods

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

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.

[LongJohnnyLongjohns]

For John_ha's code of

Code: Select all

=(B1+A2)*A2

, 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.

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.

[LongJohnnyLongjohns]

Hi,
See if this is what you're asking for:

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.

[LongJohnnyLongjohns]

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.

Clipboard01.png

sum.ods

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.

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.

[LongJohnnyLongjohns]

With the string of digits in A1:

Code: Select all

=LEN(A1)-SEARCH(A1;"1*$")+1

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.

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.

[Alex1]

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.

Mr. Programmer was the first one who interpreted your question correctly, so how can you call his answer unnecessary?

[RusselB]

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

[robleyd]

I note that MrProgrammer also provided an attachment in his post which appears to meet the requirements of the original question.

[keme]

...
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.

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.

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!