Is it possible to search backwards in Calc?

English
#1

Dear All

I’ve exhausted my knowledge of LibreOffice Calc with this little problem. I’ve racked my brains for days trying to figure this out, and I’d be SO grateful if someone could point me in the right direction…

I’m creating a game for my grandson which uses a LibreOffice Calc spreadsheet to produce a series of 20 random rolls of a theoretical triangular ‘dice’, which can only produce the letters A, B or C as its output. Before each roll of the dice, I want the spreadsheet to be able to determine which of the three letters, A, B or C, has taken the longest time to appear out of the other two.

I use =RANDBETWEEN(1,3) to get a random number, then convert that to A, B or C using =IF(A1=1,“A”,IF(A1=2,“B”,IF(A1=3,“C”,""))).

This is what I need the sheet to do:

Roll 1 A
Roll 2 A
Roll 3 C
Roll 4 A
Roll 5 B

Before roll 6 it calculates that C has taken the longest time to appear in the last few rolls.

Roll 6 B

It recognises, again, that C has taken the longest time to appear in the last few rolls.

Roll 7 C

A has now taken the longest time to appear in the last few rolls.

Roll 8 A

B has now taken the longest time to appear in the last few rolls.

Roll 9 B

C has now taken the longest time to appear in the last few rolls.

Roll 10 A

etc etc.

I don’t want the spreadsheet to stop between ‘rolls’, I want to press F9 and get the whole sheet to calculate once, all in one go, with the results of its findings (above) alongside the vertical list of results somewhere. The results of rolls of the dice are ordered from earliest at the top to latest at the bottom. I just need it to be able to detect before each roll of the dice which letter has taken the longest to come out at that point in time. I’m guessing what I really want is a version of VLOOKUP or XLOOKUP which will search backwards, sequentially, through past results, and give me the position of the last occurrence of each of the three outcomes. I’ve tried searching backwards but it doesn’t seem to work.

I know I could write a little program in Basic which would easily handle this, but I’m trying to show my grandson how spreadsheets work.

Is what I’m asking for not really within the scope of a spreadsheet?

All help and advice gratefully appreciated.

Steve P

#4

I’m very grateful to both of you for your replies and very helpful spreadsheets.

My first realisation was that you both clearly have a much better grasp of Calc than I do, and I needed to look at the LibreOffice help file to understand how your sheets worked.

What I need is something simpler. If we look at the following output from rolls of a 3-sided dice, after each roll I want to know which letter hasn’t been out for the longest time:

Roll # Result
Roll 1 B
Roll 2 A
Roll 3 C
Roll 4 C
Roll 5 A
Roll 6 C
Roll 7 B
Roll 8 B
Roll 9 B
Roll 10 C
Roll 11 A
Roll 12 B
Roll 13 B
Roll 14 C
Roll 15 B
Roll 16 A
Roll 17 C
Roll 18 B
Roll 19 B
Roll 20 C

etc

If, after Roll 14, I were to look back through the last ten rolls before each new roll (in the assumption that each of the three letters would probably have come out at least once in ten rolls), I can see at a glance that the letter I haven’t seen for the longest time is A.

That’s easy for me, I recognise it instantly. But how can I get Calc to do that for me for the entire history of rolls each time the sheet recalculates, so that it would look like this:

Roll # Result Not seen for longest time
Roll 1 B
Roll 2 A
Roll 3 C B
Roll 4 C B
Roll 5 A B
Roll 6 C B
Roll 7 B A
Roll 8 B A
Roll 9 B A
Roll 10 C A
Roll 11 A B
Roll 12 B C
Roll 13 B C
Roll 14 C A
Roll 15 B A
Roll 16 A C
Roll 17 C B
Roll 18 B A
Roll 19 B A
Roll 20 C A

If someone can come up with a way of doing that I’d be very much obliged. I’ve spent days with Calc trying to do this, trying every formula I can think of, reading the help files, watching YouTube videos on Calc, but to no avail.

As you can probably gather I’m not a Calc expert by any means, I’ve only got very limited experience of using it, but I’m keen to learn.

Any help, really appreciated.

Steve

#7

Hello Steve!
Does your table start from the first cell, A1? Please try in C2

=IFERROR(OFFSET($B$1;MIN(MAXIFS(ROW($B$1:B2);$B$1:B2;{"A";"B";"C"}))-1;0);"")
#8

I did as you asked John, but nothing appeared in C2:

2 B
2 B
3 C
1 A
2 B
2 B
2 B
2 B
2 B
2 B
2 B
1 A
2 B
1 A
2 B
2 B
2 B
2 B
2 B
3 C
3 C
3 C
3 C
1 A
3 C
3 C
1 A
2 B
2 B
2 B
1 A
2 B
2 B
1 A
3 C
2 B
2 B
3 C
1 A
2 B
3 C
1 A
2 B
3 C
2 B
2 B
1 A
2 B
3 C
#9

I meant this - backwardSearch.ods (15.4 KB)

2 Likes
#12

@JohnSUN’s result here definitely demonstrates a method with wide theoretic usage. I think it is worth explaining a little more, so I’ve attached a spreadsheet with such an explanation. I hope it is correct. Almost literally, @JohnSUN is playing three dimensional chess here :grinning: by using the matrix result of MAXIFS.

BackwardSearchExplained.ods (18.9 KB)

#2

(I don’t think the term “search backwards” is a good description of what is needed here.)
…
You may want to study the attached example.
disask1025760LongestMissingResult.ods (77.1 KB)

#13

I have to admit:

  1. I did not thoroughly study all the posts by others.
  2. The demo attached to my above answer contains errors in the secondary evaluation concerning the wide-spread suspicion a long missed result might be preferred.

I now attach a file containing a solution based on the lengths of the runs of failing to get each one of the possible results. No search backwards again, of course.

disask1025760LongestMissingResultFocusingOnRuns.ods (172.8 KB)

#3

I like the idea of this spreadsheet–it would definitiely give a student a feel for randomness and the fact that 1 out of 3 doesn’t mean everything happens every three rolls.

I took a mathematical approach. The attached example uses modular arithmatic to encode the result of each roll with the roll number itself, then uses more modular arithmatic to distinguish the aging and run length for each roll value. It has the benefit of being expandable quite easily up to 10-sided dice.

Like @Lupp’s example, this example also uses helper columns. The roll is determined in Column B as a number. Column A shows this as a letter. All other calculations are done with numbers, not letters, although the column headers also convert numbers to letters for convenience.

Column C encodes the roll number (relative, that is, actually the row number) and the roll result into a single integer. The row is multiplied by 10 to “leave room” to add the result of the roll for the final code.

Columns D through F look at their header to know what roll result they pertain to then store:

  • -1 in case of an out-of-bounds situation (no previous roll of the header number)
  • the code for the last occurence of a roll of the header number if the current roll is not of the header number–the code just multiplies the row by 10 and adds the roll result
  • the new code for the roll if the current roll is of the header number

Columns G through I:

  • test for out-of-bounds and enter blanks in that case
  • decode the row/roll difference between the current roll and the last roll for the given header number (actually, the related columns header number, such as D’s header for Column G). They do this by dividing 10 back out of the encoding and calculating the difference between the current roll and the last roll, where the last row comes from the D through F set of columns, and so indicates the last time the same roll result occurred.

For the run length, Columns J through L find the difference between the current row and the previous row for the given header number (again, really the related column’s header number).

Then Columns M through O:

  • Add 1 for the first occurrence of a roll of their related header number in order to start the run length at 1 if the current roll is of the specified result.
  • Test to see if the difference between an entry in J through L and the previous such entry is 10, which would mean that the previous entry was the same roll result (since 10 means that the added bit for the roll result has a difference of 0).
  • If so, M through O add one to the run length, that is, to their own previous cell.

Use Ctrl+Shift+F9 to force a new set of rolls.

Edit: Improved example spreadsheet. Added explicit column of letter of most-left-behind letter. Added tab with results for a 6-sided die.

RollAgingAndRuns.ods (47.3 KB)

1 Like
#5

Sorry, my spacing for those columns was disregarded by the website. In the second example there are three headings and columns, I hope it’s clear enough.

Steve

#6

If do you paste from calc, the columns are autocreated.
I just edited your comment data.

1 Like
#11

See my RollAgingAndRuns again, now edited with explicit results and a tab for 6-sided die. This example spreadsheet might serve well in an educational setting such as an early probability class.

Unsurprisingly, @JohnSun’s method is brilliant and satisfies the OP need very cleanly.

#10

Column E:E has the formula you want for those choice not seen for longest time, please check it:
LO-CALC_randoms-ABC_v0001.ods (18.1 KB)
or the 2nd file detailed, see here column F:F:
LO-CALC_randoms-ABC_v0002.ods (19.9 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.