Forums

Any spreadsheet experts?

• Chinajohn 1,190 Posts

  Wed, Apr 9 2014 12:58 AM

  I like many others keep track of my ranked rounds as a legend and my average using a simple spreadsheet. At the moment I only have 310 ranked rounds as a legend so keeping tabs on the average is very basic.

  However, in the future I'll eventually get to the magic 500 after which only my best 500 rounds will be used for calculating my average. Does anyone know of a relatively simple instruction to get the average only to use the best 500 scores?

  At the moment the only way I can think of doing it is highlight the column with the 18 hole score in, then select the key to put them in order (and select keep the rest of the linked information too) then move the scores for those outside the 500 and then put back in order. This sounds a long and laborious method, so I'm keen to hear any easier method.

• Hogwired 82 Posts

  Wed, Apr 9 2014 1:16 AM

  RANK function

  AVGIF function (<501 criteria)

  I'm a little removed from my desk job, but I think a little brain grease could get the above to return your desired answer.

• oilyrag 875 Posts

  Wed, Apr 9 2014 1:17 AM

  all you need to do is use sort

   

  um - was a bit quick on the draw in putting this up - will only work where ranked round are the only factor in the av calculation

   

  b to f are imported

  g to j are calculated

  sort columns a-g based on g

   

  

• ScottHope 10,623 Posts

  Wed, Apr 9 2014 2:58 AM

  Hi John, you might laugh at this but I have a formula that I don't fully understand, but it works.

  To find the average of a column of figures obviously you would use the AVERAGE function.

  But you don't want the average of all the numbers, just the lowest 500.

  That's where the SMALL function comes in. With the small function you can find the smallest number, 2nd smallest, 10th smallest and so on in an array.

  SMALL(*your array or data set goes here*,x) The 'x' in the formula here extracts the 'x'th smallest value in the data, so if the 'x' value is 12 then the formula returns the 12th smallest value in the data.

  Now this is where I get lost and confused because I can't fully comprehend the INDIRECT function.

  SMALL(*your scores array goes here*,ROW(INDIRECT("1:500")))

  Now I suppose the INDIRECT("1:500") function here must give the ROW function, (which takes the place of the 'x' value in the SMALL function explanation example above), the values 1 through to 500 so that the SMALL function can extract the 500 smallest values from your scores array but I'm a little confused about that so I won't say anymore.

  So now that we have the 500 smallest values, we can tack the AVERAGE function onto the front

  AVERAGE(SMALL(*your scores array goes here*,ROW(INDIRECT("1:500"))))

  I am a little worried that as you have less than 500 scores John this won't work. So you could try changing the top value in the INDIRECT function to match the maximum number of scores you have and then increase by one as you add each score until you get to 500, or you could extend your score column to 500 and have 190 or so empty cells ready for your upcoming scores.

  One final thing to note is the formula should look like this in the formula bar...

  {=AVERAGE(SMALL(*your scores array goes here*,ROW(INDIRECT("1:500"))))}

  ...notice that there are curly brackets around the formula, this is because the formula is working with an array, and as such has to be entered using the CTRL + SHIFT + ENTER key combination. You don't add the curly brackets to the actual formula, they appear when you enter the formula with that key combo, if they're not there, the formula won't work correctly.

  As I said John I don't fully understand how it works, so I might not be able to help you out if it doesn't work, but I will try to assist if I can.

  (PS. EasyEdward and DaddysKat are both very clever, perhaps they can help too).

• Chinajohn 1,190 Posts

  Wed, Apr 9 2014 3:17 AM

  ScottHope:

  Hi John, you might laugh at this but I have a formula that I don't fully understand, but it works.

  To find the average of a column of figures obviously you would use the AVERAGE function.

  But you don't want the average of all the numbers, just the lowest 500.

  That's where the SMALL function comes in. With the small function you can find the smallest number, 2nd smallest, 10th smallest and so on in an array.

  Now this is where I get lost and confused because I can't fully comprehend the INDIRECT function.

  SMALL(*your scores array goes here*,ROW(INDIRECT("1:500")))

  Now I suppose the INDIRECT("1:500") function here must give the ROW function the values 1 through to 500 so that the SMALL function can extract the 500 smallest values from your scores array but I'm a little confused about that so I won't say anymore.

  So now that we have the 500 smallest values, we can tack the AVERAGE function onto the front

  AVERAGE(SMALL(*your scores array goes here*,ROW(INDIRECT("1:500"))))

  I am a little worried that as you have less than 500 scores John this won't work. So you could try changing the top value in the INDIRECT function to match the maximum number of scores you have and then increase by one as you add each score, or you could extend your score column to 500 and have a 190 or so empty cells ready for your upcoming scores.

  One final thing to note is the formula should look like this in the formula bar...

  {=AVERAGE(SMALL(*your scores array goes here*,ROW(INDIRECT("1:500"))))}

  ...notice that there are curly brackets around the formula, this is because the formula is working with an array, and as such has to be entered using the CTRL + SHIFT + ENTER key combination. You don't add the curly brackets to the actual formula, they appear when you enter the formula with that key combo, if they're not there, the formula won't work correctly.

  As I said John I don't fully understand how it works, so I might not be able to help you out if it doesn't work, but I will try to assist if I can.

  (PS. EasyEdward and DaddysKat are both very clever, perhaps they can help too).

  Scott wonderful I'm pretty sure that's what I need. One question though,

  'scores array', what do you mean, the cells the scores are in? e.g. E3:E503?

• ScottHope 10,623 Posts

  Wed, Apr 9 2014 3:20 AM

  Yes John, E3:E503 is an array, sorry about not explaining that bit.

• DaddysKat 3,554 Posts

  Wed, Apr 9 2014 8:43 AM

  ScottHope:

  {=AVERAGE(SMALL(*your scores array goes here*,ROW(INDIRECT("1:500"))))}

  This will work as long as you have 500 or more scores ... only issue is, if you don't, the rows with nothing will either return 0 or give you an error (I'm not very good with formulas, but can write VB Script with the best of them).

  The other issue I see is the range defined as "e3:e503".  Seems like you would be better off naming this range (such as "Scores") and use that instead of the fixed range (e3:e503) ... it seems like you want to get the smallest 500 scores from an array that can grow beyond 500 rows (700, 800 or even 1000 rows of data).

  I think since you're under 500 scores, you will need to incorporate an if statement to see if there are 500 or more scores in the range.  If there are, use Scott's formula.  If not, you can just do the Average function for the full range of scores.

  Maybe something like =if(count(scores)>=500,(scott's calculation),average-calc for the range of scores only).  You'll probably need to do some conjoining in your formula to determine the true range (maybe incorporate the count when defining the range).

   

• ScottHope 10,623 Posts

  Wed, Apr 9 2014 9:00 AM

  Thanks for jumping in on this Kat, I don't do Excel too well so I appreciate you helping out.

• DaddysKat 3,554 Posts

  Wed, Apr 9 2014 9:58 AM

  ScottHope:

  Thanks for jumping in on this Kat, I don't do Excel too well so I appreciate you helping out.

  You're welcome, Scott ... I'm not very well versed with the formulas ... I know they are somewhat early basic language, so I could probably figure out how to do the conjoining when determining the range if under 500.  It would take me a bit of time and testing.  

  Let me know if you get it to work John, or if you need a bit of further help.  I'll try to come up with a solution for you.

• Chinajohn 1,190 Posts

  Wed, Apr 9 2014 10:18 PM

  DaddysKat:

  ScottHope:

  Thanks for jumping in on this Kat, I don't do Excel too well so I appreciate you helping out.

  You're welcome, Scott ... I'm not very well versed with the formulas ... I know they are somewhat early basic language, so I could probably figure out how to do the conjoining when determining the range if under 500.  It would take me a bit of time and testing.  

  Let me know if you get it to work John, or if you need a bit of further help.  I'll try to come up with a solution for you.

  Thanks to you both. I'll have a play this coming weekend (unless it's a free rental weekend) and let you know how it turns out.