Newbie here

[hawnal]

I'm trying to write a spreadsheet using google sheets, similar to excel, for european roulette (single zero).
Here's what I'm hoping to create.
Legend: Column 1: 3,6,9,12,15,18,21,24,27,30,33,36
Column 2: 2,5,8,11,14,17,20,23,26,29,32,35
Column: 3: 1,4,7,10,13,16,19,22,25,28,31,34
1st 12: 1,2,3,4,5,6,7,8,9,10,11,12
2nd 12: 13,14,15,16,17,18,19,20,21,22,23,24
3rd 12: 25,26,27,28,29,30,31,32,33,34,35,36


Spin result: Column 1 Column 2 Column 3 1st 12 2nd 12 3rd 12
1 3 1
14 2 2
36 1 3

The ideas is to enter 1) spin result in 1st section, then display 2) which column and 3) which group of 12 it as the example shows.

Can someone help this newbie?

Thank you
Al

 

[Kaper]

If spin result is in A1 then formulas for:
column

=3-mod(A1-1,3)

group

=roundup(A1/12,0)

PS. You are probably not-so-frequent forums user, so let me advise: Please use meaningful thread titles. The title shall be a concise summary of the problem

(in the formula on screenshot you can see semicolon as separator - this is because of my local central-european settings)

 

[hawnal]

Kaper
Thank you for your help

 

[hawnal]

I have another question if you will.
In your screenshot

If spin result is in A1 then formulas for:
column

=3-mod(A1-1,3)

group

=roundup(A1/12,0)

PS. You are probably not-so-frequent forums user, so let me advise: Please use meaningful thread titles. The title shall be a concise summary of the problem

View attachment 117094 (in the formula on screenshot you can see semicolon as separator - this is because of my local central-european settings)

I’d like to create 2 more results from columns B&C.
Most Common and Least Common. Let’s use 5 results from eac.

Thank you again

 

[Kaper]

It's ... crazy.
Have you marked your own text, including quotation of my proposition, as a solution ?

And then wrote next question as a quote from my text something I never wrote?

As for the merit:
Do you want to say which are the most and least common values in columns B and C if you have there some resulkts calculated the same way but from different spins? How about equaly rare or common results ?

My counterproposition is to make a list of all 3 results and their frequency and then sort this list descending, so the most common results are presented on the top. so the first column will be the result and the second column the counter. That way you will see for instance 2 ex-aequo most common results. See screenshot.
in E1 (no copying, this is spill-type formula, so it spills the result into whole E1:F3 range):

=TAKE(SORT(HSTACK(SEQUENCE(3),FREQUENCY(B:B,SEQUENCE(3))),2,-1),3,2)

and similar, just referring to column C not B in H1:

=TAKE(SORT(HSTACK(SEQUENCE(3),FREQUENCY(C:C,SEQUENCE(3))),2,-1),3,2)

Edit: or even a shorter formulas for E1 and H1:

=SORT(HSTACK(SEQUENCE(3),COUNTIF(B:B,SEQUENCE(3))),2,-1)
=SORT(HSTACK(SEQUENCE(3),COUNTIF(C:C,SEQUENCE(3))),2,-1)

 

[smozgur]

Kaper
Thank you for your help

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.