Forums

Any spreadsheet experts?

• oilyrag 875 Posts

  Thu, Apr 10 2014 8:52 AM

  DaddysKat:

   

  Nice SS Oily ... How do you handle the average once you get beyond 500 rounds played (top 500 scores only)?  The spreadsheet covers an average well ... I just didn't see how you were going to handle the "top 500 scores" average once you get beyond the saturation point.  

  its simple

  the basic wgt average is the best X number of scores

  sort into order , lowest to highest

  line 500 is the average :)

  so on mine, use the tour master tab as example.

  the basic wgt formula for tour master to legend is best 50 rounds, 61 average 

  column a is added to keep scores sequential

  sort columns a to g based on column g - lowest to highest

  leave columns h i j static - do not include in sort

  look down column i for round 50 ( the saturation point )

   

  ....

   

  re sort columns a - f based on column a - lowest to highest

  this puts them back in playing order

   

   

  ---------------

  on the legend tab if the jump from legend to tour legend is 61

  then i currently have 151 rounds out of 182 played at legend level that fit the criteria

• DaddysKat 3,554 Posts

  Thu, Apr 10 2014 8:53 AM

  ScottHope:

  Nice one oily, but I have a hunch that Kat is going to come up with one of her wonderful creations which with make all of our efforts look pretty pale by comparison.  : )

  Sorry Scott ... since I'm a good 200 - 300 games in as a legend and haven't really bothered keeping track from day 1, it's not something I would use.  I've given up writing for others if I won't use it.  My current mantra ... adding to my tried and true mantra of "don't f**k up" is "me first".

  If I'm using it and find it beneficial, I'll write a ton of code to share with others.  If its just for others (tourney manager with handicapping), it just seems to be a big let-down when it's not received well.  I don't have to take my shoes off to count the number of CC's that use that spreadsheet.

• DaddysKat 3,554 Posts

  Thu, Apr 10 2014 9:10 AM

  Chinajohn:

  Again wonderful and thank you. 

  Another question, your "(ScoresTable)" I assume must refer to something, probably the complete set of data perhaps the whole spreadsheet, how do you define it? 

  In Excel 2007, when you click the Menu Option "Insert", there will be an icon called "Table".  You can select your range of scores first, then click the icon, or you can click the icon then define your range.  

  If you set the topmost cell with the heading "ScoresTable" and tell it your table has headers, the table will receive the name "ScoresTable" by default.  If you don't have headers, you can enter the name of the table manually ... in 2007, there is a field in the icon display that will allow you to type in a name.  Mine comes up in the upper left corner of the icon displays.

• DaddysKat 3,554 Posts

  Thu, Apr 10 2014 9:13 AM

  oilyrag:

  its simple

  the basic wgt average is the best X number of scores

  sort into order , lowest to highest

  line 500 is the average :)

  Gotcha ... thanks!

   

• ScottHope 10,623 Posts

  Thu, Apr 10 2014 10:01 AM

  I don't know if this will make things clearer, or more confusing, but I've done it now so I'm going to post it. : )

  DaddysKat:

  Sorry Scott ... since I'm a good 200 - 300 games in as a legend and haven't really bothered keeping track from day 1, it's not something I would use.

  Okay Kat, but you have done me a small favour into spurring me on try and understand the formula that I'm using and to tweak it a bit, cheers.  : )

• DaddysKat 3,554 Posts

  Thu, Apr 10 2014 10:14 AM

  ScottHope:

  I don't know if this will make things clearer, or more confusing, but I've done it now so I'm going to post it. : )

  Very nice ... for everyone's info, you can also name the table ... but for the purposes of the calc you shared and only having one table on the spreadsheet, naming the table really isn't important.

  I just have to deal with this FOP programmer/spreadsheet designer ... and he's big on getting into the habit of naming everything.  His reasoning ... it makes the formulas easier to read.

  Great job Scott and a very good share!

  (personal question ... how do you get the text definition boxes on your video?)

• ScottHope 10,623 Posts

  Thu, Apr 10 2014 10:32 AM

  Thanks again Kat, the application I use for making my videos is Camtasia, a bit pricey for a tight a$$ like me but it's a good program IMHO.

  EDIT : One thing I failed to point out in the vid was that because the data is in a table format, it can be sorted and filtered from each column header in a variety of ways using the little drop down arrows.  Video updated.

  ↓↓  EDIT : Cheers Kat, I'm getting there with some expert help.  : )  ↓↓

• DaddysKat 3,554 Posts

  Thu, Apr 10 2014 11:03 AM

  ScottHope:

  Thanks again Kat, the application I use for making my videos is Camtasia, a bit pricey for a tight a$ like me buts it's a good program IMHO.

  EDIT : One thing I failed to point out in the vid was that because the data is in a table format, it can be sorted from each column header in a variety of ways using the little drop down arrows.

  Thanks for the info, Scott.  And you get my vote for the best way to handle CJ's question.

• Chinajohn 1,190 Posts

  Thu, Apr 10 2014 6:16 PM

  Thanks all, some wonderful help. Now I need a free day or two to play with all your ideas. Trouble is if there is a free rental weekend my 'free day or two' may have to wait another week.

  Oilyrag, your sort function is (was?) the way I'd planned on doing it but as you've pointed out it means re-arranging the sheet every time you calculate your average, (or it does for me as I border on OCD when it comes to tidy), so I was looking for something that would continuously update as soon as I put a new score in. It appears Scott and DaddysKat have come up with a method.

• DaddysKat 3,554 Posts

  Thu, Apr 10 2014 7:06 PM

  Chinajohn:

  Oilyrag, your sort function is (was?) the way I'd planned on doing it but as you've pointed out it means re-arranging the sheet every time you calculate your average, (or it does for me as I border on OCD when it comes to tidy), so I was looking for something that would continuously update as soon as I put a new score in. It appears Scott and DaddysKat have come up with a method.

  Got to give credit where credit is due, CJ ... that was all Scott.  I just threw in something I'm sure he already knew.  Adding the video to show how to setup the table went above and beyond in my book.  

  Way to go Scott!