Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 07 Aug 2018 11:33:40 +0200Calc Rows between matching entrieshttps://ask.libreoffice.org/en/question/162212/calc-rows-between-matching-entries/Hey everyone, I'm new here so just a short Hello and hoping you're all well.
I have a one column list of over 3000 rolls of a dice. E.g (shows it as a row here, but it is a column).
3
6
5
3
2
3
4
4
2
2
3
etc, etc
I need a way to know how many rolls before the same number came up again. So, in the above snippet of the data, if I checked #3 it would return a result of: 3, 2, 5 (from the 1st #3 to the 2nd was 3 rolls, from the 2nd to 3rd was 2 rolls and from the 3rd to the 4th was 5 rolls). I could of course count all this manually but going through a data set of 3000 figures 6 separate times would be very time consuming and just one error could throw the statistics off. Rows between each matching item would also work, I'd just have to add +1 to each value returned.
Can anyone help me with a formula that could do this for me?
Many Thanks in advance, Shane.
Tue, 07 Aug 2018 08:22:30 +0200https://ask.libreoffice.org/en/question/162212/calc-rows-between-matching-entries/Answer by Mike Kaganski for <p>Hey everyone, I'm new here so just a short Hello and hoping you're all well.</p>
<p>I have a one column list of over 3000 rolls of a dice. E.g (shows it as a row here, but it is a column).</p>
<p>3
6
5
3
2
3
4
4
2
2
3
etc, etc </p>
<p>I need a way to know how many rolls before the same number came up again. So, in the above snippet of the data, if I checked #3 it would return a result of: 3, 2, 5 (from the 1st #3 to the 2nd was 3 rolls, from the 2nd to 3rd was 2 rolls and from the 3rd to the 4th was 5 rolls). I could of course count all this manually but going through a data set of 3000 figures 6 separate times would be very time consuming and just one error could throw the statistics off. Rows between each matching item would also work, I'd just have to add +1 to each value returned.</p>
<p>Can anyone help me with a formula that could do this for me?</p>
<p>Many Thanks in advance, Shane.</p>
https://ask.libreoffice.org/en/question/162212/calc-rows-between-matching-entries/?answer=162226#post-id-162226Assuming that your data is in column A, starting from A1: enter this formula to B3:
=ROW()-MAXIFS(ROW($A$1:$A2);$A$1:$A2;$A3)
Unfortunately, this doesn't work for B2 using formula like `=ROW()-MAXIFS(ROW($A$1:$A1);$A$1:$A1;$A2)` - I have just filed tdf#119137 for that; so for the second row, you'd need to count manually (first row, naturally, doesn't have previous rows, including matching).Tue, 07 Aug 2018 10:23:07 +0200https://ask.libreoffice.org/en/question/162212/calc-rows-between-matching-entries/?answer=162226#post-id-162226Answer by ShaneLevene for <p>Hey everyone, I'm new here so just a short Hello and hoping you're all well.</p>
<p>I have a one column list of over 3000 rolls of a dice. E.g (shows it as a row here, but it is a column).</p>
<p>3
6
5
3
2
3
4
4
2
2
3
etc, etc </p>
<p>I need a way to know how many rolls before the same number came up again. So, in the above snippet of the data, if I checked #3 it would return a result of: 3, 2, 5 (from the 1st #3 to the 2nd was 3 rolls, from the 2nd to 3rd was 2 rolls and from the 3rd to the 4th was 5 rolls). I could of course count all this manually but going through a data set of 3000 figures 6 separate times would be very time consuming and just one error could throw the statistics off. Rows between each matching item would also work, I'd just have to add +1 to each value returned.</p>
<p>Can anyone help me with a formula that could do this for me?</p>
<p>Many Thanks in advance, Shane.</p>
https://ask.libreoffice.org/en/question/162212/calc-rows-between-matching-entries/?answer=162231#post-id-162231Mike, thanks for your time and reply. I just found the answer by creating a validity list and then using an IF and MATCH formula and then dragging it down. It works for all numbers as soon as I choose it from my validity list.
But thank you anyway. It looks like you was on the right path and not too far away from what I eventually did. It wasn't my cleverness which solved it I found a similar problem elsewhere on the web and struck gold with that.Tue, 07 Aug 2018 11:24:36 +0200https://ask.libreoffice.org/en/question/162212/calc-rows-between-matching-entries/?answer=162231#post-id-162231Comment by Mike Kaganski for <p>Mike, thanks for your time and reply. I just found the answer by creating a validity list and then using an IF and MATCH formula and then dragging it down. It works for all numbers as soon as I choose it from my validity list.</p>
<p>But thank you anyway. It looks like you was on the right path and not too far away from what I eventually did. It wasn't my cleverness which solved it I found a similar problem elsewhere on the web and struck gold with that.</p>
https://ask.libreoffice.org/en/question/162212/calc-rows-between-matching-entries/?comment=162234#post-id-162234Please edit the answer to include the formulas and steps to your answer, to make it useful to those who might seek for a similar solution. The goal of the site is not only to help those who ask a question, but also to keep solutions so that others could benefit.Tue, 07 Aug 2018 11:33:40 +0200https://ask.libreoffice.org/en/question/162212/calc-rows-between-matching-entries/?comment=162234#post-id-162234