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