You may know an athlete's lifetime personal best, but how about their most recent one? The reason I look at the most recent is that although we know their lifetime best, they're not always going to be performing at that level. It could have been that they hit that two years ago in a competition, or it could have been that they hit it two months ago, in which case they might be slightly closer to it. So I look for the most recent so we can get a more up-to-date idea of where the athlete is.
To find this out, you are going to need to perform two steps. The first thing you want to do is look at the most recent date the athlete performed a lift. We can't look for the weight lifted first of all because the date is the major factor. We do this by using the MAXIFS function.
Breaking Down The MAXIFS Function
MAXIFS is usually used to identify the highest number in a range, but with dates, the MAX function will return the most recent.
max_range: The range in which your date can be found. This will be your date column.
criteria_range1: This is the set of cells that Excel will look at to see if the data matches your criteria. This will be the AthleteName for our example.
criteria1: This is simply the value that Excel is looking to match, so the name you have selected.
criteria_range2: The second set of cells that Excel will look at if you want to have a second criteria. This will be Exercise for our example.
criteria2: Again, simply the value you are looking to match in your criteria2 range, the exercise you have selected.
Theoretically, you can have as many criteria_ranges and criteria as you wish.
Using XLOOKUP to Return the Corresponding 1RM
Now that you have found the most recent date, you can use XLOOKUP to find the corresponding one rep max. If you want to learn the basics of XLOOKUP, you can view our blog post HERE. However, you now need to use three criteria in the function. You do this by using "&" to add more reference points to your function for each stage.
A Reminder of a Basic Function
Here is an example of a funtion that is using a single criteria.
lookup_value: F3
lookup_array: C3:C14
return_array: D3:D14
if_not_found: "No Data"
match_mode: 0
search_mode: 1
Now we are going to use the & symbol to add 2 more criteria and search ranges (yes I know, the formula seems to get really long now)!
lookup_value: H15&H3&H7
lookup_array: A2:A13&C2:C13&B2:B13
return_array: F2:F13
if_not_found: "No Data"
match_mode: 0
search_mode: 1
If you have made it this far, congratulations! I know it seems daunting, but mastering the XLOOKUP and learning how to use it with MAXIFS as a date finder can save you hours and hours of time in the long run!
תגובות