You won't always be looking for your athletes' personal bests; you may also want to see how they've improved. In this post, I explain how to find multiple data points for a single athlete on a single exercise. Basically, it means getting their whole history for a certain KPI.
To achieve this, all we need to do is ask Excel to search a particular table (you can find more information about tables here), and it will locate any data that corresponds to our athlete name and the exercise that we have chosen. We will also need to tell Excel what to do if there are no data matches, but since this is a relatively simple step, we can leave it until last.
Before we break down the formula, you are going to need to create a space for your data to be shown. To do this, simply create a handful of headers that relate to the information you want to show.
Breaking Down the =IF Formula
logical_test: This is the question that you are going to ask Excel. It must be TRUE or FALSE. You can use mathematical symbols to ask your question:
Does X equal Y: =IF(X=Y
Is X greater than Y: =IF(X>Y
Is X less than Y: =IF(X=Y
Is X greater than or equal to Y: =IF(X>=Y
Is X less than or equal to Y: =IF(X<=Y
You can use specific cell references, ranges or columns from named tables in you logical_test. In addition to this, you can ask Excel to ask multiple questions by using the "&" symbol.
value_if_true: The value that you want to be returned when logical_test = TRUE. If more than on match is found, Excel will return multiple values (this is how we return the entire lifting history).
value_if_false - [optional]: The value that you want to be returned when logical_test = False. This can be a certain cell value, or a string of text (remember to enclose your text with " " when writing your formula)
A Practical Example of =IF
In this example the breakdown of my formula looks like this.
logical_test: Data3[Athlete] "&" Data3[Exercise] = H3 "&" H&7
value_if_true: Data3[Date]
value_if_false - [optional]: ""
In this example, I have a table called "Data3". My formula asks Excel to find rows where Data3[athlete] column matches H3 (the athlete name I have selected) AND Data3[Exercise] column matches H7 (the exercise I have selected). If there are matches, it returns the corresponding values from the Data3[Date] Column. If the row is NOT a match, it return a blank.
From here, I simply perform this function for each column of information I want to find. To do so, I simply change the Data3[Date] column to Data3[Weight] and so on.
I fully appreciate that this may all appear very confusing! However, taking a little time to get used to and understand each formula will open up so many options and save you so much time.
To learn how to create a full Multi-Athlete Lift Log book, check out our Free Course Here.
Comments