wykikitoon 20109 Posted February 21, 2023 Author Share Posted February 21, 2023 The first two are two separate tabs. One is the league, the others is where I have been imputing my events. So I put in a name of who has done the event and that's picked up on the league. Link to comment Share on other sites More sharing options...
The Fish 10838 Posted February 21, 2023 Share Posted February 21, 2023 Ok, make a data table with the names of the rider id, rank, rider number, people, gender, score, medal (unless this is a calculated field), then the events, etc. than create a pivot table off this for the men Gender in Filters Name as rows Events as columns Do the same for women. 1 Link to comment Share on other sites More sharing options...
wykikitoon 20109 Posted February 21, 2023 Author Share Posted February 21, 2023 1 hour ago, The Fish said: Ok, make a data table with the names of the rider id, rank, rider number, people, gender, score, medal (unless this is a calculated field), then the events, etc. than create a pivot table off this for the men Gender in Filters Name as rows Events as columns Do the same for women. Top man, will give it a go this eve and let thee know. Link to comment Share on other sites More sharing options...
The Fish 10838 Posted February 21, 2023 Share Posted February 21, 2023 6 minutes ago, wykikitoon said: Top man, will give it a go this eve and let thee know. Remember, when you're creating your data table; a) include as many columns as you can imagine needing. e.g. all of the events you'll schedule for the year, b) format the information as a table. a) it's easier to ignore empty fields than it is to add more and b) formatting as a table means the pivot table will include all the changes you make to the table easier than if it's a range. I was thinking it'd also be useful to have a separate table for the dates of the events. Purely as an index (or vlookup) reference. 1 Link to comment Share on other sites More sharing options...
wykikitoon 20109 Posted February 22, 2023 Author Share Posted February 22, 2023 @The Fish Just thinking. Is it worth having a table for each league member? There's 50 Riders. Then in that table have what each event they have done. Then that can be put into the overall table Link to comment Share on other sites More sharing options...
The Fish 10838 Posted February 22, 2023 Share Posted February 22, 2023 24 minutes ago, wykikitoon said: @The Fish Just thinking. Is it worth having a table for each league member? There's 50 Riders. Then in that table have what each event they have done. Then that can be put into the overall table You want as few steps as possible. Better to have one monster table that the pivot table summarises, than a multitude of smaller tables that you're referencing from. Say you wanted only the men. You'd need to instruct excel to look in each table, individually, for the gender of the rider. Instead have one table with all the riders, their genders, the events they've taken part in, etc. 1 Link to comment Share on other sites More sharing options...
Gemmill 44784 Posted February 22, 2023 Share Posted February 22, 2023 No one should be using vlookup now that xlookup exists. Re what the fish is saying. Make it a proper table (ctrl+t). There are loads of benefits of using proper excel tables. 1 Link to comment Share on other sites More sharing options...
wykikitoon 20109 Posted February 22, 2023 Author Share Posted February 22, 2023 I've actually simplfied it somewhat. Instead if using an XLOOPUP for some events I have just manually inputted the points awarded. At the end of the day its not going to have shit loads of Data in so why complicate it? Or am I just being a wimp The table thing is great to work with, then the pivot table off it is so simple. 1 Link to comment Share on other sites More sharing options...
Craig 6682 Posted March 10, 2023 Share Posted March 10, 2023 2 1 Link to comment Share on other sites More sharing options...
The Fish 10838 Posted March 10, 2023 Share Posted March 10, 2023 This touches me 2 Link to comment Share on other sites More sharing options...
Monkeys Fist 42356 Posted March 10, 2023 Share Posted March 10, 2023 Link to comment Share on other sites More sharing options...
Gemmill 44784 Posted March 10, 2023 Share Posted March 10, 2023 I'm finally getting to grips with DAX and have just built my first data model for power BI, complete with a bunch of DAX measures I wrote ALL BY MYSELF. Nothing you say can hurt me today. I'm going into the weekend on a MASSIVE high. 2 Link to comment Share on other sites More sharing options...
wykikitoon 20109 Posted March 10, 2023 Author Share Posted March 10, 2023 9 hours ago, The Fish said: This touches me Honestly I've used excel for a couple of decades but very very basic. Ive got loads of spreadsheets for my calcs. When you mentioned making some of my stuff a table I've gone back and redone a lot of my stuff and it's soooooo good. What's your. PayPal 😂 1 Link to comment Share on other sites More sharing options...
wykikitoon 20109 Posted May 3, 2023 Author Share Posted May 3, 2023 Righto A new one. I have got a table again and this time I want calculate some things. One of the value sets is in letter formats A, B, B+ C etc. Using the table I can filter thow only A values, etc. I want to do that and then it give me the total number of these. How the FUCK do I do that? Link to comment Share on other sites More sharing options...
Christmas Tree 4725 Posted May 3, 2023 Share Posted May 3, 2023 31 minutes ago, wykikitoon said: Righto A new one. I have got a table again and this time I want calculate some things. One of the value sets is in letter formats A, B, B+ C etc. Using the table I can filter thow only A values, etc. I want to do that and then it give me the total number of these. How the FUCK do I do that? Can’t you just sort by the letter column and then autosum the result Link to comment Share on other sites More sharing options...
wykikitoon 20109 Posted May 3, 2023 Author Share Posted May 3, 2023 Link to comment Share on other sites More sharing options...
Gemmill 44784 Posted May 3, 2023 Share Posted May 3, 2023 @wykikitoon Use the Subtotal function. It allows you to count only visible cells so you could filter on A and it would just count those. There's a Guide here. https://exceljet.net/formulas/count-visible-rows-in-a-filtered-list Link to comment Share on other sites More sharing options...
Gemmill 44784 Posted May 3, 2023 Share Posted May 3, 2023 Alternative would be a COUNTIF for all the different unique values in the table. You could create a drop down for the value you want to look at and link that cell to the formula if you were anything like as cool as me. That way you could just write the formula once and have the score you're looking for be dynamic. 1 Link to comment Share on other sites More sharing options...
Gemmill 44784 Posted May 3, 2023 Share Posted May 3, 2023 Use the UNIQUE function to create your list of scores. That way you won't have to keep updating the list every time a new score gets added. Link to comment Share on other sites More sharing options...
wykikitoon 20109 Posted May 3, 2023 Author Share Posted May 3, 2023 Man, You're just too sexy! Link to comment Share on other sites More sharing options...
Christmas Tree 4725 Posted May 3, 2023 Share Posted May 3, 2023 3 minutes ago, wykikitoon said: Man, You're just too sexy! Cheers 4 Link to comment Share on other sites More sharing options...
wykikitoon 20109 Posted May 3, 2023 Author Share Posted May 3, 2023 Right another one @Gemmill I want to count how many instances of a criteria between a range. So in the highlighted column I want to count how many I have from 17:00 - 17:30. That it would be a total of 3. Then the same from 17:35 - 18:00 That selection it would be a total of 9 Link to comment Share on other sites More sharing options...
Howmanheyman 33094 Posted May 3, 2023 Share Posted May 3, 2023 13 minutes ago, wykikitoon said: Right another one @Gemmill I want to count how many instances of a criteria between a range. So in the highlighted column I want to count how many I have from 17:00 - 17:30. That it would be a total of 3. Then the same from 17:35 - 18:00 That selection it would be a total of 9 I fucking hope you're not trying a time-in-motion calculation to shaft someone, Wykiki? You'll go right down in my estimation if you are, right down.... 4 Link to comment Share on other sites More sharing options...
The Fish 10838 Posted May 3, 2023 Share Posted May 3, 2023 25 minutes ago, wykikitoon said: Right another one @Gemmill I want to count how many instances of a criteria between a range. So in the highlighted column I want to count how many I have from 17:00 - 17:30. That it would be a total of 3. Then the same from 17:35 - 18:00 That selection it would be a total of 9 One idea is to have a reference table =COUNTIFS($E$3:$E$100,">"&G4,$E$3:$E$100,"<"&G3) Where E is the column with the times. 1 Link to comment Share on other sites More sharing options...
wykikitoon 20109 Posted May 3, 2023 Author Share Posted May 3, 2023 51 minutes ago, Howmanheyman said: I fucking hope you're not trying a time-in-motion calculation to shaft someone, Wykiki? You'll go right down in my estimation if you are, right down.... Its for the cycle club. We offer several paced rides setting off at different times. Just trying to get some stats on it. Because G-Dogg and Fishman know stats are king. 2 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now