Multiple AND statements, long formula

PaperBoi5870

New Member
Joined
May 27, 2022
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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:
Excel Formula:
=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 categoryH: Best distanceI: Season of PRJ: Absolute timeRelative season best time
One of 5 categoriesOne of 6 distancesOne of 5 seasonsSome timeFormula dividing absolute time by season best time in age category on best distance during season of PR
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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 ?
 
Upvote 0
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
FGHIJK
1AgeAgeCategoryBestDistanceSeasonPRAbsTimerSBT
213.35Junior C500m2020-202140
SEBT Database
Cell Formulas
RangeFormula
G2G2=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
ABCDEFG
1Men Junior C
2500m1000m1500m3000m5000m10000m
32021-202237.5374.45115.26240.66417.05952.54
42020-202137.7072.11111.54244.47439.751048.42
52019-202036.7872.79111.03233.40413.18999.44
62018-201936.9074.14114.74239.42414.79904.46
72017-201836.3072.49114.79239.33410.101132.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?
 
Upvote 0
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 ?
 
Upvote 0
Solution
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.
 
Upvote 0
I think I've been able to create a simple formula with INDEX and MATCH that works!

FemaleSEBT_DatasetConcept.xlsx
FGHIJK
1AgeAgeCategoryBestDistanceSeasonPRAbsTimerSBT
219.00Neo-senior500m2021-202250.001.37
318.00Junior A500m2021-202242.001.10
417.00Junior A1000m2020-202187.001.13
516.00Junior B1500m2020-2021125.001.05
615.00Junior B1500m2019-2020142.001.16
714.00Junior C500m2018-201948.001.21
813.00Junior C500m2017-201843.001.10
SEBT Database
Cell Formulas
RangeFormula
G2:G8G2=INDEX($AH$2:$AH$6,MATCH(F2,$AG$2:$AG$6,1))
K2:K8K2=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
ABCDEFGHIJK
1Junior C 500mJunior C 1000mJunior C 1500mJunior C 3000mJunior C 5000mJunior B 500mJunior B 1000mJunior B 1500mJunior B 3000mJunior B 5000m
22021-202239.1477.94120.57259.98515.7638.6478.26120.71251.38446.74
32020-202139.5578.45125.32263.56481.2139.4278.46118.56250.18478.51
42019-202040.8680.36121.86256.60440.0339.7079.55122.01254.49446.60
52018-201939.8281.59122.50262.01455.8239.5478.81120.83255.76445.75
62017-201839.1877.86120.89259.25471.6739.0377.83120.8255.43453.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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top