3 жыл бұрын
3 жыл бұрын
Пікірлер
@tannerlaske1911 5 күн бұрын
Hi Adam, I am working with a very large data set and I am following along correctly but my date ranges do not seem to be working. Any way you can help me out with this? Thanks
@AdamVirgile34 5 күн бұрын
Hi Tanner. That's a great observation. Dates are finicky. Typically, this occurs when the data in the database is not actually being stored as dates (they are stored as text or some other format). Obviously I'm not quite sure what's going on in your specific situation, but I hope this helps. Thank you!
@tannerlaske1911 5 күн бұрын
@@AdamVirgile34 Hi Adam, thanks for the response. When I was editing the data set and used the filter function I did notice the dates came out as random numbers that excel spits out. I changed that by formatting the cells back to dates.. not sure if that was the right thing to do. But once I did change the cell format so it does look like a regular date (11/6/24) it still was not working
@AdamVirgile34 5 күн бұрын
@@tannerlaske1911 thank you for the update! That is helpful. With your initial description: "my date ranges do not seem to be working", it is difficult to pinpoint what the issue may be. I would use the DATE or ISDATE function to confirm that the values are, in fact, dates. I would then ensure that, if your formula is not as simple as a FILTER for dates only, that other areas within the formula are investigated. I hope this helps. Thank you.
@aaangel30 9 күн бұрын
Great video! THANK YOU! Pain, especially chronic pain, is very difficult to communicate, and I'm very excited about using this kind of tool to more effectively document what happens and how it affects daily functioning throughout the month (or months!) between my doctor appointments. There's two things I'd like to incorporate, and I'd appreciate your input on them. 1. TYPE of pain, such as burning, numb, stabbing, etc. 2. Date range Again, I want to express my appreciation for your time and detailed instructions. Oh! ALSO, I'm so glad you didn't use some generic computer generated voice to walk us through; I enjoyed your candid video, and you even kept my attention during the tedious necessities that one's got to do to get it done! Happy Wishes ⭐
@AdamVirgile34 5 күн бұрын
Hi there! These are fantastic thoughts. Thank you for sharing them. Depending on the situation, having different sensations displayed on the body map could definitely be helpful (such as burning, numb, etc.). I also think that being able to view how this information changes over time would be helpful. In other words, I agree! Thank you for watching.
@vszly 18 күн бұрын
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.
@AdamVirgile34 13 күн бұрын
That is a great question. I would include logic in the formula to ignore blank values (using IF criteria). I hope this helps. Thank you!
@vszly 13 күн бұрын
yeah i figured it out
@fernando5166 20 күн бұрын
Thank you very much for the video, can you share the file to practice?
@AdamVirgile34 13 күн бұрын
Hi there! I cannot, but feel free to copy the structure and contents of what you see into your own file. I hope this helps.
@stevenwoolery 25 күн бұрын
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!
@AdamVirgile34 13 күн бұрын
Hi Steven. My apologies for the delay. Were you able to get this sorted out? Your troubleshooting sequence is spot-on - the issue is likely due to the data (or lack thereof) in your TestingData sheet. The error may occur because there is no data for one or more elements of your formula criteria. In any case, I would definitely check the data source first and identify the data points that match your search criteria. I hope this helps. Again, my apologies for the delay!
@superbar 28 күн бұрын
this doesn't work anymore, is there an updated way to do it?
@AdamVirgile34 27 күн бұрын
Hi! I have tested these functions recently and they continue to work on my end. Could you give me an example? Thank you!
@hectorazul7462 28 күн бұрын
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!
@AdamVirgile34 27 күн бұрын
I do, but it is not free: adamvirgile.com/product/body-map-dashboard/
@hectorazul7462 26 күн бұрын
@AdamVirgile34 How much is the spreadsheet? And can the value be changed or additional data collected?
@hectorazul7462 26 күн бұрын
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.
@AdamVirgile34 26 күн бұрын
@@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!
@StrengthandConditioningScience Ай бұрын
Excellent tutorial. Revisit this at least once per year!
@AdamVirgile34 27 күн бұрын
Thank you, my friend! I appreciate you!
@athleticvision2527 Ай бұрын
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!
@AdamVirgile34 Ай бұрын
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!
@athleticvision2527 Ай бұрын
YOYO SCORE,40M DASH,PRO AGILITY,MB THROW & CHEST PASS
@Delaney001 Ай бұрын
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
@AdamVirgile34 Ай бұрын
Of course! I'm glad it was helpful.
@giuliodambrosio9038 2 ай бұрын
How do I include both the severity level and the injury type?
@aaangel30 9 күн бұрын
Curious what you mean about "injury type"... Would you have to add more data such as numb, tingling, sprain, strain, etc.?
@sipsiprfl 2 ай бұрын
Great tutorial ❤..finally found a channel that explains clearly. Thank you you deserve a +
@AdamVirgile34 2 ай бұрын
Thank you for your kind words!
@eddietorres2622 2 ай бұрын
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
@AdamVirgile34 2 ай бұрын
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!
@Lbuck3284 2 ай бұрын
Hey Adam, do you have a way to compare scores against norms like for ROM and minimum thresholds?
@AdamVirgile34 2 ай бұрын
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.
@Lbuck3284 2 ай бұрын
@@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?
@satwikpal9258 2 ай бұрын
Thanks bro. You made the most accurate video for the alternate to unique function across the whole youtube.
@AdamVirgile34 2 ай бұрын
Thank you. I appreciate your kind words!
@Lbuck3284 3 ай бұрын
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.
@AdamVirgile34 2 ай бұрын
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.
@yuvraj9281 3 ай бұрын
Thanks
@LawnEnthusiast 3 ай бұрын
Hey Adam, great content, is there a way to filter these leaderboards for athlete age? So i can see my top 10 athletes aged 16, 17 and 18 individually?
@AdamVirgile34 2 ай бұрын
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.
@yasseralqerfan5077 3 ай бұрын
رائع
@differentaim 3 ай бұрын
Is there anyway you could share this with me?
@omkarmane5448 4 ай бұрын
Very Helpful
@arindambhattacharya7848 4 ай бұрын
The result is definitely not the worth for the time invested
@AdamVirgile34 4 ай бұрын
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).
@video42please 4 ай бұрын
This is awesome! Is there a download of this completed excel file, please?
@areebbunny66 4 ай бұрын
Excellent bro❤
@lachieatkinson801 5 ай бұрын
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?
@AdamVirgile34 5 ай бұрын
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.
@performancepodiatrist 5 ай бұрын
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?
@AdamVirgile34 5 ай бұрын
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!
@muhammadfadhlirizwardi5300 5 ай бұрын
Detail explanation, thanks man! This solved my work attendance sheet. What if I want to combine Good Guys and Bad Guys in 1 column?
@AdamVirgile34 5 ай бұрын
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!
@gerbherb8215 7 ай бұрын
Quite a messy instruction that is. I hope this isn't your best one 🙂
@gerbherb8215 7 ай бұрын
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!
@sinmiloluwa7691 7 ай бұрын
Great content. Do you have the workbook downloadable?
@AdamVirgile34 7 ай бұрын
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/
@GopikaDhas 7 ай бұрын
Bro Formula is confusing, Help me understand
@surennair2926 8 ай бұрын
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
@AdamVirgile34 8 ай бұрын
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.
@lukealford4560 9 ай бұрын
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.
@SuperKillbill1234 9 ай бұрын
great video, ty
@AdamVirgile34 9 ай бұрын
Thank you, Bill!
@kingshonor1954 9 ай бұрын
I love your work. I am working on using forms and building a Dashboard could use a little help.
@AdamVirgile34 9 ай бұрын
Thank you for your kind words! Keep looking for free online resources and I think you will be able to accomplish your goals.
@chickenking8 9 ай бұрын
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.
@AdamVirgile34 9 ай бұрын
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: kzbin.info/www/bejne/q4LHl5aYrt-Kd5osi=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!
@nelaxhighlights3128 9 ай бұрын
Such a great idea! Using this easy to follow tutorial I was able to put together a shot chart for the lacrosse team I coach!
@AdamVirgile34 9 ай бұрын
This made my day! Great job, coach!
@adilmirza4074 10 ай бұрын
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!
@AdamVirgile34 10 ай бұрын
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!
@adilmirza4074 10 ай бұрын
@@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!
@AdamVirgile34 10 ай бұрын
@@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.
@adilmirza4074 10 ай бұрын
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!!
@AdamVirgile34 10 ай бұрын
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!
@SharonRaju-xd5dq 10 ай бұрын
which version of excel are you uszing as i am getting an error while adding budget(mln)column the orginal budget is changing into #Name please help me
@tarunx2 10 ай бұрын
Hiw do I input this in Data Validation list?
@AdamVirgile34 10 ай бұрын
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!
@leoconzutti764 10 ай бұрын
When I add the if error to the formula it changes the dob into a raw number like 37490
@AdamVirgile34 10 ай бұрын
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!
@kingshonor1954 10 ай бұрын
Thank you, I never comment but you have saved me so much time truly thank you.
@AdamVirgile34 10 ай бұрын
Thank you for your kind words!
@user-te9yg8nl5h 11 ай бұрын
Nice video man quite helpful it would be even if you could make a short regarding the index-match formula and explain the formula you used briefly
@AdamVirgile34 11 ай бұрын
Thank you for your kind words, Amaan! I have a few videos on INDEX-MATCH with Google Sheets. Here is one: kzbin.info/www/bejne/m4HOfa2geJdkfrMsi=-BeGlqEzQykqmegh
@Ubbnockshuss 11 ай бұрын
Thank you for putting this video together!
@LeonLedgister 11 ай бұрын
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 KZbin community.
@AdamVirgile34 11 ай бұрын
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!
@BlakesFULLofFIT 11 ай бұрын
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).
@AdamVirgile34 11 ай бұрын
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!
@mate28 11 ай бұрын
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!!!
@AdamVirgile34 11 ай бұрын
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!
@LateNightRon Жыл бұрын
this is fantastic. You can always learn some excel goodness.
@AdamVirgile34 Жыл бұрын
Thank you!