Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average 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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
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,COL UMN(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). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
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,COL UMN(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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
=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 "Harlan Grove" wrote in message oups.com... 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,COL UMN(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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
Try this array formula (commit with Ctrl+shift+Enter):
=IF(COUNT(A6:W6),AVERAGE(INDEX(A6:W6,LARGE(IF(A6:W 6<"",COLUMN(A6:W6)),MIN(COUNT(A6:W6),10))):W6),"N O DATA") Does that help? *********** Regards, Ron XL2002, WinXP-Pro "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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
I don't know if this helps or not. I can't seem to get this formula
pasted into my spreadsheet as a formula. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
Also what does (commit with Ctrl+shift+Enter): mean?
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
Well at least I got the formula pasted in, but it says it has an error.
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
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 "GaryC" wrote in message oups.com... Well at least I got the formula pasted in, but it says it has an error. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of 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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
Sometimes error results are the correct and expected result.
Agreed Biff "Harlan Grove" wrote in message oups.com... 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. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
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 "GaryC" wrote: Well at least I got the formula pasted in, but it says it has an error. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
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. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of Last 10
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 "Ron Coderre" wrote: 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 "GaryC" wrote: Well at least I got the formula pasted in, but it says it has an error. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for determing average based on weighting | Excel Worksheet Functions | |||
plotted Average | Charts and Charting in Excel | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
What is this kind of average called? | Excel Worksheet Functions | |||
Average the Last Five Cells in a Column | Excel Worksheet Functions |