Multiple AND statements, long formula

[PaperBoi5870]

I have a dataset with 500+ athletes, each with information on age category, best distance, season in which PR was achieved, and the absolute time of the PR. On another tab ("SBT") I have the overall season best time per age category on 6 distance over 5 seasons (total of 150 season best times). I want to divide the absolute time of PR by one of the 150 options to obtain relative season best time; based on age category, best distance, and season in which PR was achieved. For example, if AgeCat = Junior C and best distance = 500m and season = 2021-2022 then divide AbsTime by season best time on 500m for men in Cat1 during 2021-2022 season.

I've figured out a formula to achieve this, but doing it for all possible options results in a very long formula. The formula for Junior C would look like this:

=IFS(AND(G2="Junior C";H2="500m";I2="2021-2022");J2/SBT!$B$3;AND(G2="Junior C";H2="500m";I2="2020-2021");J2/SBT!$B$4;AND(G2="Junior C";H2="500m";I2="2019-2020");J2/SBT!$B$5;AND(G2="Junior C";H2="500m";I2="2018-2019");J2/SBT!$B$6;AND(G2="Junior C";H2="500m";I2="2017-2018");J2/SBT!$B$7)

(Note: I work in a Dutch version of Excel, so functions might translate different)

Is there a better/easier/quicker way to achieve this?

G: Age category	H: Best distance	I: Season of PR	J: Absolute time	Relative season best time
One of 5 categories	One of 6 distances	One of 5 seasons	Some time	Formula dividing absolute time by season best time in age category on best distance during season of PR

 

[Gerald Higgins]

Hi, welcome to the board.
What you want may well be do-able, but I just can't make out from your post what that is exactly.
Can you post a small sample of data - maybe the full data for a single athlete - and explain clearly what calculation you want to do ?

 

[PaperBoi5870]

Hi, welcome to the board.
What you want may well be do-able, but I just can't make out from your post what that is exactly.
Can you post a small sample of data - maybe the full data for a single athlete - and explain clearly what calculation you want to do ?

SEBT_DatasetConcept.xlsx
	F	G	H	I	J	K
1	Age	AgeCategory	BestDistance	SeasonPR	AbsTime	rSBT
2	13.35	Junior C	500m	2020-2021	40
SEBT Database

Cell Formulas
Range	Formula
G2	G2	=IFS(F2<=14,"Junior C",AND(F2>14,F2<=16),"Junior B",AND(F2>16,F2<=18),"Junior A",AND(F2>18,F2<=22),"Neo-senior",F2>22,"Senior")

This is what it would look like for one athlete. Each athlete has an age, putting them in a certain age category. There is also their best distance (BestDistance), a personal best time on this distance (AbsTime), and the season in which this PR was achieved (SeasonPR).

SEBT_DatasetConcept.xlsx
	A	B	C	D	E	F	G
1		Men Junior C
2		500m	1000m	1500m	3000m	5000m	10000m
3	2021-2022	37.53	74.45	115.26	240.66	417.05	952.54
4	2020-2021	37.70	72.11	111.54	244.47	439.75	1048.42
5	2019-2020	36.78	72.79	111.03	233.40	413.18	999.44
6	2018-2019	36.90	74.14	114.74	239.42	414.79	904.46
7	2017-2018	36.30	72.49	114.79	239.33	410.10	1132.48
SBT

And this is another sheet refering to all season best times for the Junior C category. This is just an example, but I also have these times for the other age categories.

What I want the formula to do is take the information on age category, best distance, and season of PR, and find the corresponding overall season best time in the second sheet. Then the AbsTime (in the example a time of 40 seconds) needs to be divided by that overall season best time.

I have been able to do that with the previously mentioned formula, however that string is only for the 500m in the Junior C category. To this I can add all the other distances, and do the same for the other categories, but this would create a very long formula. Maybe this can be done another way?

 

[Gerald Higgins]

OK, it seems like there is alot going on here.
Let's take it one or two steps at a time.

First, your formula that works out the Age Category - if that works for you, fine, but it looks very clunky and I wouldn't do it that way.
I would use a Lookup, or Index/Match combination, like this . . .
Set up a table somewhere, let's say in the range Y1:Z5, with this data in it . . .
Col Y........Col Z
0...............Junior C
14............Junior B
16............Junior A
18............Neo-Senior
22............Senior

Then in cell G2 . . .
=INDEX(N2:N6,MATCH(L2,M2:M6,1))

Doing it this way means that if the age categories ever change, it will be easier to manage that.

Second question - is it the case that each athlete only has ONE single record, like you've shown in your first table in post #3 ?

Third question - do you already have the final table in post #3, or do you need help producing that as well ?

 

[PaperBoi5870]

Thanks for the first part!

For the second question: each athlete will indeed have 1 single record

For the third question: if with final table you mean the table with all the season best times (SBT), I do have that one for all age categories and distances.

 

[PaperBoi5870]

I think I've been able to create a simple formula with INDEX and MATCH that works!

FemaleSEBT_DatasetConcept.xlsx
	F	G	H	I	J	K
1	Age	AgeCategory	BestDistance	SeasonPR	AbsTime	rSBT
2	19.00	Neo-senior	500m	2021-2022	50.00	1.37
3	18.00	Junior A	500m	2021-2022	42.00	1.10
4	17.00	Junior A	1000m	2020-2021	87.00	1.13
5	16.00	Junior B	1500m	2020-2021	125.00	1.05
6	15.00	Junior B	1500m	2019-2020	142.00	1.16
7	14.00	Junior C	500m	2018-2019	48.00	1.21
8	13.00	Junior C	500m	2017-2018	43.00	1.10
SEBT Database

Cell Formulas
Range	Formula
G2:G8	G2	=INDEX($AH$2:$AH$6,MATCH(F2,$AG$2:$AG$6,1))
K2:K8	K2	=J2/INDEX(SBT!$B$2:$Z$6,MATCH(I2,SBT!$A$2:$A$6,0),MATCH(G2&" "&H2,SBT!$B$1:$Z$1,0))

FemaleSEBT_DatasetConcept.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		Junior C 500m	Junior C 1000m	Junior C 1500m	Junior C 3000m	Junior C 5000m	Junior B 500m	Junior B 1000m	Junior B 1500m	Junior B 3000m	Junior B 5000m
2	2021-2022	39.14	77.94	120.57	259.98	515.76	38.64	78.26	120.71	251.38	446.74
3	2020-2021	39.55	78.45	125.32	263.56	481.21	39.42	78.46	118.56	250.18	478.51
4	2019-2020	40.86	80.36	121.86	256.60	440.03	39.70	79.55	122.01	254.49	446.60
5	2018-2019	39.82	81.59	122.50	262.01	455.82	39.54	78.81	120.83	255.76	445.75
6	2017-2018	39.18	77.86	120.89	259.25	471.67	39.03	77.83	120.8	255.43	453.34
SBT

The first table shows some mock data, the second table part of all season best times. The formula in K2 looks for the matching season in the second table, then for the matching AgeCat+Distance. The time in column J is then divided by the matching time in the second table, resulting in a relative season best time.

Thanks for the inspiration with INDEX and MATCH! Saved me a whole lot of work.

 

[Gerald Higgins]

OK good, thanks for letting us know, do you need any more help with this one ?

 

[PaperBoi5870]

No, I've got everything I need for now. Thanks again!