Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average of last 3 non-0 numbers in row?
I am secratary of golf league and handicap is calculated of the average of
the last 3 rounds played. If a player is absent for a week, there is a zero in his score. This would be a rolling average of coarse. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average of last 3 non-0 numbers in row?
=AVERAGE(N(OFFSET($B$2,0,LARGE(IF($B$2:I2<0,COLUM N($B$2:I2)-MIN(COLUMN($B$2:I2))),{1,2,3}))))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "joewis" wrote in message ... I am secratary of golf league and handicap is calculated of the average of the last 3 rounds played. If a player is absent for a week, there is a zero in his score. This would be a rolling average of coarse. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average of last 3 non-0 numbers in row?
With....Positive scores (or zeros) in B2:K2
This regular formula calculates the average of the last 3 values that are greater than zero: L2: =AVERAGE(LOOKUP(LARGE(INDEX(ISNUMBER(B2:K2)*(B2:K2 0)*COLUMN(B2:K2),0),{3,2,1}),COLUMN(B2:K2),B2:K2) ) Adjust range references and copy that formula down as far as you need. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "joewis" wrote: I am secratary of golf league and handicap is calculated of the average of the last 3 rounds played. If a player is absent for a week, there is a zero in his score. This would be a rolling average of coarse. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average of numbers under 10 | New Users to Excel | |||
average a continuous group of numbers and negative numbers are 0 | Excel Worksheet Functions | |||
Average highest 16 numbers on a column of 32 numbers | Excel Worksheet Functions | |||
average of kth largest numbers in an array of n numbers | Excel Worksheet Functions | |||
Average of numbers in column between to other numbers | Excel Discussion (Misc queries) |