On this channel, you will find resources aimed to facilitate improved athletic performance through enhanced sports science applications, including tutorials in Microsoft Excel and Google Sheets. Whether you're part of an athletic performance staff (sports scientists, athletic trainers, and sport and strength coaches) or a casual Excel or Google Sheets user, the tutorial videos on this channel will help you take your game to the next level.
Very useful video! I have one question though: what to do if I have empty cells in the source column? When I use your formula, it lists the different values, but there is a 0 in the list because of the empty cells.
Hey Adam I keep getting an error with the Position Average formula it says "Evaluation of funtcion AVERAGEIFS caused a divide by zero error" I have triple checked that it is the same as your however it still does not work. I will say could it potentially be because the data I have in the TestingData sheet? It is very much random and I thought maybe that could be the case. Thank you!
I am currently attempting to build this myself.... I am a Safety Technician who is passionate about Ergonomics, this tool/ Spreadsheet will be God sent if i manage to duplicate it. Its a long shot, but do you have a downloadable example online?... If there is a ready to use sheet I'd love to get it. Thank you!
Awesome 👌 just saw the link. I am very interested in purchasing it. Would it affects the data if titles such as "Athletes" are renamed or the overall value of the pain level is changed? Or would you custom fit it?.... if so, how much would it be.
@@hectorazul7462 the file on the website is literally the Excel file that is produced in this video. It will operate as described in this video. If your data format differs from what is described in this video, it will not be a great option for you. I would encourage you to watch the video to understand how the body map works prior to making a decision. Thank you!
Hello Adam, I done all the process.But,In my chartdata Date & Event Id .[=sort(UNIQUE(FILTER({'Testing Data'!B:B,'Testing Data'!H:H},'Testing Data'!A:A=A4,'Testing Data'!A:A<>""),1,false))] after this formula . Only one date and event only came there,i don't know what happent.Can you please guide me. Thank you so much!
Hi there! There could be many reasons why this is happening. To recap, what you are telling the formula to do is: - Search through all of the data that is in Testing Data B:B and Testing Data H:H whereby the data in Testing Data A:A equals the contents in the cell A4. - Only include unique combinations of Testing Data B:B and Testing Data H:H. - Sort the unique combinations that you found. I would first check your data sources to confirm that you expect a different result from what you have generated. I would then consider whether or not the formula is an appropriate one for your specific needs. I hope this helps. Thank you!
Amazing tutorial, thanks for sharing. I couldnt figure out how to differentiate goals and misses, but adding the additional columns in the table simplified it so much. Thanks
Great instruction video. I'm attempting to utilize this video for my own body map. However, my body parts are listed within a row of other information. Does the order column make it break this process
Thank you for your kind words! Unfortunately, the organization of the data will dictate the setup and formulas used. On my website, I have a tutorial for long and wide data formats (e.g., it seems like yours is long, and mine is wide in this video) alongside a free body map dashboard in Google Sheets. I believe it requires a paid membership though. In case you're interested in checking: adamvirgile.com/interactive-body-map-dashboard-tutorial/ I hope this helps. Thank you!
Unfortunately, I have not filmed a video on way(s) to develop a scoring system using user-defined benchmarks. There is a lot of nuance to this question, which is great, because there is a lot of flexibility in how you may decide to develop the system based around your specific criteria. I apologize that this answer is not very helpful.
Yea this one is not working for me. I'm getting a #value error and having a hard time figuring out why. works fine until I had the Index/Match function.
My apologies. I know it is a complicated formula. My suggestion is to create an environment that mimics the one I use in the video and apply the formula to that. Then, look for differences between what you've done and what you're trying to do in your sheet. I hope this helps.
Hi! Thank you for your kind words. There certainly is. There are a few steps: 1. Have a cell whereby you enter the age of the athletes of interest. You can also enter the specific age of interest directly into the formula below - this will make the leaderboard more dynamic. Let's say the cell you choose for the age entry is cell A1. 2. Use the FILTER function inside of the SORTN function to filter the data for only what you type into cell A1. Let's say that your ages are stored in column A. - Cell for age entry = A1 - Ages stored in column A Using my example at 7:24 in the video: =SORTN(FILTER({A:A,B:B,C:C},A:A=A1),10,FALSE,3,FALSE) I hope this helps! Thank you.
Hey Adam, just a quick question. When changing athete name, my HeadshotLookup reverts to a white background without actually changing the cell fill from "no fill". Any fixes?
Hi Lachie. Are your images .jpg or .png? I'm not sure whether having the images be .png (with transparent background) will maintain the fill color of the cell in which the image resides, but it is worth a shot, perhaps.
Hi Adam. For metrics where a lower value is preferred (such as speed, agility, body fat%), is there a better way to graphically depict it so that for the best score the bar appears higher, but the y axis values are in reverse?
Hi Jackson. That's a great question. The best approach (IMO) is to normalize your data. Perhaps the most common normalization methods in the "high performance" space are Z-Socre and Percentile normalizations. If you decide to create Z-Scores and/or Percentiles for your data, you can invert the values for the metrics whereby "lower is better." Z-Score: Multiple the Z-Score by -1 for these metrics. Percentile: Subtract the percentile from 1 for these metrics (e.g., 1-percentile). Another benefit of doing this is that you can display all of your data on the same scale. In other words, you can display data for all metrics on the same chart and maintain high-quality data interpretation potential. I hope this helps. Thank you!
Hi Muhammad. That's a great question! All you would need to do is add an OR statement within the IF statement. In the OR statement, you would note all conditions that you would want to include in the list. Per your question, if you wanted to get the Good Guys and Bad Guys in the same column, the code would look like this: =IFERROR(INDEX(Table1[Athlete Name],MATCH(0,IF(OR($M$2=Table1[Team],$N$2=Table1[Team]),COUNTIF($M$2:M2,Table1[Athlete Name])),0)),"") $N$2 is the word combination "Bad Guys" in this example. I have not tested the code because I don't have the file used to create the video, but I hope this helps and makes sense. Thank you!
You get the blank in the good guys list because you use IF. Use FILTER instead: =SORT(UNIQUE(FILTER(Table1[athlete];Table1[team]="good guys"))). And this one for good guys and 34.4: =SORT(UNIQUE(FILTER(Table1[athlete];(Table1[team]="good guys")*(Table1[peak spead]=34.4)))). And let's not go back to old CSE-formulas. The horror!
Hi I have used =counta(unique) which gave me unique value. But I want to add another criteria to this. If another column has yes/no I want to know how to get unique value if it’s yes. How would i do this
Hi there! You might decide to include FILTER to specify your criteria. Imagine your "yes/no" is in Column C and your values are in Column B. =COUNT(UNIQUE(FILTER(B1:B10,C1:C10="yes"))) This formula should give you a count of the unique values in column B whereby the value is also "yes" in column C. I hope this helps.
This comes 3 years after the vid was made so I'm not sure my observations matter. As I follow along I noticed my outputs weren't the same as yours following the formula build. Some people were showing as not present in my build, and on your screen they were present. After a few minutes of going back and forth I figured out that in the download file the code name "sailor jerry" has a space at the end in the Daily Entry tab, but it didn't when we copy+pasted the code names from the Player Profiles tab to the Data Viz tab. Deleting the "hanging space" from the Daily Entry table corrected the issue. Some other codenames (Jason Faunt and stacy's mom) corrected after I retyped the names in the Daily Entry table. ANYWAY, that's just a piece of info I wanted to share in case anyone else is discovering these AWESOME videos years down the road like me. Thanks Adam! I'm enjoying this.
Good stuff. Can you do a tutorial that shows how to pull in specific data from multiple columns, if I change lets say like a team name. So basically I have a formula setup, but I'm having to manually enter in that data. I want to be able to just enter in like Boston, and it will pull in the criteria I need from the table/column I direct it to.
Thanks for your kind words! Ultimately, your ability to do this will come down to how the web pages are organized (by the website's developers). I will try to give a use case without a tutorial. At ~2 minutes in to this tutorial, I get data from a website: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-uQdfcbyzWA8.htmlsi=PuL7CHIaS27FtYLy If I wanted to change a year in a cell in my Google Sheet and have the data for that year come along for the ride, I would separate parameterize the URL. Here is the starting URL: "www.basketball-reference.com/leagues/NBA_2021_totals.html" We could separate this into: "www.basketball-reference.com/leagues/NBA_" AND "_totals.html" THEN Let's say in cell A1, we could have a spot for us to insert the year. The URL in the formula would end up looking something like: "www.basketball-reference.com/leagues/NBA_"&A1&"_totals.html" I hope this example helps a bit. Thank you!
Hi Adam I keep getting an error saying ‘array arguments to averages are of different size’. Why is this? My formula is exactly the same as yours! Hope you can help!
My guess is that there are differences between range sizes within the formula. For example, if you have a formula that includes something like "A:A" and "B1:B" or "C3:C20", the formula may not work (depending on other contextual information pertaining to the formula). There are many possible causes, but this one is the most likely in my opinion. I hope this helps. Thank you!
@@AdamVirgile34 thanks for the reply Adam! Would it be possible for me to send over my document so u can have a look at it please? Average works instead of averageifs so I’m really confused tbh!
@@adilmirza4074 I love your enthusiasm! Unfortunately, I cannot take a look at your document, but I trust that you will find the solutions you need using free online resources. You got this! Thank you.
Hi Adam, thanks for this it has been so useful! I need some help! I have created this for the whole of our academy so as you can imagine we have a lot of athletes. But on the main ‘player profile’ sheet I want to create a slicer than only shows the players for the teams that I have selected. For example, if I only want to see U21 players on the drop down I have a slicer that allows me to filter only U21s and deselect the rest of the academy teams. hope that makes sense? i hope you can help!!
Hi there! I would not use slicers. You will want to implement logic that likely uses the FILTER function with your data to see the data for only a group of athletes (or multiple groups of athletes) that you specify. I would create a drop-down list of teams/groups and use logic with the FILTER function to say "only show me the data with the group I pick in cell [A1], but if cell [A1] is blank, show me data for all athletes in my database (who fall under the other FILTER criteria I specified in the formula). I hope this helps. Thank you!
Hi Leo. Yes, that can/will happen in this instance. You can format that cell differently if you'd like (e.g., Format --> "Short Date"). I hope this helps. Thank you!
Thank you for your kind words, Amaan! I have a few videos on INDEX-MATCH with Google Sheets. Here is one: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ePkLzjC21HQ.htmlsi=-BeGlqEzQykqmegh
Adam, Made it through in 4 weekends and I must say thank you for your willingness to share your knowledge with those that you've never met. I'm going to modify mine a little to fit my baseball needs, but I included everything because "It all makes sense!" Thanks again for sharing, and I look forward to getting into some of the other projects that you've shared with the RU-vid community.
Hi Leon. Wow, you cruised through! Huge congrats. And thank you so much for the kind words. I'm glad that you'll be modifying yours to optimize it for your needs - words cannot express how much joy this brings me. Thank you for your support and for paying such great attention that you can take this framework to support your specific goals!
Greatly appreciated sir! This unlocks a knowledge gap of mine as I build out my own client training worksheet. Really appreciate you specificity and real life applications in the training field. Any formula pointers to use if I want the date column to be dynamic also so I can populate the last time a client had data in a respective field? Often times I don’t know the date of the data I’m seeking but want to see where the data for said exercise (or sleep quality, RPE, etc…) was during the clients last visit (or last 3 visits).
Hi Blake. Thank you for your kind words, and this is a great question. The first step I take is finding the dates with the most recent data. You can use either MAXIFS or MAX(FILTER( to get the maximum date for the client of interest. Or, if you want the last 3 sessions, you could use SORTN(FILTER( to get highest X number of dates for that client. I hope this helps!
Hi Adam, Thanks for the video, brilliant. Could you give me a hint on how to modify the formula if there are 2 workouts in a day, so 2 sRPE? Because at the moment it is taken by both. Thanks!!!
Hi Mate, great question! It depends on what you want to show. If total sRPE, you could use SUMIFS instead of AVERAGEIFS. Or if you wanted to show the highest sRPE, you could use MAXIFS. I hope this helps!
