 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2006Average of Last 10
What I am trying to do is track a 10 week rolling average, where scores are entered weekly in row A6 thru W6. The formula needs to count back starting with W6 until it counts 10 scores then average them. Some cells may be blank. Can anyone help? GaryC wrote... >What I am trying to do is track a 10 week rolling average, where scores >are entered weekly in row A6 thru W6. The formula needs to count back >starting with W6 >until it counts 10 scores then average them. Some cells may be blank. >Can anyone help? Without using volatile functions, try =AVERAGE(INDEX(A6:W6,MAX(1,LOOKUP(1E+300,A6:W6,COLUMN(A6:W6))-9)) :INDEX(A6:W6,LOOKUP(1E+300,A6:W6,COLUMN(A6:W6)))) Using the volatile OFFSET function, =AVERAGE(OFFSET(A6:W6,0,LOOKUP(1E+300,A6:W6,COLUMN(A6:W6))-1,1,-10)) Both assume there are no gaps in your data, e.g., blank cell K6 between nonblank cells A6:I6 and L6:P6. If A6:W6 all allways contain values, then just use =AVERAGE(N6:W6). GaryC wrote... >What I am trying to do is track a 10 week rolling average, where scores >are entered weekly in row A6 thru W6. The formula needs to count back >starting with W6 >until it counts 10 scores then average them. Some cells may be blank. >Can anyone help? Without using volatile functions, try =AVERAGE(INDEX(A6:W6,MAX(1,LOOKUP(1E+300,A6:W6,COLUMN(A6:W6))-9)) :INDEX(A6:W6,LOOKUP(1E+300,A6:W6,COLUMN(A6:W6)))) Using the volatile OFFSET function, =AVERAGE(OFFSET(A6:W6,0,LOOKUP(1E+300,A6:W6,COLUMN(A6:W6))-1,1,-10)) Both assume there are no gaps in your data, e.g., blank cell K6 between nonblank cells A6:I6 and L6:P6. If A6:W6 all allways contain values, then just use =AVERAGE(N6:W6). I have a feeling my caveat above about blank cells may be your exact problem. If so, then use the array formula =AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6), --(COLUMN(A6:W6)<=TRANSPOSE(COLUMN(A6:W6)))),0)):W6) I tried to copy =AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6), --(COLUMN(A6:W6)<=TRANSPOSE(COLUMN(A6:W6)))),0)):W6) into my spreadsheet and it treats it as text not a formula. GaryC wrote... >I tried to copy >=AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6), >--(COLUMN(A6:W6)<=TRANSPOSE(COLUMN(A6:W6)))),0)):W6) >into my spreadsheet and it treats it as text not a formula. Make sure there are no leading spaces before the initial = character. Some browser-based newsgroup portals, like Google Groups, add invisible characters that screw up copying from your browser and pasting into Excel. >=AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6), >--(COLUMN(A6:W6)<=TRANSPOSE(COLUMN(A6:W6)))),0)):W6) If there are <10 values, returns #N/A. .....MATCH(MIN(COUNT(A6:W6),10)....... Still doesn't account for COUNT = 0 So, maybe: =IF(COUNT(),AVERAGE(INDEX..........................)):W6),"") Biff > GaryC wrote... >>What I am trying to do is track a 10 week rolling average, where scores [quoted text clipped - 22 lines] > =AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6), > --(COLUMN(A6:W6)<=TRANSPOSE(COLUMN(A6:W6)))),0)):W6) You're getting ahead of me. I'm still trying to plug the first formula in my spreadsheet. I haven't done this before. I did a copy paste and obviously I need to do something different for the formula to work. It pasted into two cells. There very well may be blanks in the string of numbers. The first week there will be only one number in the row of data. The second week, 2 and so on. After 10 weeks of scores, I want to drop off the oldest score and average the last 10. Biff wrote... >>=AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6), >>--(COLUMN(A6:W6)<=TRANSPOSE(COLUMN(A6:W6)))),0)):W6) > >If there are <10 values, returns #N/A. > >.....MATCH(MIN(COUNT(A6:W6),10)....... Good point, if averages of fewer than 10 values should be permitted. >Still doesn't account for COUNT = 0 > >So, maybe: > >=IF(COUNT(),AVERAGE(INDEX..........................)):W6),"") Depends. AVERAGE of a range of blank cells returns #DIV/0!, which is the correct result. After your change, using MIN, the array formula would also return #DIV/0! if count were 0. Sometimes error results are the correct and expected result. >Sometimes error results are the correct and expected result. Agreed Biff > Biff wrote... >>>=AVERAGE(INDEX(A6:W6,MATCH(10,MMULT(--ISNUMBER(A6:W6), [quoted text clipped - 16 lines] > would also return #DIV/0! if count were 0. Sometimes error results are > the correct and expected result. I really apreciate you guys trying to help me, but all this is so far over my head I am still having problems getting any formula to work at all. I've tried copying your formulas into note pad, putting it all on one line and then copying it into my spreadsheet. Since I don't understand the formula, I'm not sure if I'm suppose to insert or delete things from what you have posted. I see "--" in a couple of places. Am I supposed to replace this with something? Any further help will be greatly appreciated. Try this array formula (commit with Ctrl+shift+Enter): =IF(COUNT(A6:W6),AVERAGE(INDEX(A6:W6,LARGE(IF(A6:W6<>"",COLUMN(A6:W6)),MIN(COUNT(A6:W6),10))):W6),"NO DATA") Does that help? *********** Regards, Ron XL2002, WinXP-Pro > What I am trying to do is track a 10 week rolling average, where scores > are entered weekly in row A6 thru W6. The formula needs to count back > starting with W6 > until it counts 10 scores then average them. Some cells may be blank. > Can anyone help? I don't know if this helps or not. I can't seem to get this formula pasted into my spreadsheet as a formula. Also what does (commit with Ctrl+shift+Enter): mean? Well at least I got the formula pasted in, but it says it has an error. Select the cell with the formula. Double click on that cell then, hold down both the CTRL key and the SHIFT key then hit ENTER. If done properly the formula will be enclosed in squiggly braces { }. You can't just type the braces in, you MUST use the key combo of CTRL,SHIFT,ENTER. Also, if you edit the formula (which is what you did when you double clicked the formula cell) it must be re-entered as an array using the key combo. Biff > Well at least I got the formula pasted in, but it says it has an error. Sorry about the delay....I've been away from my PC for the last 4 hours. I'm glad Biff jumped in here with the answer you needed. Did the formula work for you? *********** Regards, Ron XL2002, WinXP-Pro > Well at least I got the formula pasted in, but it says it has an error. To mitigate some of the apparent confusion, I emailed the OP and received the actual scenario he is using. The range containing the values is F4:AB4, so the column number difference between Col_A and Col_F must be accounted for. I sent him this array formula, which I believe will work for him (I'm waiting to hear back): AC4: =IF(COUNT(F4:AB4),AVERAGE(INDEX(F4:AB4,LARGE(IF(F4:AB4<>"",COLUMN(F4:AB4)-COLUMN(F4)+1),MIN(COUNT(F4:AB4),10))):AB4),"NO DATA") *********** Regards, Ron XL2002, WinXP-Pro > Sorry about the delay....I've been away from my PC for the last 4 hours. > [quoted text clipped - 9 lines] > > > Well at least I got the formula pasted in, but it says it has an error. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.