[Solved] Formule Generator

[Bart Van Hout]

I want a formula in column F that shows the score out of 20 based on the swimming time. The swimming time is in column F. The first digit in column F represents the number of minutes. The last two digits of column F represent the number of seconds. I would like to see the number in column F converted into a score out of 20, taking into account whether it is a boy or a girl. This is indicated in column C: boy (B), girl (G).

On the second tab, there is a points table that links the score to the number of seconds swum. Three examples for a boy for clarification:
312 = 19
315 = 19
316 = 18

Can someone provide me with the file with the formula filled in?

Thank you in advance!

 

[shamimarita]

Hello Bart Van Hout,

Because your "time" in Boys and Girls sheets is stored like "3'10" (minute' second format), you first need to convert it to total seconds so that you can compare.

In the "Boys" sheet:
Insert a new helper column C next to "Time (boys)".
Formula in C2:
=LEFT(B2,FIND("'",B2)-1)*60 + MID(B2,FIND("'",B2)+1,2)
This converts "3'10" → 190 seconds.

Same for "Girls" sheet:
Insert helper column C next to "Time (girls)".
Formula in C2:
=LEFT(B2,FIND("'",B2)-1)*60 + MID(B2,FIND("'",B2)+1,2)

In Main Sheet:
Formula for G2:
=IF(C2="J",
IFERROR(LOOKUP((INT(F2/100)*60) + MOD(F2,100), Boys!C$2:C$100, Boys!A$2:A$100), 0),
IFERROR(LOOKUP((INT(F2/100)*60) + MOD(F2,100), Girls!C$2:C$100, Girls!A$2:A$100), 0)
)
Explanation:

• INT(F2/100) → Takes the minute part (e.g., 3 from 310).
• MOD(F2,100) → Takes the seconds part (e.g., 10 from 310).
• (Minutes × 60) + Seconds → Total seconds.
• Then LOOKUP into the helper column for a total of seconds to find the correct score!

 

[Bart Van Hout]

Hello Bart Van Hout,

Because your "time" in Boys and Girls sheets is stored like "3'10" (minute' second format), you first need to convert it to total seconds so that you can compare.

In the "Boys" sheet:
Insert a new helper column C next to "Time (boys)".
Formula in C2:
=LEFT(B2,FIND("'",B2)-1)*60 + MID(B2,FIND("'",B2)+1,2)
This converts "3'10" → 190 seconds.

Same for "Girls" sheet:
Insert helper column C next to "Time (girls)".
Formula in C2:
=LEFT(B2,FIND("'",B2)-1)*60 + MID(B2,FIND("'",B2)+1,2)

In Main Sheet:
Formula for G2:
=IF(C2="J",
IFERROR(LOOKUP((INT(F2/100)*60) + MOD(F2,100), Boys!C$2:C$100, Boys!A$2:A$100), 0),
IFERROR(LOOKUP((INT(F2/100)*60) + MOD(F2,100), Girls!C$2:C$100, Girls!A$2:A$100), 0)
)
Explanation:

• INT(F2/100) → Takes the minute part (e.g., 3 from 310).
• MOD(F2,100) → Takes the seconds part (e.g., 10 from 310).
• (Minutes × 60) + Seconds → Total seconds.
• Then LOOKUP into the helper column for a total of seconds to find the correct score!

View attachment 1767

Thx!!

 

[Bart Van Hout]

Thank you very much for all your help. It turns out the current formula is not entirely correct. Can you help me again, please?

The formula needs to approximate the time value. After converting to seconds, Excel looks for the value from column C. If the swimming time in seconds exactly matches a value from column C (worksheet boys or girls), then the corresponding score from column A should be given as the result. However, if the swimming time does not exactly match a time from the boys or girls worksheets, then the score from one row lower should be given.

Example row 1: Sterke Jef swims a time of 3'11". He should get a score of 19. However, according to the current formula, this time corresponds to a score of 20. Only when a boy swims faster or equal to 3'10", should he get 20.

A time between 3'10" and 3'14" corresponds to 20. When he swims slower, namely from 3'15", a boy scores 19. When he swims even slower, namely from 3'20", he scores only 18, and so on.

Example row 2: Marie swims a time of 4'16". She should get a score of 9. In the current formula, however, she already gets a 10, which is not correct.

Thank you very much for all your help!

 

[shamimarita]

Hello Bart Van Hout,

The previous formula using LOOKUP returns the highest matching score below or equal to the time, which causes incorrect scoring for cases like 3'11" or 4'16", as it doesn't shift down to the next row when there's no exact match.

Revised Formula for G2 (score):
=IF(C2="J",
IFERROR(
INDEX(Boys!A$2:A$100, MATCH((INT(F2/100)*60 + MOD(F2,100)), Boys!C$2:C$100, 0)),
INDEX(Boys!A$2:A$100, MATCH((INT(F2/100)*60 + MOD(F2,100)), Boys!C$2:C$100, 1) + 1)
),
IFERROR(
INDEX(Girls!A$2:A$100, MATCH((INT(F2/100)*60 + MOD(F2,100)), Girls!C$2:C$100, 0)),
INDEX(Girls!A$2:A$100, MATCH((INT(F2/100)*60 + MOD(F2,100)), Girls!C$2:C$100, 1) + 1)
)
)

How it works:

• INT(F2/100)*60 + MOD(F2,100) converts 3'11 to 191 seconds.
• MATCH(..., ..., 0) tries exact match.
• If it fails, MATCH(..., ..., 1) gives the row above the swimmer’s time → adding +1 gives the correct next score down.
• INDEX(..., row) fetches the score from column A in the "Boys" or "Girls" sheet.

Please make sure your helper column C in "Boys" and "Girls" sheets still has the total seconds using:
=LEFT(B2,FIND("'",B2)-1)*60 + MID(B2,FIND("'",B2)+1,2)

Let me know if you'd like a sample file with this implemented.