Jump to content

Excel People


wykikitoon
 Share

Recommended Posts

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. 

 

  • Like 1
Link to comment
Share on other sites

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

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.

  • Like 1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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 :lol: 

The table thing is great to work with, then the pivot table off it is so simple.

WLTable1.png

WLTable2.png

  • Like 1
Link to comment
Share on other sites

  • 3 weeks later...

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. 

  • Haha 2
Link to comment
Share on other sites

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 😂

  • Haha 1
Link to comment
Share on other sites

  • 1 month later...

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?

 

 

2023-05-03 11_46_33-Window.png

Link to comment
Share on other sites

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?

 

 

2023-05-03 11_46_33-Window.png


Can’t you just sort by the letter column and then autosum the result

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

 



 

2023-05-03 12_57_36-Window.png

Link to comment
Share on other sites

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

 



 

2023-05-03 12_57_36-Window.png

 

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

 

keeganrant_450x299.jpg?quality=90&strip=

 

  • Haha 4
Link to comment
Share on other sites

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

 



 

2023-05-03 12_57_36-Window.png

 

One idea is to have a reference table

image.png.dd7e05b8c1a4ae5aad2424c4d054c0c4.png

 

=COUNTIFS($E$3:$E$100,">"&G4,$E$3:$E$100,"<"&G3)

 

Where E is the column with the times.

  • Like 1
Link to comment
Share on other sites

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

 

keeganrant_450x299.jpg?quality=90&strip=

 

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. 

  • Haha 2
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.