Видео

Thank you for your kind words!

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.

@@AdamVirgile34 do you have one using if(And()) functions to have the cell report a "pass" or "fail" if it falls within a given criteria range?

Thank you. I appreciate your kind words!

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.

I agree. In fact, there are far more efficient methods to complete these tasks in newer versions of Excel (this video is from nearly 5 years ago).

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 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!

Thank you for reaching out! It's a pleasure to e-meet you. I offer the workbook on my website: adamvirgile.com/product/kpi-monitoring-dashboard/

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.

Thank you, Bill!

Thank you for your kind words! Keep looking for free online resources and I think you will be able to accomplish your goals.

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: ruclips.net/video/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!

This made my day! Great job, coach!

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 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 Tarun. I don't fully understand your question. If you have time to elaborate on the task, I might be able to offer better support. 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!

Thank you for your kind words, Amaan! I have a few videos on INDEX-MATCH with Google Sheets. Here is one: ruclips.net/video/ePkLzjC21HQ/видео.htmlsi=-BeGlqEzQykqmegh

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!

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 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!

Thank you!

It is not downloadable, unfortunately.

not even for sale? @@AdamVirgile34

That's a great question. There are a variety of ways to simulate/impute data. Monte Carlo is one method. If you were to use Monte Carlo simulation, you would likely not be executing it within Google Sheets or Excel. I hope this helps!

So you're telling me there are other ways to do it? @@AdamVirgile34

Fantastic! Thank you for watching.

Hi Brady. It's certainly possible. The means to accomplish the task will depend on what the dataset looks like.

That's a great question, Sydney. Unfortunately, I don't have a simple solution to this problem in respect to this video series. There are a variety of strategies to incorporate that framework, but none that I can think of that would be simple to explain and align with what has already been completed in this video series.

Does this question relate to this video? In short, it depends on your goal. You can use SUMIFS or SUM + FILTER to get totals for a given date-athlete combination. I hope this helps.

@@AdamVirgile34 Apologies, I don't think it was this video specifically, I think it may have been ruclips.net/video/uQdfcbyzWA8/видео.html I am trying to build a GPS Specific dashboard, where the raw data is divided into splits for each athlete, however when I have filtered by date, the splits do not add together based on athlete name. I have looked at sumifs, but have not been able to find a way to get it functioning as yet...... Is good to know I was kind of on the right track though...... Many thanks mate... :)

@@user-mo7hc6cr7t that's fantastic! You're on the right path. It's hard to guide without understanding the general goal(s), but I imagine that using SUMIFS or SUM + FILTER will help get you the results you want if you're attempting to aggregate the information per athlete per date.

For cells with check boxes, there is no need to consider whether the cell is blank (the value will only be TRUE or FALSE). However, and especially with extensive formulas, there may be other parts of the formula where you may need to consider whether a cell (or cells) is blank. I hope this helps! Thank you.

Sorry, I think didn't explain myself well enough. The checbox is FALSE in the dashboard, consequently in the chartdata, below "Selected event", the cell is blank. That's why in the Date of Athlete shows "#N/A" and says: Error: No coincidences are found on the function FILTER (bad translation 😅). @@AdamVirgile34

@@albertolinan11 it was probably my issue interpreting what you were saying - my apologies! In this case, yes, the N/A is likely a result of that cell being blank. In this case, I would add in the IF("Selected event" cell = "", ""...) if you'd like the cell to be blank with that cell is blank. If you share your sheet with me and describe what you're trying to accomplish, I'm happy to take a look - adding IFs can be done inside of the FILTER function, but these are sometimes difficult for people to set up.

Sounds great, it is a little complex since there are two cells that we would incorporate on those "IF"s functions. I will give it a try and will let you know! THANKS A LOT!! @@AdamVirgile34

Totally worked it! Thanks a lot again! Great job Adam! 💪🏼💪🏼

Thank you, Brett! I appreciate you.

Hi James. There are many reasons why this may be happening. I don't have enough information to help you directly, unfortunately. Have you solved the issue yet?

Hi Tracie. That's a great question. I would add two additional columns to the database to identify previous winners. Let's say you have a column called "previous winners", and in that column you have 1s and 0s. For IDs that have previously been drawn as winners, you put in a "1", and for IDs that have not won previously, you put in a "0." I would then write a formula to get a list of IDs that exclude previous winners. Let's called this column "non-winner IDs". We would use IF([previous winners]=1,"",[previous winners]) to generate a list of IDs that exclude the previous winners. In your RANDBETWEEN function, you can replace the original ID column with the "non-winner IDs" column. That's the quickest solution I can think of. I hope this helps! Thank you.