How to use the AVERAGE function
Calculates the average of numbers in a cell range. In other words, the sum of a group of numbers and then dividing by the count of those numbers.
Table of Contents
- AVERAGE Function Syntax
- AVERAGE function Arguments
- AVERAGE Function Example
- How to average a column containing some text values
- Are zeros counted in the AVERAGE function?
- Are boolean values ignored in the AVERAGE function?
- How to average absolute values
- How to average across sheets - 3D range
- How to average by group
- How to average by month
- How to average blank as zero
- How to average excluding high and low
- How to exclude some cells from average
- Calculate running average of last 10 data with random blank cells
- Get Excel *.xlsx file
1. AVERAGE Function Syntax
The AVERAGE function may have up to 255 arguments, each argument can contain a reference to a single cell, cell range, and/or constants.
You can also use other functions in the arguments, this makes it possible to calculate an average based on a condition or criteria among many other things.
AVERAGE(number1, [number2], ...)
2. AVERAGE Function Arguments
The first argument is required, the remaining arguments are optional.
number1 - Required. numbers or a cell range
[number2]Â - Optional. Additional arguments up to 255
3. AVERAGE Function example
This example demonstrates a formula in cell D3 that calculates an average based on a column, the column contains both numerical values and empty cells.
This is not a problem, the AVERAGE function is designed to ignore empty blank cells.
Formula in cell D3:
Cells B4 and B7 are empty, they are not counted. 3 + 5 + 4 + 4 equals 16. 16 / 4 equals 4.
Here is how the AVERAGE function works:
4. How to average a column containing some text values
This example demonstrates a formula in cell D3 that calculates an average based on a column, the column contains both numerical values and text values.
This is also not a problem, the AVERAGE function is designed to ignore text values, however, keep in mind that error values are not ignored.
Formula in cell D3:
Cells B4 and B7 are empty, they are not counted. 3 + 5 + 4 + 4 equals 16. 16 / 4 equals 4.
5. Are zeros included in the AVERAGE function?
This example demonstrates a formula in cell D3 that calculates an average based on values located in a column, the column contains numerical values including some zeros.
The AVERAGE function calculates an average including zeros by default. How to calculate an average and ignore 0 (zeros)
Formula in cell D3:
Cells B4 and B7 are empty, they are not counted. 3 + 5 + 4 + 4 equals 16. 16 / 4 equals 4.
Cells B4 and B7 contain 0 (zeros), they are counted.
6. Are boolean values ignored in the AVERAGE function?
Boolean values in a cell range are ignored and not counted shown in cell D3 in the example above.
Formula in cell D3:
3 + 5 + 4 + 4 = 16. 16/4 = 4.
Boolean values are ignored by the AVERAGE function, however, they are included if you enter a boolean value as a constant in an argument, see cell B13 above.
The numerical equivalents are:
TRUE = 1
FALSE = 0 (zero)
Formula in cell B13:
1 + 0 = 1. 1/2 = 0.5 Cell B13 returns 0.5.
To average boolean values multiply the cell range with 1, this converts boolean values to their numerical equivalent.
Array formula in cell D6:
6.1 How to enter an array formula
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
6.2 Explaining formula in cell D6
Step 1 - Convert boolean values
B3:B8*1
becomes
{3; TRUE; 5; 4; TRUE; 4}*1
and returns
{3; 1; 5; 4; 1; 4}.
Step 2 - Calculate the average
AVERAGE(B3:B8*1)
becomes
AVERAGE({3; 1; 5; 4; 1; 4})
and returns 3. 3 + 1 + 5 + 4 + 1 + 4 = 18. 18/6 = 3.
7. How to average absolute values
The array formula in cell D3 converts numbers in B3:B8 to absolute numbers meaning the minus sign is removed. In other words, all numbers are equal to 0 (zero) or larger. The numbers are 3, -2, 5, -3, 3, and 4.
Formula in cell D3:
3 + 2 + 5 + 3 + 3 + 4 equals 20. 20 / 6 = approx. 3.33
8. Explaining formula
Step 1 - Calculate positive numbers
The ABS function converts negative numbers to positive numbers, in other words, the ABS function removes the sign.
ABS(number)
ABS(B3:B8)
becomes
ABS({3; -2; 5; -3; 3; 4})
and returns
{3; 2; 5; 3; 3; 4}.
Step 2 - Calculate the average
AVERAGE(ABS(B3:B8))
becomes
AVERAGE({3; 2; 5; 3; 3; 4})
and returns approx. 3.33
8. How to calculate an average across sheets
In order to calculate an average across worksheets values must be located at the same cell range throughout all worksheets. This technique can greatly reduce time you need to spend on formulas.
Formula in cell D3:
The formula uses values from sheets 'Across sheets' and 'Across sheets1' in both cell ranges B3:B8 and returns approx. 3.167
Here is how to enter this formula:
- Double press with left mouse button on cell D3, the prompt appears.
- Type =AVERAGE(
- Select cell range B3:B8 with mouse.
- Select tab "Across sheets" with left mouse button.
- Press and hold SHIFT key.
- Select tab "Across sheets1" with left mouse button.
- Release SHIFT key.
- Type )
- Press Enter.
All worksheets between Across sheets and Across sheets1 are now selected This example has only these two worksheets however, the technique is the same regardless the number of worksheets.
9. How to average by group
The image above demonstrates an array formula that calculates an average based on a condition specified in cell F2. Cell range B3:B8 contains categories and cell range C3:C8 contains the corresponding numbers we want to calculate an average.
Array formula in cell D3:
The array formula in cell D3 extracts number 3, 5, 3, and 4 based on the corresponding categories on the same row in cell range B3:B8. The average is 3.75 and here is how:
3+5+3+4 equals 15.
15 / 4 equals 3.75 This value matches the value in cell D3, our calculations are correct.
Explaining formula
Step 1 - Logical test
The equal sign lets you compare value to value, the result is a boolean value TRUE or FALSE. This will tell us which cells are equal to the condition.
B3:B8=F2 becomes {"A"; "B"; "A"; "B"; "A"; "A"}="A"
and returns {TRUE; FALSE; TRUE; FALSE; TRUE; TRUE}.
Step 2 - Evaluate IF function
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(B3:B8=F2,C3:C8,"") becomes IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE},C3:C8,"")
becomes IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE},{3; -2; 5; -3; 3; 4},"")
and returns {3; ""; 5; ""; 3; 4}.
Step 3 - Calculate average
AVERAGE(IF(B3:B8=F2,C3:C8,"")) becomes AVERAGE({3; ""; 5; ""; 3; 4}) and returns 3.75
Note, the AVERAGEIF and AVERAGEIFS functions are built to handle conditions without the need for an array formula. They are available for Excel 2007 users and later versions, I highly recommend you check them out.
10. How to calculate an average based on a month in corresponding date on the same row
This example lets you calculate an average based on the month in corresponding dates. The image above shows dates in cell range B3:B8, the months are B3: January, B4: February, B5: January, B6: March, B7 and B8: January.
The condition is specified in cell F2. 1 is January, 2 i February ... 12 is December. The formula filters the numbers in C3:C8 based on the dates in B3:B8 if the dates are equal to 1 meaning January.
Array formula in cell F4:
The number is not included in the calculation if the corresponding date is not equal to the specified value in cell F2 which is 1 in this example.
Explaining formula
Step 1 - Calculate month number
The MONTH function returns a number corresponding to the position of a given month in a year. January = 1, ... December = 12.
MONTH(B3:B8)
becomes
MONTH({44583; 44611; 44564; 44635; 44579; 44572})
and returns
{1; 2; 1; 3; 1; 1}.
Step 2 - Identify numbers equal to the specified month
The equal sign lets you compare value to value, the result is a boolean value TRUE or FALSE. This will tell us which cells are equal to the condition.
MONTH(B3:B8)=F2
becomes
{1; 2; 1; 3; 1; 1}=F2
becomes
{1; 2; 1; 3; 1; 1}=1
and returns
{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE}.
Step 3 - Replace TRUE with corresponding number in C3:C8
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(MONTH(B3:B8)=F2,C3:C8,"")
becomes
IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE}, C3:C8, "")
becomes
IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE}, {3; -2; 5; -3; 3; 4}, "")
and returns
{3; ""; 5; ""; 3; 4}.
Step 4 - Calculate average
AVERAGE(IF(MONTH(B3:B8)=F2,C3:C8,""))
becomes
AVERAGE({3; ""; 5; ""; 3; 4})
and returns 3.75
3 + 5 + 3 + 4 = 15. 15/4 = 3.75
11. How to average blank as zero
The AVERAGE function ignores blank cells , however, there is a simple workaround that allows you to count 0's (zeros) to the total number of observations.
Keep in mind, the average is calculated like this: Sum / total number of items. A blank cell is not in the total they are simply ignored. The trick is to add a 0 to the cell range, this makes the formula add a zero to a blank cell which returns a 0 (zero) in all blank cells in the given cell range.
Array formula in cell E4:
There are occasions when you want the blank to be counted in the total number of items and this is how to do it in the regular AVERAGE function. This is simple, reliable and effective.
Explaining formula
Step 1 - Add 0 (zero) to values
The plus sign lets you add numbers in Excel, an empty cell converts to a 0 (zero).
B3:B8+0
becomes
{3; ""; 5; ""; 3; 4} + 0
and returns {3; 0; 5; 0; 3; 4}.
Step 2 - Calculate average
AVERAGE(B3:B8+0)
becomes
AVERAGE({3; 0; 5; 0; 3; 4})
and returns 2.5
3+0+5+0+3+4 = 15. 15/6 = 2.5
Average without blanks as zeros: 3+5+3+4 = 15. 15/4 = 3.75
12. How to calculate an average excluding the largest and smallest numbers
This example demonstrates a formula that excludes the largest and smallest number from a set of values. The image above shows six values in cell range B3:B8 and they are: 3, 2, 5, 2, 1, and 4.
The formula excludes the highest and lowest number from the calculations, if there are multiple values matching the largest or smallest value then all of them are excluded.
Array formula in cell E2:
The largest number in 3, 2, 5, 2, 1, and 4 is 5 and the smallest is 1, The remaining values are 3, 2, 2, and 4. 3+2+2+4 equals 11. 11 / 4 equals 2.75
Explaining formula
Step 1 - Calculate the largest value
The MAX function returns the largest value in a cell range or array.
MAX(number1, [number2], ...)
MAX(B3:B8)
becomes
MAX({3; 2; 5; 2; 1; 4})
and returns 5.
Step 2 - Compare largest value to B3:B8
The equal sign lets you compare value to value, the result is a boolean value TRUE or FALSE. This will tell us which cells are equal to the largest number.
MAX(B3:B8)=B3:B8
becomes
5={3; 2; 5; 2; 1; 4}
and returns
{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}.
Step 3 - Calculate smallest value
The MIN function returns the smallest number in cell range or array.
MIN(number1, [number2], ...)
MIN(B3:B8)
becomes
MIN({3; 2; 5; 2; 1; 4})
and returns 1.
Step 4 - Compare the smallest number to numbers in B3:B8
The equal sign lets you compare value to value, the result is a boolean value TRUE or FALSE. This will tell us which cells are equal to the smallest number.
MIN(B3:B8)=B3:B8
becomes
1= {3; 2; 5; 2; 1; 4}
and returns
{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.
Step 5 - Add arrays (OR logic)
The plus sign lets you add values and also arrays, this creates OR logic between the two arrays containing boolean values.
TRUE + TRUE = TRUE
TRUE + FALSE = TRUE
FALSE + TRUE = TRUE
FALSE + FALSE = FALSE
One more thing, the boolean values are converted into their numerical equivalents when you add the arrays.
TRUE = 1 or in fact any number except zero
FALSE = 0 (zero)
(MAX(B3:B8)=B3:B8)+(MIN(B3:B8)=B3:B8)
becomes
{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE} + {FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}
and returns {0; 0; 1; 0; 1; 0}.
Step 6 - Replace 0 (zero) with corresponding value
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF((MAX(B3:B8)=B3:B8)+(MIN(B3:B8)=B3:B8),"",B3:B8)
becomes
IF({0; 0; 1; 0; 1; 0},"",B3:B8)
becomes
IF({0; 0; 1; 0; 1; 0},"", {3; 2; 5; 2; 1; 4})
and returns {3; 2; ""; 2; ""; 4}.
Step 7 - Calculate average
AVERAGE(IF((MAX(B3:B8)=B3:B8)+(MIN(B3:B8)=B3:B8),"",B3:B8))
becomes
AVERAGE({3; 2; ""; 2; ""; 4})
and returns 2.75
3 +2 + 2 + 4 = 11. 11/4 = 2.75
13. How to exclude some cells from average
This formula calculates an average based on numbers in cell range B3:B8, however, only if they are not also in the exclude list in cell range D3:D5.
For example, the numbers in cell range B3:B8 are 3, 2, 5, 1, 3, and 4. The numbers in the exclude list are 2, 3, and 4. The remaining values in cell range B3:B8 are 5 and 1.
The average becomes 5 + 1 equals 6. 6 / 2 eqials 3.
Array formula in cell F3:
The formula is small, easy to adjust and easy to understand. Excel 365 users may enter this array formula as a regular formula.
Explaining formula
Step 1 - Identify numbers based on criteria
The COUNTIF function calculates the number of cells that is equal to a condition. This will tell us where the excluded cells are.
COUNTIF(range, criteria)
COUNTIF(D3:D5, B3:B8)
becomes
COUNTIF({2;3;4},{3;2;5;1;3;4})
and returns
{1; 1; 0; 0; 1; 1}.
Step 2 - Replace 1 with nothing and 0 (zero) with number
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(COUNTIF(D3:D5, B3:B8), "", B3:B8)
becomes
IF({1; 1; 0; 0; 1; 1}, "", B3:B8)
becomes
IF({1; 1; 0; 0; 1; 1}, "", {3; 2; 5; 1; 3; 4})
and returns
{""; ""; 5; 1; ""; ""}.
Step 3 - Calculate average
AVERAGE(IF(COUNTIF(D3:D5, B3:B8), "", B3:B8))
becomes
AVERAGE({""; ""; 5; 1; ""; ""})
and returns 3. 5+1 = 6. 6/2 = 3
14. Calculate running average of last 10 data with random blank cells
Question:
- List of data and blank cells in a column which will be added from day to day.
- There are sometimes blank cells in column.
How to get the average of the 10 most recent data? The average will change from day to day.
Answer:
This array formula creates a dynamic range, filtering the 10 last data and returns the average. Adjust cell ranges $A$1:$A$25 in formula below.
Array formula in cell C3:
How to create an array formula
Excel 365 users can skip these steps, no need to enter an array formula. Simply press Enter.
- Select cell C3
- Copy (Ctrl + c) array formula
- Paste (Ctrl + v) array formula to formula bar.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- Release all keys.
How this array formula works in cell C18
The array formula calculates a dynamic cell range in column C to use based on non-empty cells in column B. $B$3:B3 is a dynamic cell reference, it expands as you copy the formula to cells below. This makes it a running average meaning a new average is calculated in every new cell below cell C.
The INDEX function creates a cell reference based on the 10th largest row number calculated from the non-empty cells in column B. This cell reference changes dynamically making it possible to calculate a running average.
The LARGE function tries to find the 10th largest row number. The LARGE function returns an error before 10 non empty cells are found and the IFERROR function converts the errors to a "hyphen". This is why you see hyphen or a minus character in the first top cells in column C.
As soon as there are 10 values in column B to average the formula returns the calculated value. This continues as far as you copy the formula to cells below and have values in column B.
The difference between this formula and other running calculations is that it actually checks that there are 10 values to process. It also calculates the 10 last values based on the current cell ignoring blanks. Earlier values are not in the calculation, you can change this to any number. To do this change 10 in the formula to the number of values you want to calculate an average of.
Step 1 - Extract row numbers of not empty cells
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
The logical expression contains an expanding cell range that grows when the cell is copied to cells below.
IF($B$3:B18<>"", ROW($B$3:B18), "")
becomes
IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}, ROW($B$3:B18), "")
becomes
IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18}, "")
and returns {3;"";5;"";7;8;9;10;"";"";"";14;15;"";17;18}.
Step 2 - Calculate the 10th largest row number
The LARGE function returns the k-th largest number. LARGE( array, k)
LARGE(IF($B$3:B18<>"", ROW($B$3:B18), ""), 10)
becomes
LARGE({3;"";5;"";7;8;9;10;"";"";"";14;15;"";17;18}, 10)
and returns 3.
Step 3 - Use row number to create a cell reference
The INDEX function lets you build a cell reference based on a row and column number.
INDEX(B:B, LARGE(IF($B$3:B3<>"", ROW($B$3:B3), ""), 10))
becomes
INDEX(B:B, 3)
and returns B3.
Step 4 - Build complete cell reference
A cell reference may contain two parts separated by a colon.
INDEX(B:B, LARGE(IF($B$3:B3<>"", ROW($B$3:B3), ""), 10)):B18
returns B3:B18.
Step 5 - Average values
The AVERAGE function calculates the average based on the cell reference we calculated earlier.
AVERAGE(INDEX(B:B, LARGE(IF($B$3:B3<>"", ROW($B$3:B3), ""), 10)):B18)
becomes
AVERAGE(B3:B18)
and returns 53.8
Step 6 - Return - if calculation returns error
The IFERROR fucntion returns "-" if the formula returns an error which it does until 10 numbers are found in the cell range.
Get Excel *.xlsx file
Useful resources
AVERAGE function - Microsoft
How To Calculate the Average of a Group of Numbers
'AVERAGE' function examples
The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]
This article demonstrates two formulas that calculate averages, the first formula calculates an average based on criteria, and the second […]
Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]
Functions in 'Statistical' category
The AVERAGE function function is one of 74 functions in the 'Statistical' category.
Excel function categories
Excel categories
22 Responses to “How to use the AVERAGE function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Oscar, what if list of data and blank cells are in a row.
Let's say data starts from Column C.
Cyril,
Array formula:
Yes, this CSE would average the last 10 Columns. But it would include the blanks.
Cyril,
Yes, the formula includes the ten last values. But the blanks are not counted and calculated?
Given the example posted above that is {1;"" ; 2;"" ; 2; 3; 4; 5;"" ;"" ;"" ; 6; 7;"" ; 8; 9;"" ; 10;"" ;"" ; 11; 12; 13;"" ; 14}, the average of the last 10 values (integers) would be 9.5.
Given that the data are in Row 1 from Column I to AG.
Given the formula in D1 as follows =AVERAGE(INDEX(I1:AG1, LARGE(IF($I$1:$AG$1"", COLUMN($I$1:$AG$1), ""), 10)):INDEX($I1:$AG1,MATCH(1E+307, $I$1:$AG$1))) CSE.
The formula would return 11.5 that is the average of the last 10 Columns. {9;"" ; 10;"" ;"" ; 11; 12; 13;"" ; 14}
The blanks are not counted, they are not calculated but the formula doesn't return the average of the last 10 values.
Would you agree?
Cyril,
COLUMN($I$1:$AG$1) returns {9, 10, .. and so on}
COLUMN(1:1) returns {1, 2, ... and so on}
MATCH(COLUMN($I$1:$AG$1),COLUMN($I$1:$AG$1)) returns the right array and size for your range.
Try this:
Very nice.
Consider this as well:
=AVERAGE(IF(COLUMN(I1:AG1)>=LARGE(IF(ISNUMBER(I1:AG1), COLUMN(I1:AG1)),MIN(10,COUNT(I1:AG1))), IF(ISNUMBER(I1:AG1),I1:AG1)))
Thanks Oscar.
Cyril,
thanks for your contribution!
I am trying to use this same formula, but for sum of last 10, instead of average. I replace AVERAGE in the formula, then I get an error. What do I need to correct make this happen with last 10 entries.
Thank you.
Hi Bill, are your data arranged in a Column or in a Row?
if in a row:
=SUM(IF(COLUMN(I6:AG6)>=LARGE(IF(ISNUMBER(I6:AG6), COLUMN(I6:AG6)),MIN(10,COUNT(I6:AG6))), IF(ISNUMBER(I6:AG6),I6:AG6))) Ctrl + Shift + Enter, not just enter.
If In column, I have a solution with Named ranges. But for sure Oscar will have a better solution.
Cheers.
Bill, if in a column, wouldn't =SUM(INDEX(A:A,LARGE(IF($A$1:$A$12500"",ROW($A$1:$A$12500),""),10)):INDEX(A:A,MATCH(9.99999999999999E+307,$A$1:$A$12500))) CSE work for you?
Hi,
I have a large data set of 546825 rows x 14 column. I need to average each 5 rows of this data; 1-5, 5-10, etc. Can you show me how do it in excel? Thanks indeed.
Cheers,
Andy
Hi Oscar
Nice to help us.
I would like you to help me with a function that find average of the first 3 scores. As your example there is blank cells.
I don't know what to change in the function you already published.
=AVERAGE(INDEX(A:A;LARGE(IF($A$1:$A$25"";ROW($A$1:$A$25);"");10)):INDEX(A:A;MATCH(1E+307;$A$1:$A$25)))
thank you
Theodor
tried to adapt this formula for "calculate average of last 10 (ignore blanks)in excel" for my data that is in columns F-AE but something is wrong:
=AVERAGE(OFFSET(INDEX(2:2,,COUNT($F2:$AE2)),,-9,1,10))
I really want to average the last 20. What am I doing wrong?
janis,
The array formula in this post works only for values in a single column.
I struggled a bit with getting the formula to work before I realized that the comma in 9,9E+307 needs to be changed for a dot for my regional settings. If anyone else has the same problem use this code:
=AVERAGE(INDEX(A:A, LARGE(IF($A$1:$A$25"", ROW($A$1:$A$25), ""), 10)):INDEX(A:A, MATCH(9.9E+307, $A$1:$A$25)))
I am trying to average the last five numbers of data arrayed in a row. How does the formula have to be modified for that?
hi, good formular, it did though take me a while to make it work, as I had never worked with array functions before.. I didnt event know how to insert them in excel ! Good stuff
Thank you, Henrik.
I am trying to calculate the running average of the largest 10 out of last 20 values in a ROW (exclude blanks). I have been trying to adapt this formula but not having a lot of luck. Can anyone lead me in the right direction?
=AVERAGE(INDEX(I1:AG1, LARGE(IF($I$1:$AG$1"", MATCH(COLUMN($I$1:$AG$1),COLUMN($I$1:$AG$1)), ""), 10)):INDEX($I1:$AG1,MATCH(1E+307, $I$1:$AG$1)))
Glenn,
The LARGE function excludes blanks automatically, what about this formula?
=AVERAGE(LARGE(I1:AG1,ROW($A$1:$A$10)))
Oscar,
Thanks for the reply!
After rereading my post, I noticed I worded my question wrong I will also use an example from my working spreadshe. This formula looks at the entire ROW of data from C3 to PG3 and averages the last 10 values excluding blanks:
AVERAGE(INDEX(C3:PG3, LARGE(IF($C3:$PG3"", MATCH(COLUMN($C3:$PG3),COLUMN($C3:$PG3)), ""), 10)):INDEX($C3:$PG3,MATCH(1E+307, $C3:$PG3))))
I need the formula to look at the last 20 values, and AVERAGE the SMALLEST 10 values out of those 20. I mistakenly said largest in my original question.
I'm looking at your formula and I don't see where it is looking at the last 20 values.
Thank you again for your help!!