scooby_simon Posted September 13, 2008 Report Share Posted September 13, 2008 I need to be able to use the LOCATION of the cell that a formula resides in to be part of the formula. Essentially I want to average the scores in a row up to an including the score in the row above. Score: 1,2,3,4 Average 1,1.5,2,2.25 etc, scores will be added as an when, BUT I do not want to effect the previous values for average score and so I cannot just average the whole row, I also do not want to explicitly define the range as the total number of races is not known at the beginning. The ROW() and COLUMN() functions will return the NUMERIC row and col numbers, but is there a function that returns the value of the cell it is in? CELL("address") returns the last cell to be changed so that's no good... Ideas ? Link to comment Share on other sites More sharing options...
Teacake Posted September 13, 2008 Report Share Posted September 13, 2008 I've read that through four or five times, and I'm not really understanding it. Can you try again? Have you got a list of values, and you want in the first calculated cell to show the average of the first value, and then in the second calculated cell the average of the first two values, in the third the average of the first three, and so on? Link to comment Share on other sites More sharing options...
D1MAC Posted September 13, 2008 Report Share Posted September 13, 2008 Would it not be worth doing in columns rather than rows and then using the average element of SUBTOTAL? Should be fine as long as you have an identifier column/columns. Otherwise, I would have thought you'd be looking at a rather large nested if formula (or AVERAGEIF - if it exists, can't remember) or a running average across an entire row. Unless there's a very clever way, of course Link to comment Share on other sites More sharing options...
scooby_simon Posted September 13, 2008 Author Report Share Posted September 13, 2008 I have a list of results after day one of a championship I score 1,2,3 and so my average at the end of the first race is 1, 2nd race is 1.5 and 3rd race is 2. Next race I score a 3 and so my average is now 9/4; I want to keep the historic average after each race and have it displayed; however I do not know how many races thre will be and so I cannot hard code the averge calc in each cell; thus the average I need is the average of the scores to date so I wanted to use (in the row below the actual scores) AVERAGE(a1, current location of row above) Link to comment Share on other sites More sharing options...
sidicks Posted September 13, 2008 Report Share Posted September 13, 2008 I have a list of results after day one of a championship I score 1,2,3 and so my average at the end of the first race is 1, 2nd race is 1.5 and 3rd race is 2. Next race I score a 3 and so my average is now 9/4; I want to keep the historic average after each race and have it displayed; however I do not know how many races thre will be and so I cannot hard code the averge calc in each cell; thus the average I need is the average of the scores to date so I wanted to use (in the row below the actual scores) AVERAGE(a1, current location of row above) Not sure if i understand correctly... Can't you use the formula "=average($A$1:A1)", where A1 is the first entry? If you copy that accross columns, then the first part of the range is fixed (i.e. the first race) and then the average will automatically include the latest race..?? Or have i misunderstood....? Sidicks Link to comment Share on other sites More sharing options...
scooby_simon Posted September 13, 2008 Author Report Share Posted September 13, 2008 Not sure if i understand correctly...Can't you use the formula "=average($A$1:A1)", where A1 is the first entry? If you copy that accross columns, then the first part of the range is fixed (i.e. the first race) and then the average will automatically include the latest race..?? Or have i misunderstood....? Sidicks That would work if I only wanted to calc one persons average, but I also have an unknown number of people who will race. Thus I need to do this across mutile rows of peoples results. Thus if I use AVERAGE($A1:A1) it will work Thanks ++++++ Link to comment Share on other sites More sharing options...
Teacake Posted September 14, 2008 Report Share Posted September 14, 2008 Not sure if i understand correctly...Can't you use the formula "=average($A$1:A1)", where A1 is the first entry? That's what I was angling towards, but I'm still not sure what Simon's after nor whether this solved it. Link to comment Share on other sites More sharing options...
scooby_simon Posted September 14, 2008 Author Report Share Posted September 14, 2008 That's what I was angling towards, but I'm still not sure what Simon's after nor whether this solved it. See above Link to comment Share on other sites More sharing options...
Teacake Posted September 15, 2008 Report Share Posted September 15, 2008 See above Yeah, I was just confused because it started off with you seeming to say that it wouldn't work because you didn't know the number of people that would race, but then saying that it would work after all. I missed the fact that you'd dropped the $ from the second calculation until I re-read it for the tenth time. Glad it's worked out. Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now