PaperBoi5870
New Member
- Joined
- May 27, 2022
- Messages
- 5
- Office Version
- 2019
- Platform
- 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:
(Note: I work in a Dutch version of Excel, so functions might translate different)
Is there a better/easier/quicker way to achieve this?
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)
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 |