From: houndawg on 6 Mar 2010 01:02 I'm trying to figure out a way to make an excel spreadsheet that will help me out with my players golf scores. I'd like a spreadsheet that I can enter scores in for each player daily, but will only give me an average for the lowest 4 out of their most recent 5 scores. I'd like to have their older scores still visible on the spreadsheet, but not used in the calculation of their average. In case I'm as confusing as I figure I am, here's an example: Say Billy turns in 6 practice scores, I'd like the spreadsheet to have all 7 scores on it, but only give me an average of the 4 lowest scores he's turned in out of his latest 5 scores....ignoring his first two scores.
From: Lars�ke Aspelin on 6 Mar 2010 02:17 On Fri, 5 Mar 2010 22:02:01 0800, houndawg <houndawg(a)discussions.microsoft.com> wrote: >I'm trying to figure out a way to make an excel spreadsheet that will help me >out with my players golf scores. I'd like a spreadsheet that I can enter >scores in for each player daily, but will only give me an average for the >lowest 4 out of their most recent 5 scores. I'd like to have their older >scores still visible on the spreadsheet, but not used in the calculation of >their average. In case I'm as confusing as I figure I am, here's an example: > >Say Billy turns in 6 practice scores, I'd like the spreadsheet to have all 7 >scores on it, but only give me an average of the 4 lowest scores he's turned >in out of his latest 5 scores....ignoring his first two scores. Assuming that the scores are in column A starting in cell A1 and that all scores are positive numbers. Try this formula: =AVERAGE(SMALL(OFFSET(A1,MAX((A1:A100>0)*ROW(A1:A100))5,,5),{1,2,3,4})) Note: This is an array formula that should be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Change the 100 in both places to reflect the maximum number of scores to be in column A. Hope this helps / Lars�ke
From: Russell Dawson on 6 Mar 2010 03:12 If A1 contains players name and b1, c2, d1 etc contain scores. I've made a very simple perhaps ugly solution. In F2 input =SUM(B1:F1)MAX(B1:F1) That gives the total less the highest  I'm struggling to average that figure. Logically it should be divided by 4 but it won't work. To get round this in F3 input F2/4 Hide row 2 I only give this half baked solution as I'd love to know why my formula won't work with a /4 tagged on.  Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "houndawg" wrote: > I'm trying to figure out a way to make an excel spreadsheet that will help me > out with my players golf scores. I'd like a spreadsheet that I can enter > scores in for each player daily, but will only give me an average for the > lowest 4 out of their most recent 5 scores. I'd like to have their older > scores still visible on the spreadsheet, but not used in the calculation of > their average. In case I'm as confusing as I figure I am, here's an example: > > Say Billy turns in 6 practice scores, I'd like the spreadsheet to have all 7 > scores on it, but only give me an average of the 4 lowest scores he's turned > in out of his latest 5 scores....ignoring his first two scores. >
From: Russell Dawson on 6 Mar 2010 03:14 I meant to say that you can then drag the formulae across which will give you a continous rolling average as scores come in.  Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "houndawg" wrote: > I'm trying to figure out a way to make an excel spreadsheet that will help me > out with my players golf scores. I'd like a spreadsheet that I can enter > scores in for each player daily, but will only give me an average for the > lowest 4 out of their most recent 5 scores. I'd like to have their older > scores still visible on the spreadsheet, but not used in the calculation of > their average. In case I'm as confusing as I figure I am, here's an example: > > Say Billy turns in 6 practice scores, I'd like the spreadsheet to have all 7 > scores on it, but only give me an average of the 4 lowest scores he's turned > in out of his latest 5 scores....ignoring his first two scores. >
From: T. Valko on 6 Mar 2010 13:04 Are there/will there be any empty cells within the range? For example: A2 = 77 A3 A4 = 82 A5 = 83 A6 = 80 A7 A8 A9 = 79 The average would include 77, 79, 80, 82. What should happen if there aren't at least 5 scores?  Biff Microsoft Excel MVP "houndawg" <houndawg(a)discussions.microsoft.com> wrote in message news:2C6AE611E51E421194B7B6C09068F668(a)microsoft.com... > I'm trying to figure out a way to make an excel spreadsheet that will help > me > out with my players golf scores. I'd like a spreadsheet that I can enter > scores in for each player daily, but will only give me an average for the > lowest 4 out of their most recent 5 scores. I'd like to have their older > scores still visible on the spreadsheet, but not used in the calculation > of > their average. In case I'm as confusing as I figure I am, here's an > example: > > Say Billy turns in 6 practice scores, I'd like the spreadsheet to have all > 7 > scores on it, but only give me an average of the 4 lowest scores he's > turned > in out of his latest 5 scores....ignoring his first two scores. >

Next

Last
Pages: 1 2 3 Prev: Entering Values and Updating Next Empty Cell in a Range Next: Conditional Formatting Query 