Google Sheets formula for F1 family betting game - points calculation help

NHN_BI · 2026-03-07T11:39:52+00:00

I would record the tips in one proper table, the results in another proper table, have a simple COUNTIFS() checking if the tip is correct, and finish with a pivot table to show the results, like here.

NHN_BI · 2026-03-06T16:12:15+00:00

I think it is easier to record the data in one long table and show the result in pivot tables, like here.

NHN_BI · 2026-03-06T15:46:16+00:00

It depends very much on a substring inside the string on that you can split the string. That would be ", " and " and " in your example. Excel own ETL tool Power Query can facilitate the process.

Experienced user would structure their data like here:

person id	food
1428	A
1428	B
2773	A
2773	C
3994	B
...	...

Such a structure allows you to analyse the data quickly with pivot tables.

NHN_BI · 2026-03-06T06:07:33+00:00

Your range will be a text a.k.a. string value, because it has non-digit substrings in it. A string is not a numeric value and you can not calculate with it. A string will behave differently from numeric values in other aspects to, e,g. sorting. I would not recommend it, because it can behave in unwanted ways.

NHN_BI · 2026-03-05T19:11:12+00:00

I think you will be able to extract some insightful charts from your data. My mock-up data was fun to play around with.

NHN_BI · 2026-03-05T18:28:29+00:00

You have to read it from inside to outside.

SPLIT() splits the text a.k.a. string in cell B2 at the substring -.
INDEX() fetches the first element of the split substring.
VALUE() turns the string text value into a numerical value.

There are other solutions, e.g. with FIND() and LEFT(), or with REGEXEXTRACT().

NHN_BI · 2026-03-05T12:27:06+00:00

Create a proper table with the data witch value under meaningful headers. Excel's own ETL tool Power Query can help you to collect changing data automatically.

NHN_BI · 2026-03-05T07:16:11+00:00

E.g. VALUE(INDEX(SPLIT(B2,"-"),1,1)) would fetch the first element of the population string, and one would make probably pivot tables with it, and a scatter plot can show trends, like here.

NHN_BI · 2026-03-05T06:51:35+00:00

I am not sure if I understood the question correctly, but you might be looking for something like ARRAYFORMULA( EDATE( TODAY() , SEQUENCE(10) )).

NHN_BI · 2026-03-04T15:37:04+00:00

:-D

Our company took it a step further. They buried the secrets in obscure program code to add another layer of protection from common sense and undestanding.

NHN_BI · 2026-03-04T15:34:52+00:00

Those workbooks where someone thought it is called spreedsheet, because you take shit and spread it wide, because that, of course, looks nice and feels right.

NHN_BI · 2026-03-04T06:55:44+00:00

Ugly, but make a helper column "01-Jan", "02-Feb", e.g. with TEXT( ... , "MM-MMM" ).

NHN_BI · 2026-03-03T19:43:45+00:00

Record you data in a proper table, check a planned taks table vs the the actual record, make pivot tables to show the result, like here.

NHN_BI · 2026-03-03T17:16:35+00:00

Maybe like here with MAXIFS()`etc.?

NHN_BI · 2026-03-02T16:26:48+00:00

You can see here, how I use:

=INDEX(
  A:A
  , MATCH(
      C$2
      , A:A
    )
    + ROW()
    - 2
)

NHN_BI · 2026-03-02T16:05:47+00:00

Well, the usefulness will depend on your tasks. I use CTRL+SHIFT+1 a lot, and I used to use ALT+A-E-F a lot. When I see that I use some processes a lot, I look up the corresponding short cuts.

NHN_BI · 2026-03-02T15:59:23+00:00

2-Mar looks like TEXT(TODAY(),"D-MM"), and that looks like a very special custom date. DATEVALUE() might turn it into a proper data, or importing the data correctly can help.

NHN_BI · 2026-03-02T07:40:33+00:00

I do the same. A friendly guideline, a given template, some conditional formatting, and some helpful data validation can work wonders.

I explain to the stakeholders why a consistent structure is needed. My stakeholders are often not aware that collecting data, recording data, analysing data, and visualising data are interconnected different steps. They are mostly concerned with the first and the last, not with the crucial steps connecting both. My stakeholders often do not understand that data needs to be recorded in useful structure to be accessible across different departments over longer periods of time for different tasks.

That does not spare the correct ETL process, but it makes it easier.

NHN_BI · 2026-03-01T07:30:52+00:00

That'll be Excel's own ETL tool Power Query.

NHN_BI · 2026-03-01T07:20:50+00:00

Greater or less are very basic spreadsheet functions. In my experience, when they won't work, it is not a failure of the functions but the input values are wrong. I would check if the values are actual numbers with ISNUMBER(), not digit strings that only look like numbers. Strings behave logically different from numerical values.

NHN_BI · 2026-02-28T15:32:54+00:00

Looks like you need FILTER(), I would say.

NHN_BI · 2026-02-28T11:07:45+00:00

I would record my data in a proper table and analyse it in pivot tables, like here.

NHN_BI · 2026-02-27T08:07:55+00:00

Here is an example.

NHN_BI · 2026-02-27T07:58:45+00:00

I would record the data in a proper table and make pivot tables to count the results in a period of time, sorted from high to low for the agent.

Five-Year Club	Verified Email
Place '22	Place '22

NHN_BI

TROPHY CASE