Modifying Excel Formula for Age Group Calculation

Other Questions Google Sheets
1

Hey everyone,

I’m working on a spreadsheet and I’m stuck with a formula. I need column I to show the age group I picked in the second row of column C for each race. My current formula works for most ages, but it’s not handling the ‘LIT’ group in Race 5 (row 68).

The formula also needs to work for ‘All Star’ races, like the one in row 243.

Here’s what I’ve got so far:

=ARRAYFORMULA(IF(LEN(D3:D866),vlookup(row(A3:866)+REGEXMATCH(C3:C866&E3:E866,'^RACE.+\d$'),SORT({row(H3:H866)*(REGEXMATCH(C3:C866&E3:E866,'\d+s')),iferror(REGEXEXTRACT(C3:C866&E3:E866,'\d+s'))}),2,2)&' '&D3:D866&'s',))

Any ideas on how to tweak this to include the ‘LIT’ group? Thanks in advance for your help!

2

Have you considered using a SWITCH function instead? It might simplify your formula and make it easier to handle special cases like ‘LIT’ and ‘All Star’. Here’s a basic structure:

=ARRAYFORMULA(
SWITCH(
TRUE,
REGEXMATCH(C3:C866, ‘LIT|All Star’), C3:C866,
REGEXMATCH(C3:C866&E3:E866, ‘^RACE.+\d$’), [your existing vlookup logic],
‘’
)
)

This approach allows you to define multiple conditions and their corresponding outputs. It’s more readable and maintainable in the long run. You might need to adjust the regex patterns to fit your exact data structure, but this should give you a good starting point.

3

hey mikezhang, I’ve dealt with similar stuff. try adding an extra IF statement to catch ‘LIT’ and ‘All Star’ before your main formula. something like:

=IF(OR(REGEXMATCH(C3:C866, ‘LIT|All Star’)), C3:C866, [your existing formula])

this shud handle those special cases. good luck!

4

I’ve encountered similar issues when working with age group calculations in Excel. In my experience the key is to modify your REGEXMATCH and REGEXEXTRACT functions to account for non-numeric age groups like ‘LIT’ and ‘All Star’.

One approach is to add a separate condition in your IF statement so that when the formula detects ‘LIT’ or ‘All Star’, it extracts these values directly. This could be implemented with a nested IF: first check for these special cases and then fall back on your existing numeric solution using vlookup and the regex functions.

You may need to fine-tune the regex patterns to align with your data structure, but this method should allow you to handle both numeric and non-numeric age groups consistently.