Excel AVERAGE - Free Excel Tutorial

April 14, 2023April 14, 2023

How to Average Only Positive or Negative Numbers of a Range

This post will guide you on how to average only positive or negative numbers of a range in Excel 2013/2016/2019/365.

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative numbers ignored. In this article, we will help you to construct a formula with AVERAGE and IF functions to get average of only positive numbers or negative numbers.

Refer to above left side table, we can see both positive numbers and negative numbers are listed in range A2:E4. We want to calculate average of positive numbers and negative numbers separately and save results in H2 and I2 correspondingly.

In this instance, we will enter below formula into H2. We build this formula with AVERAGE and IF function.

=IF(logical_test,[value_if_true],[value_if_false])

1. Average Only Positive Numbers of a Range

The two functions are used frequently in Excel when running mathematics and logical expression. The AVERAGE function returns the average of numbers from a given range reference. The IF function returns “true value” or “false value” based on the result of provided logical test. It is one of the most popular function in Excel.

In cell H2, enter the formula:

=AVERAGE(IF(A2:E4>0,A2:E4,""))

Range reference A2:E4 represents all numbers in this range.

This formula will execute IF function firstly to filter and keep all positive numbers in current array. If numbers are greater than 0, they are positive numbers. If logical expression “A2:E4>0” is true (number in A2:E4 is greater than 0), this number will be saved in array A2:E4.

After comparing each number in range A2:E4 with 0, below numbers are filtered and saved. Then AVERAGE function will calculate the average of these numbers.

After entering the formula, press Ctrl + Shift + Enter to load result because this is an array formula. But on Excel 365 you can directly press Enter as usual to load result.

2. Average Only Negative Numbers of a Range

In I2, enter the formula:

=AVERAGE(IF(A2:E4<0,A2:E4,""))

Press Ctrl + Shift + Enter to load result.

3. Video: Average Only Positive or Negative Numbers of a Range in Excel

This video will demonstrate how to easily calculate the average of only positive or negative numbers within a range in Excel.

4. Related Functions

Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value],
Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….

April 4, 2023April 4, 2023

How to Count the Average Between Two Dates in Excel

In our daily work, we usually have the problem of counting the average of a period. If the given dates are continuous, we can handle this situation very well, and if the dates are discontinuous, how do we count the average for different periods? In this article, we will use some examples to show you how to count the average for two dates by Average formula in different situations.

1. Count the Average Between Continuous Dates

First prepare a table with given dates and sales.

You can see the given dates are continuous, so we can count average by Average formula directly.

Step1: In B7, enter the below formula to count the average between dates 1/28/2019 and 3/28/2019.

=AVERAGE(B2:B4)

Step2: Click Enter to get the result.

2. Count the Average Between Discontinuous Dates using Formula

If in above table the dates are discontinuous and unordered, how can we count the average of sales? See screenshot below.

You can follow below steps to count the average for two discontinuous dates.

Step1: Prepare another table to save the Start Date, End Date and Average.

Step2: In Start Date enter the start date of the period you want to count the average. In End Date enter the end date of the period you want to count the average. For example, we count the average for period 2/28/2019 and 4/28/2019.

Step3: In Average, enter the formula:

=AVERAGE(IF((A2:A7>=B10)*(A2:A7<=B11),B2:B7))

In above formula, A2:A7 is the given date range, B10 is the start date, B11 is the end date, B2:B7 is the sales range.

Step4: After entering the formula, click Ctrl + Shift + Enter to get the result.

Check the formula bar, you can find the formula is displayed as below. That’s an array formula.

Notes:

1. In step 4, if we just click Enter after entering the formula, then we will get an error here. That’s because above formula is an array formula.

2. If we just want to use a normal formula to count the average and click Enter to get the result, we can use below formula:

 =SUMPRODUCT(--(A2:A7>=B10),--(A2:A7<=B11),B2:B7)/SUMPRODUCT(--(A2:A7>=B10),--(A2:A7<=B11))

We can get the same result.

3. Count the Average Between Discontinuous Dates using User Defined Function with VBA Code

You can also use a User Defined Function with VBA Code to count the average between two given dates in Excel. Just follow these steps:

Step1: Open the Visual Basic Editor (VBE) by pressing Alt + F11 or going to the Developer tab and clicking on Visual Basic.

Adding Comma Character at End of Cells vba1.png

Step2: Insert a new module by going to the Insert menu and selecting Module.

Step3: Write your user defined function in the module using the Function keyword and the desired name, parameters and return value. Save and close the VBE.

How to Count the Average Between Two Dates in Excel vba1.png

Function MyAverage_Excelhow(rng1 As Range, rng2 As Range) As Double

Dim sum As Double
Dim count As Long
Dim i As Long
Dim crit1 As Variant 
Dim crit2 As Variant 

' Check if the ranges have the same size
If rng1.Rows.count <> rng2.Rows.count Or rng1.Columns.count <> rng2.Columns.count Then
    MyAverage = CVErr(xlErrValue) ' Return a #VALUE! error
    Exit Function
End If

' Get the criteria from cells B10 and B11
crit1 = ActiveSheet.Range("B10").Value
crit2 = ActiveSheet.Range("B11").Value

' Loop through the ranges and calculate the sum and count of the values that meet the criteria
For i = 1 To rng1.Cells.count
    If rng1.Cells(i).Value >= crit1 And rng1.Cells(i).Value <= crit2 Then
        sum = sum + rng2.Cells(i).Value
        count = count + 1
    End If
Next i

' Calculate and return the average
MyAverage_Excelhow = sum / count

End Function

Note: you need to change both two criterion variables in cell B10, B11 as you need.

Step4: Go back to Excel and enter a cell where you want to use the function. Type the following formula:

=MyAverage_Excelhow(A2:A7,B2:B7)

Step5: press Enter to apply this formula. And the average result will be returned.

How to Count the Average Between Two Dates in Excel vba2.png

4. Video: Count the Average Between Two Dates

This video will show you how to count the average between two dates in Excel using a simple formula or a VBA code.

5. Related Functions

Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….

April 4, 2023April 4, 2023

How to Calculate Average If Criteria Not Blank/Ignore Blank Cell

In daily work we often need to calculate the average of some numbers based on given conditions or criteria. To calculate average with criteria, we can apply AVERAGEIF of AVERAGEIFS function. AVERAGEIFS function can handle more than one group of criteria range and criteria. In this article, we will show you how to calculate average of numbers whose corresponding cells in another group are not blank. Thus, we need to know some knowledge of AVERAGEIFS function.

In this article, we will let you know the syntax, arguments, and basic usage about AVERAGEIFS function, and apply it to build a formula, also let you know the calculation steps of the formula.

1. EXAMPLE

How to Calculate Average If Criteria Not Blank 1

Refer to “Score” column, some numbers are listed in range B2:B10. “Level” column is used for providing a level based on scores. In E2 and F2, we calculate the average of all scores and qualified scores separately. To calculate average of all scores, we can directly use AVERAGE function. To calculate average of qualified scores (cell is not blank in Level group), we need to add criteria to filter them, we can apply AVERAGEIFS function, it is also one of the most common used functions in Excel.

First, In C2, enter “=AVERAGE(Score)”. Then press Enter, AVERAGE function returns 73.33333333.

How to Calculate Average If Criteria Not Blank 2

You can adjust decimal places by “Increase Decimal” or “Decrease Decimal” in “Number” section.

How to Calculate Average If Criteria Not Blank 11

Keep clicking on “Decrease Decimal” button till decimal places is ok for you. Keep two decimal places in this case.

Now we can apply AVERGAEIFS function to calculate the average of qualified scores.

Before creating the formula, name range “B2:B10” to “Score”, “C2:C10” to “Level” in Name Box.

How to Calculate Average If Criteria Not Blank 4

2. CREATE A FORMULA with AVERAGEIF FUNCTION

Step 1: In F2, enter the formula:

 =AVERAGEIFS(Score,Level,"<>").

How to Calculate Average If Criteria Not Blank 5

Step 2: Press Enter after typing the formula.

How to Calculate Average If Criteria Not Blank 6

Only B2, B4, B5, B6, B8, B9 and B10 meet our given criteria “level is A or B or C”, so we calculate the average of numbers in above cells, total 7 numbers. (60+62+90+83+88+74+90)/7=78.14285714, keep two decimal places 78.14. The formula works correctly.

a. FUNCTION INTRODUCTION

AVERAGEIFS function is AVERAGE+IFS. It returns the average of some numbers in a range based on one or more given conditions or criteria.

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2],…)

It supports wildcards like asterisk ‘*’ and question mark ‘?’, also supports logical expressions like ‘>=’,’<=’. If wildcards or logical operators are required to build criteria, they should be enclosed into double quotes (““). In this case we entered “<>” to represent criteria.

b. EXPLANATION

After expanding values, the formula is displayed as:

=AVERAGEIFS({60;55;62;90;83;58;88;74;90},{"C";0;"C";"A";"B";0;"A";"C";"A"},"<>")

Note: 0 is recorded in this array to represent blank cells.

In the formula, the criteria is “<>” (“not equals to” operator), based on this criteria, cells without any level in “Level” column cannot meet our condition, so for the corresponding values in the average range, they are excluded in calculation.

{60;55;62;90;83;58;88;74;90} -> {60;0;62;90;83;0;88;74;90} – Ignore blank cells

Now this new array only contains numbers. We can calculate the average now (60+62+90+83+88+74+90)/7=78.14.

3. Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
Excel AVERAGEIFS function
The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

March 28, 2023March 28, 2023

How to Calculate Average Ignore Non-Numeric Values and Errors

In daily work we often need to calculate the average of some numbers in a range for further analysis. Thus, we need to know some basic functions of calculate average in Excel. From now on, we will introduce you some basic functions like AVERAGE, AVERAGEIF, and show you some common formulas to calculate average value.

In this article, we will let you know how to calculate average by AVERAGE function easily and the way to calculate average based on a given criteria by AVERAGEIF function. We will introduce you the syntax, arguments, and basic usage about the two functions, and let you know the working process of our created formulas.

1. EXAMPLE

How to Calculate Average Ignore Non-Numeric Values and Errors 1

Refer to “Numbers” column, some numbers are listed in range A2:A11. C2 is used for entering a formula which can calculate the average of numbers in range A2:A11. In fact, Excel has amount of built-in functions and they can execute most simple calculations properly, and AVERAGE is one of the most common used functions.

In C2, enter “=AVERAGE(A2:A11)”.

How to Calculate Average Ignore Non-Numeric Values and Errors 2

Then press Enter, AVERAGE function returns 63.8.

How to Calculate Average Ignore Non-Numeric Values and Errors 4

You can adjust decimal places by “Increase Decimal” or “Decrease Decimal” in “Number” section.

But if some errors or blank or non-numeric values exist in the list, how can we only calculate average for numbers ignoring the invalid values? See example below.

How to Calculate Average Ignore Non-Numeric Values and Errors 5

We get an error when calculating average for range A2:A11, the difference is there are some invalid values in the list. To calculate average ignoring the errors, that means we need to calculate average of numbers in a range with one or more criteria, thus, we can apply AVERGAEIF function instead of basic AVERAGE function.

2. CREATE A FORMULA with AVERAGEIF FUNCTION

Step1: In C2, enter the formula =AVERAGEIF(A2:A11,”>0″).

How to Calculate Average Ignore Non-Numeric Values and Errors 6

You can also name range “Numbers” for A2:A11, then you can enter =AVERAGEIF(Numbers,”>0″).

Step2: Press Enter after typing the formula.

How to Calculate Average Ignore Non-Numeric Values and Errors 7

Ignore the improper cells, and only calculate the average of numbers 54, 40, 88, 76, 100, 90 and 44, total 7 numbers. (54+40+88+76+100+90+44)/7=70.3. The formula works correctly.

a. FUNCTION INTRODUCTION

AVERAGEIF function is AVERAGE+IF. It returns the average of some numbers in a range based on given criteria.

Syntax:

=AVERAGEIF(range, criteria, [average_range])

It supports wildcards like asterisk ‘*’ and question mark ‘?’, also supports logical operators like ‘>’,’<’. If wildcards or logical operators are required, they should be enclosed into double quotes (““), in this case we entered “>0” to show the criteria.

AVERAGEIF – RANGE

This example is very simple, we have only one list. A2:A11 is criteria range and average range.

In the formula bar, select “A2:A11”, press F9, values in this range are expanded in an array.

How to Calculate Average Ignore Non-Numeric Values and Errors 8

If average range=criteria range, average range can be omitted.

AVERAGEIF – CRITERIA

Obviously, the criteria in our case is “>0”, this condition can filter numbers from criteria range.

b. HOW THE FORMULA WORKS

After expanding values, the formula is displayed as:

=AVERAGEIF({54;40;#N/A;88;"ABC";76;0;100;90;44},">0")

Note: blank cell is recorded as 0 in the array.

Because of criteria “>0”, so invalid cells are ignored. Replace all invalid values with 0. Keep all numbers.

{54;40;#N/A;88;"ABC";76;0;100;90;44} -> {54;40;0;88;0;76;0;100;90;44}

Now this new array only contains numbers. We can calculate the average now.

3. Calculate Average Ignore Non-Numeric Values and Errors with VBA Code

You can also create a User Defined Function to calculate the average of a range of cells, ignoring any non-numeric values or error values, just do the following steps:

Step1: Click on the “Visual Basic” button in the Developer tab to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, click on “Insert” in the menu and select “Module” to create a new module.

Step3: Paste the VBA code provided in the new module.

VBA Calculate Average Ignore Non-Numeric Values and Errors1.png

Function AverageIgnoreNonNumeric(rng As Range) As Variant
    Dim cell As Range
    Dim sum As Double
    Dim count As Long
    
    sum = 0
    count = 0
    
    For Each cell In rng
        If IsNumeric(cell.Value) And Not IsError(cell.Value) And Not IsEmpty(cell.Value) Then
            sum = sum + cell.Value
            count = count + 1
        End If
    Next cell
    
    If count > 0 Then
        AverageIgnoreNonNumeric = sum / count
    Else
        AverageIgnoreNonNumeric = CVErr(xlErrNA)
    End If
End Function

Step4: Save the workbook as a macro-enabled workbook by selecting “Excel Macro-Enabled Workbook” from the “Save as type” drop-down menu.

Step5: Close the Visual Basic Editor and return to the Excel worksheet.

Step6: Enter the below formula in a blank cell where you want to calculate the average

=AverageIgnoreNonNumeric(A2:A11)

Step7: Press “Enter” to calculate the average ignoring non-numeric values.

VBA Calculate Average Ignore Non-Numeric Values and Errors2.png

4. Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….

March 26, 2023March 26, 2023

How to Average the Last N Values in Excel

In Excel there are a lot of built-in functions, and AVERAGE is one of the most frequently used functions. We can apply it to calculate the average of numbers from a given range reference. In daily work, we may apply AVERAGE function together with some other functions to get average based on some conditions. For example, get the average of sales in the nearest 3 days. In this article, we will let you know the method to apply AVERAGE function together with OFFSET function and COUNT function to get the average of last N values from a dynamic range.

In this article, we will introduce you the syntax, arguments, and basic usage of above three functions. We will also explain how the formula works with these functions.

1. Average the Last N Values using Formula

a. EXAMPLE

In this case, we want to calculate the average of the last 3 values in “Amount” column. If “Amount” range is a fixed range, we can directly create formula “=AVERAGE(C4:C6)” to get the average of last 3 values. If this range is a dynamic range, for example add a new line for date 5/31/2021, the last 3 values are changed due to a new row is inserted. So, the simple formula with only AVERAGE function doesn’t work.

To get the average of last N values for a dynamic range, we can apply AVERAGE function together with other functions to help us. In this article, to approach our goal, we also apply OFFSET function together with COUNT function to help us set our average range.

b. FORMULA with AVERAGE & OFFSET & COUNT FUNCTIONS

In E2, enter the formula:

=AVERAGE(OFFSET(C1,COUNT(C:C),0,-3))

Then press Enter, average of last three amounts is $6166.67.

(5500+4000+9000)/3=6166.666666, keep two decimal places 6166.67. The formula works correctly.

c. FUNCTION INTRODUCTION

AVERAGE function returns the average of numbers from a given range reference.

Syntax:

=AVERAGE(number1, [number2], …)

Usage:

OFFSET returns a number or a dynamic range based on given five parameters: starting point, row offset, column offset, height in rows and width in rows.

Syntax:

=OFFSET(reference, rows, cols, [height], [width])

Usage 1:

Reference: A1, it is the starting point

Rows: 3, 3 rows below the starting point

Cols: 2, 2 columns to offset to the right of the starting point

Height & Width: optional, no value

Usage 2:

Reference: A1, it is the starting point

Rows: 3, 3 rows below the starting point

Cols: 2, 2 columns to offset to the right of the starting point

Height: 2, it is used together with width, determine the size of the returned range reference, in this case is 2*2 (two rows*two columns)

Width: 2, it is used together with height, determine the size of the returned range reference, in this case is 2*2 (two rows*two columns)

COUNT returns the count of numbers from a given range.

Syntax:

 =COUNT(value1, [value2], …)

Usage:

d. EXPLANATION

=AVERAGE(OFFSET(C1,COUNT(C:C),0,-3))

In this formula, C:C is a full column reference, it represents the entire C column; if user want to cover entire C, D and E columns, we can enter C:E to represent multiple columns, just enter the start column index and end column index, use colon “:” to concentrate them. A full row reference is similar with a full column reference, for example, 1:3 means the first three entire rows. We often use full column or row reference when the selected range is a dynamic range, and user may add or delete column or row at any moment.

In this case, COUNT(C:C) returns 5, there are five cells with numbers in column C.

Now, for OFFSET part, the five inputs are:

Reference: C1 (header of column C, it will not be included when counting row and columns)

Rows: 5. 5 rows below the starting point. The returned range is started in row 6.

Cols: 0. No column to offset to the starting point.

Based on above “Rows” and “Cols” two parameters, we can confirm one point of our returned range, it is C6.

Height: -3. It represents that the returned range is 3 rows in height. Currently, Excel OFFSET function can handle the case height and width are negative numbers. In this case, height value is “-3”, so from C6, OFFSET function expands the range 3 rows backwards to the starting point C1.

Width: No value. Width value is optional for OFFSET function. If it is omitted, the returned range is only limited in current column.

Based on above “Height” and “Width”, we can confirm the returned range, it is C4:C6.

Now, =AVERAGE(OFFSET(C1,COUNT(C:C),0,-3)) is equal to =AVERAGE(C4:C6). After above steps, we get correct value 6166.67.

2. Average the Last N Values with VBA Code

If you want to average the last N values in Excel using a user-defined function with VBA code, you can follow these steps:

Step1: Press ALT+F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, select Insert > Module to create a new module.

Step3: Paste the following code into the module:

How to Average the Last N Values in Excel vba1.png

Function AvgLastN_ExcelHow(RangeToAverage As Range, N As Integer) As Double
    Dim i As Integer
    Dim sum As Double
    Dim count As Integer
    
    count = 0
    
    For i = RangeToAverage.Cells.count To (RangeToAverage.Cells.count - N + 1) Step -1
        sum = sum + RangeToAverage(i).Value
        count = count + 1
    Next i
    
    AvgLastN_ExcelHow = sum / count
End Function

Step4: Save the module and close the Visual Basic Editor.

Step5: Type the following formula into a blank cell.

=AvgLastN_ExcelHow(C2:C6,3)

Where C2:C6 is the range of cells that you want to average, and 3 is the number of values you want to include in the average.

Step6: Press Enter to calculate the average.

How to Average the Last N Values in Excel vba2.png

This custom function will calculate the average of the last 3 values in the specified range.

3. Video: Average the Last N Values

This video will demonstrate how to use a formula and VBA code to calculate the average of the last N values in Excel.

4. Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel COUNT function
The Excel COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…

February 26, 2023February 26, 2023

How to Calculate Average Ignore Blank and Zero Cells in Excel

Sometimes we need to calculate the average for a list or a range of numbers, but blank cells and zero cells may be also included in the selected range, so if these invalid cells are included, we often get error when calculating the average.

This post will guide you on how to calculate average while ignoring blank and zero cells in Excel. By default, Excel’s AVERAGE function includes all cells in the specified range, including blank cells and cells that contain zero values. However, you can use formulas such as AVERAGEIF or AVERAGEIFS to exclude these cells from the calculation.

1. Video: Calculate Average Ignore Blank and Zero Cells in Excel

In this video, you’ll learn how to use the AVERAGEIF formula to calculate the average while ignoring blank or zero cells in Excel.

2. Average Cells in Excel

If you only want to average a range of cell in Excel, and you can use the AVERAGE function. Just type the following formula:

=AVERAGE(A1:C5)

How to Calculate Average Ignore Blank and Zero Cells in Excel 11.png

This formula will calculate the average of all the cells in the given range, including blank cells and cells that contain zero values.

3. Calculate Average Ignore Blank and Zero Cells by Formula

Precondition:

Prepare below table. There are some zero cells and blank cells. We will ignore them when calculating the average.

How to Calculate Average Ignore Blank and Zero Cells in Excel1.png

This is the most convenient way to calculate the average for selected range without zero and blank cells. You can use the AVERAGEIF function with a criteria that excludes blank and zero cells. Just do the following steps:

Step 1: Select a blank cell where you want to display the last average result, for example E2.

Step2: Enter the following formula in the formula bar.

 =AVERAGEIF(A1:C5,"<>0").

How to Calculate Average Ignore Blank and Zero Cells in Excel2.png

Step 3: Press Enter to get the average. Verify that average is calculated properly.

How to Calculate Average Ignore Blank and Zero Cells in Excel3.png

This formula will exclude blank cells and cells that contain a zero value from the calculation.

4. Calculate Average Ignore Blank Only

Excel can calculate the average ignore the blank cells automatically. Zero numbers are still included. You can use the AVERAGEIF function with the criteria “<>” or AVERAGE function only to exclude blank cells.

Type one of the below formula in a blank cell where you want to display the average result:

=AVERAGE(A1:C5)

=AVERAGEIF(A1:C5,"<>")

How to Calculate Average Ignore Blank and Zero Cells in Excel 10.png

You may think if we remove all zero values and left them as blank cells, then we can use AVERAGE function directly without other index or parameter. Well, if you remove them totally, you can apply AVERAGE function directly, but, if you just make these zero numbers invisible in the table, you may get improper value by formula.

See example below:

Step1: Click File (it is adjacent to Home) in the ribbon, then select Options to enter Excel Options window.

How to Calculate Average Ignore Blank and Zero Cells in Excel5.png

Step2: On Excel Options window, in the left panel, click Advanced, then drag the scrollbar to the middle, under Display options for this worksheet, uncheck ‘Show a zero in cells that have zero value’ option. Then click OK.

How to Calculate Average Ignore Blank and Zero Cells in Excel6.png

Step3: Then you will find that zero value is ‘removed’ from your table. Only blank cells exist now.

How to Calculate Average Ignore Blank and Zero Cells in Excel7.png

Step4: In E2 enter the formula =AVERAGE(A1:C5). Then press Enter to check the result.

How to Calculate Average Ignore Blank and Zero Cells in Excel8.png

See, we still get the average with zero included. So, you cannot apply AVERAGE function without zero cells by disable showing them in the table.

5. Ignore or Hide Blank Cells in Excel Pivot Table

You can also filter out the blank cells or rows in a pivot table by using the filter options.

You just need to click on the drop-down arrow next to the column or row header that you want to filter, and then uncheck the “(blank)” option in the filter menu.

This will hide all the cells or rows that have blank values in that field.

If you want to filter out multiple columns or rows, you can use the “Filter” or ” Insert Slicer” buttons on the ribbon to apply more advanced filters to your pivot table.

6. Conclusion

In your daily work, excluding blank and zero cells when calculating averages is an very useful task that can save you time and provide more accurate results by the above steps in my article.

Just enjoy it.

April 14, 2022

Calculate Average Of Last 5 Or N Values In Columns

Suppose you come across a task where you need to calculate the average of the last 2 or 3 numeric values, then what would you do?

If you are new to Excel, then your first attempt might be doing this task manually, which is an acceptable way but only when the values of which you want to calculate the average is limited to 2 or 3 but when it comes to calculating the last 5 or N values in the columns then it becomes nearly impossible to do this cumbersome task on time!

Don’t worry about it because after reading this article, you will know the easiest way to calculate the average of the last 5 or N values from the columns within seconds.

So, let’s dive into the article.

General Formula

To get the average of the last N values, use the formula below.

=AVERAGE(OFFSET(first cell,0,COUNT(range_values)-N,1,N))

Explanations For Syntax:

AVERAGE: In Excel, the AVERAGE Function may be used to calculate the arithmetic mean of a set of integers.
OFFSET: This Function outputs a reference to a range made up of pieces such as a beginning point, a row, and column offset, and a final height and width in rows and columns. Learn more about the OFFSET Function.
COUNT: The COUNT Function returns the result as a number and counts the number of cells that contain numbers.
First Cell: It indicates the first cell in the provided input range.
Range: This is the input value from your MS Excel spreadsheet.
The comma sign (,): is a separator that separates a list of values.
Parenthesis (): The main Function of this symbol is to group the elements.
Minus Operator (-): This symbol subtracts 2 values.

Explanation

To average the latest 5 data values in a range of columns, use the AVERAGE Function conjunction with the COUNT and OFFSET functions. The formula in I2 in the example is as follows:

=AVERAGE(OFFSET(A2,0,COUNT(A2:G2)-5,1,5))

The OFFSET function may create dynamic ranges from a beginning cell and specify rows, columns, height, and width.

The rows and columns parameters act as “offsets” from the beginning reference. The height and width parameters, all optional, decide how many rows and columns are included in the final range. We want the OFFSET function to return a range that starts at the last item and grows “backward,” therefore we provide the following arguments:

A2 is the initial reference — it is the cell directly to the right of the formula and the first cell in the range of values we are dealing with.

Rows – We use 0 for the rows option because we want to stay in the same row.

Columns – We use the COUNT function to count all values in the range for the columns input, then deduct 5. This moves the start of the range of 5 columns to the left.

Height – we use 1 since we want a 1-row range as the end result.

Width – we pick 5 since we want a final range with 5 columns.

For the formula in I2, OFFSET yields a final range of C2:G2. This is sent to the AVERAGE Function, which returns the average of the five values in the range.

To understand better, consider the following example, which follows a step-by-step procedure:

Consider the following example to calculate the average of the last 5 values.
This illustration will provide input data from Column A to Column G.
Next, enter the supplied formula in the formula bar section.
Finally, we shall obtain the result in the selected cell I2.

Less Than 5 Values Average

If you are want to compute the average of the last 2, 3, or 4 data, you don’t need to do it manually. Utilizing the formula discussed above will result in a circular reference mistake when computing the average of the 2, 3, or 4 values in the columns; the range will extend back into the cell that contains the formula. To avoid this mistake and complete your task, modify the formula as follows:

=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))

So If you want to average last N values in columns that less than 5 values, you can use the following formula:

=AVERAGE(OFFSET(A2,0,COUNT(A2:C2)-MIN(5,COUNT(A2:C2)),1,MIN(5,COUNT(A2:C2))))

In this case, we utilize the MIN function to “catch” cases where there are fewer than 5 values and the real count when there are.

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel COUNT function
The Excel COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…
Excel MIN function
The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….

April 12, 2022April 13, 2022

Calculating Average Of The Numbers

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed on the right article.

In this article, you will get to know the smarter ways to do these cumbersome tasks in a matter of seconds.

So without any delay, let’s dive into the article,

Average Of The Numbers By Including 0

General Formula

Use the formula below to calculate the average of a set of numbers in Excel:

=AVERAGE(range)

Explanations for Syntax:

AVERAGE: In Excel, the AVERAGE Function can calculate the arithmetic mean of a set of numbers.
Range: This is the input value from your worksheet.
Comma symbol (,): It acts as a separator that helps to separate a list of values.
Parenthesis (): The main Function of this symbol is to group the elements.

Explanation

Use the AVERAGE function to calculate the average of a set of numbers.

In the example, the formula in E3 is:

=AVERAGE(A3:C3)

which is then copied down in the table.

AVERAGE is a built-in function in MS Excel in which you supply a range of cells to average, and this function returns the result. In cases where cells are not adjacent, you can also provide individual arguments to the Function.

The AVERAGE function automatically ignores all the blank text and cells, but zero values are included.

Average Of The Numbers By Ignoring 0

General Formula

The formula below will assist you in calculating the average of a number that excludes zero:

=AVERAGEIF(range,"<>0")

Explanations for Syntax:

AVERAGEIF: This Function returns the average of a set of input values that satisfy a single condition or criteria. Learn more about the AVERAGEIF Function.

Range: This is the input value from your worksheet.

Comma symbol (,): It acts as a separator that helps to separate a list of values.

Parenthesis (): The main Function of this symbol is to group the elements.

Operator (>): The supplied criteria is “>0,” which means “not equal to zero.”

Explanation

The AVERAGEIF function computes the average of a set of numbers while excluding or ignoring zero values. In the example, the formula in E10 is:

=AVERAGEIF(A10:C10,"<>0")

The formula in E3 in the example is based on the AVERAGE Function:

=AVERAGE(A3:C3)

Because (129+299+0)/ 3 = 166, the answer is 166.

To remove the zero from the calculated average, the formula in E10 employs the AVERAGEIF function, as shown below:

=AVERAGEIF(A10:C10,"<>0") / returns 249

The provided criterion is “>0,” which means “not equal to zero”.

Data with no values

Because the AVERAGE, AVERAGEIF, and AVERAGEIFS functions all ignore blank cells (and cells with text values), there isn’t any need to provide criteria to filter out empty cells.

Average Of The Top 3 Numbers

General Formula

The formula below will assist you in calculating the average of the top 3 numbers:

=AVERAGE(LARGE(range,{1,2,3}))

Explanations for Syntax:

AVERAGE: In Excel, the AVERAGE Function can calculate the arithmetic mean of a set of numbers.
Range: This is the input value from your worksheet.
Comma symbol (,): It acts as a separator that helps to separate a list of values.
Parenthesis (): The main Function of this symbol is to group the elements.
LARGE: This Function returns the Nth largest value from the given range of data. Learn more about the LARGE Function.
Values: This is the range of cells that contain values.

Explanation

To average the top three values in a data set, use a formula based on the LARGE and AVERAGE functions. In this example, the formula in G18 is mentioned below:

=AVERAGE(LARGE(A18:E18,{1,2,3}))

The LARGE function is used to find the top nth value in a set of numbers. For example, the LARGE(A18:E18,{1,2,3})will return the highest value, LARGE(A18:E18,2) will return the second-highest value, and so on:

=LARGE(A18:E18,1)/ First largest value
=LARGE(A18:E18,2)/ 2nd largest value
=LARGE(A18:E18,3)/ 2nd largest value

In this case, we are requesting multiple values by passing an array constant 1,2,3 into LARGE as the second argument. This causes LARGE to return an array of the top three values.

=LARGE(A18:E18,{1,2,3})

returns an array similar to this:

{299,199,54}

This array is passed directly to the AVERAGE function:

=AVERAGE(299,199,54) / returns 184

The AVERAGE Function then returns the average of these values.

Because the AVERAGE Function can handle arrays natively, there is no need to enter this formula with control + shift + enter.

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
Excel LARGE function
The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…

June 8, 2021

Average per Week by Formula in Excel

We usually apply AVERAGE function or relevant functions to return average directly in Excel worksheet. But in some situations, only applying average relevant functions cannot figure out our problem. Sometimes we can create a formula with functions and mathematical operation inside to calculate average depends on different situations.

In this article, through explaining the example below, we will introduce you to apply SUMPRODUCT and COUNTIF functions together to calculate average. You can get the basic knowledge and usage of these two functions in this article.

EXAMPLE

The unit for “Amount” is pound. The unit for “Unit Price” is one dollar a pound. From the table we can know that unit prices are fluctuant every week. In different weeks, we bought different amounts of item per our demands. For example, on week-1, we bought 10 pounds with a unit price $9.99, but on week-3, we didn’t pay for this item. Now, we want to know the average of our consumption per week, and the average of unit price we cost for five weeks.

In this article, to approach our goal, we will apply SUMPRODUCT function and COUNTIF function, and use an expression =SUMPRODUCT()/COUNTIF() to get the average of total price per week; and apply SUMPRODUCT function and SUM function, and expression =SUMPRODUCT()/SUM() to get the average of unit price for one pound during five weeks.

ANALYSIS

To calculate “average of total price per week”:

a. In mathematics, the general formula to figure out this problem is “Average=(Total price of week1 + Total price of week2 + … + total price of week N)/N weeks”.

b. To calculate the total price of each week, we can create a simple formula that multiplies amount by unit price, for example, for week1, total price is B2*B3.

c The total price of all five weeks is equal to (B2*B3+C2*C3+…+F2*F3). Actually, this operation can be seen as two arrays multiplication that “values from range B2:F2 multiply the values from range B3:F3” correspondingly, then sum all products properly. Till now, we are sure we can apply Excel Math function “SUMPRODUCT” here to sum total price of five weeks simply.

d. For number of weeks, as there is no consumption on week-3, so week-3 should be excluded when counting number of weeks. As D2 is blank, so we can apply COUNT function directly to count weeks as COUNT function can ignore blank cell automatically. To make sense, we apply COUNTIF function instead of COUNT We want to count weeks with consumption, so amount should be greater than 0. To count with one condition or criterion, we choose COUNTIF function here.

e. Then we can create a formula =SUMPRODUCT(B2:F2*B3:F3)/COUNTIF(B2:F2,”>0”) to return average.

To calculate “average of unit price per pound”:

a. In mathematics, the general formula to figure out this problem is “Average=(Total price of week1 + Total price of week2 + … + total price of week N)/Total amounts”. We can repeat above first three steps to get sum of total prices.

b. For total amounts, just apply SUM function which is one of the most frequently used math functions in Excel.

c. Then we can create a formula =SUMPRODUCT(B2:F2*B3:F3)/SUM(B2:F2) to return average.

FORMULA

Input formula =SUMPRODUCT(B2:F2*B3:F3)/COUNTIF(B2:F2,”>0″) into H2 and press Enter, verify that 86.46 is returned and shown in H2 properly. (10*9.99+9*9.79+8*8.99+10*8.59)/4=86.46. The return value is correct.

Notice: actually, the returned value may be a number with more than two decimal places (based on your settings), you can click “Decrease Decimal” in “Number” section under Home tab to shorten decimal places and keep only two.

Input formula =SUMPRODUCT(B2:F2*B3:F3)/SUM(B2:F2) into H4 and press Enter, verify that 9.35 is returned and shown in H4 properly. (10*9.99+9*9.79+8*8.99+10*8.59)/ (10+9+8+10)=9.35. The return value is correct.

FUNCTION INTRODUCTION

a. SUMPRODUCT function returns the sum of the products of multiplication of multiple corresponding arrays or ranges.

Syntax:

=SUMPRODUCT(array1, [array2], [array3],…)

Example.

b. COUNTIF returns the number of cells which contain a number and the number meets one condition or criterion. The condition or criterion is determined by our input in “criteria” argument.

Syntax:

 =COUNTIF(range, criteria)

Example.

c. SUM function returns sum of a set of numbers.

Syntax:

=SUM(number1,[number2], …)

Example.

EXPLANATION

=SUMPRODUCT(B2:F2*B3:F3)/COUNTIF(B2:F2,">0")

// for SUMPRODUCT, array1 is B2:F2*B3:F3

// for COUNTIF, range is B2:F2, criteria “>0”

a. SUMPRODUCT(B2:F2*B3:F3) returns the sum of total prices of all five weeks. Expand values in B2:F2 and B3:F3, we get below expression:

=SUMPRODUCT({10,9,0,8,10}*{9.99,9.79,9.69,8.99,8.59}) //

returns 345.83

b. COUNTIF(B2:F2,”>0″) returns number of weeks with consumption. Expand values from range B2:F2, we get below expression:

=COUNTIF({10,9,0,8,10},">0")//

returns “4”

c. Above all, =SUMPRODUCT(B2:F2*B3:F3)/COUNTIF(B2:F2,”>0″) returns 345.83/4=86.46.

=SUMPRODUCT(B2:F2*B3:F3)/SUM(B2:F2)

// for SUMPRODUCT, array1 is B2:F2*B3:F3

// for SUM, numbers are saved in B2:F2

a. SUM(B2:F2) returns the sum of total amounts of all five weeks. Expand values in B2:F2, we get below expression:

=SUM({10,9,0,8,10}) //

returns 37

b. As SUMPRODUCT function returns 345.83, so =SUMPRODUCT(B2:F2*B3:F3)/COUNTIF(B2:F2,”>0″) returns 345.83/37=9.35.

Related Functions

Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….

June 5, 2021June 7, 2021

Average with Multiple Criteria in Excel

AVERAGEIFS function is one of the most popular functions in Excel. Apply AVERAGEIFS function, we can calculate average simply if there are multiple conditions.

In this article, we will introduce you to apply AVERAGEIFS function to calculate average of a set of numbers which are filtered by multiple criteria. You can get the basic knowledge and usage of AVERAGEIFS function in this article.

EXAMPLE

In this case, we want to calculate the average of sales based on different fruit in different week. Obviously, there are two criteria in this case, one is “kind of fruit”, the other one is “week number”. For example, to get average for “apple” in “week1”, we need to look up values from “Sales” which corresponding fruit and week number can satisfy above two conditions.

In this article, to approach our goal, we can apply AVERAGEIFS function.

SOLUTION

As there are two conditions in this case, so we need to choose a function that can handle multiple conditions, and at the same time can execute average calculation, so AVERAGEIFS is the best choice. If there is only one condition, we can also apply AVERAGEIF function.

FORMULA with AVERAGEIFS FUNCTION

In G2, we input the formula =AVERAGEIFS(Sales,Fruit,E2,Week,F2). After typing, press Enter, average of sales for different fruit in different weeks is returned properly.

Three values in “Sales” can meet our condition “Fruit=Apple & Week Number=1”, there are C2-100, C3-200, C4-150, so the average is (100+200+150)/3=150, the formula returns correct value.

Drag down handle to fill other cells with the same formula in Average column.

FUNCTION INTRODUCTION

In this instance we only apply one function, it is “AVERAGEIFS”, it is one of the most popular functions in Excel. It can be seen as AVERAGE and IFs, similar with SUMIFS, AVERAGIFS can handle multiple criteria when calculating average.

AVERGAEIFS function returns the average of a set of numbers in range based on one or more given conditions or criteria.

Syntax:

=AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

For AVERAGEIFS functions, it can handle wildcards like asterisk ‘*’ and question mark ‘?’; it also supports logical operations like ‘>=’,’<=’. If we need entering wildcards or logical operators to build criteria, they should be enclosed into double quotes (““).

Example.

FORMULA EXPLANATION

=AVERAGEIFS(Sales,Fruit,E2,Week,F2)

// average_range is named range “Sales”.

//criteria_range1 is named range “Fruit”, criteria1 is value in E2.

//criteria_range2 is named range “Week”, criteria2 is value in F2.

a. For the first group of criteria range and criteria, expand values in “Fruit” and “E2”:

{"Apple";"Apple";"Apple";"Banana";"Banana";"Banana";"Apple";"Apple";"Apple";"Banana";"Banana";"Banana"},"Apple"

Filter data from “Fruit” refer to criteria1 “Fruit=Apple”:

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}

b. For the second group of criteria range and criteria, expand values in “Week” and “F2”:

{1;1;1;1;1;1;2;2;2;2;2;2},1

Filter data from “Week” refer to criteria2 “Week=1”:

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

c. After filtering data from two groups, we can notice that only the first three positions in both two arrays are “TRUE”. Thus we only need to extract the first three values from average range and then come to AVERAGE calculation.

{100;200;150}

After above all steps, we get =AVERAGE({100;200;150}). So, the returned value is (100+200+150)/3=150.

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
Excel AVERAGEIFS function
The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

June 4, 2021

Average of Top N Values in Excel

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average properly based on different situations.

In this article, we will introduce you the way to calculate average of the top N numbers from a range. To approach our goal, except the main AVERAGE function, we also apply LARGE function.

EXAMPLE

In this case, we want to calculate the average of the top three scores from 5-rounds competitions for each student. To calculate the average of top N values, we need the help of LARGE function.

SOLUTION

To create a formula to get average in this case, we need to know:

1) Find out the top three values by LARGE function.

2) Calculate average of the top three values by AVERAGE function.

The general formula is =AVERAGE(LARGE(Range,{1,2,…,N})).

FORMULA with AVERAGE & OTHER FUNCTIONS

In C2, we input the formula =AVERAGE(LARGE(B2:F2,{1,2,3})). After typing, press Enter, then average “98” of the top three scores is returned.

For Kara, five scores are saved in range B2:F2, the top three values are 100, 96 and 98, so the average is (100+96+98)/3=98, the formula returns correct result.

FUNCTION INTRODUCTION

The main function in this formula is “AVERAGE”, it returns the average of the top three values by our demands; “LARGE” is used for searching for the top three values among all given values.

a. LARGE function returns the Kth largest number from a given set of numbers or a range reference.

Syntax:

=LARGE(array,k)

Example:

//If we input 2 for, only the second top value is returned;

//If we want to returning more than one top value, we can set an array constant as the function’s second argument;

//If we input {1;2} for k, 1 and 2 are separated by “;”, the top two values are saved in the same column;

//If we input {1,2} for k, 1 and 2 are separated by “,”, the top two values are saved in the same row.

b. AVERAGE function returns the average of numbers from a given range reference.

Syntax:

=AVERAGE(number1, [number2], …)

Example:

//For arguments “number1, number2,…”, they can be a set of numbers, or an array of numbers like {1;2;3}.

FORMULA EXPLANATION

=AVERAGE(LARGE(B2:F2,{1,2,3}))

//This formula contains 2 functions, we explain functions from inside to outside.

a. For LARGE(B2:F2,{1,2,3}), range B2:F2 provides all scores from 5 rounds competitions; array constant {1,2,3} is the second argument “k” value, it determines the count of top numbers returned by LARGE.

In this case, B2:F2 is {}, LARGE returns below array:

LARGE({100,90,95,96,98},{1,2,3}) -> {100,98,96}

b. Array {100,98,96} from last step goes into AVERAGE calculation now.

=AVERAGE({100,98,96})

So, we get 98 properly after above two steps.

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel LARGE function
The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…

June 3, 2021June 4, 2021

Average the Last N Numeric Values in Excel

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations.

In this article, we will introduce you to calculate average of the last N numbers from a range contains both numeric values and non-numeric values.

EXAMPLE

Average the Last N Numeric Values 7

In this case, we want to calculate the average of the last 3 numeric values in “Numbers” column. As non-numeric value “ABC” exists in last three cells, so we cannot directly apply AVERAGE(A9:A11) directly. To calculate average ignoring invalid values, we need the help of other functions.

In this article, to approach our goal, except the main AVERAGE function, we also apply ROW, ISNUMBER, IF, LARGE, LOOKUP functions.

SOLUTION

To create a formula to get average in this case, we need to know:

1) Distinguish numbers and non-numeric values from range “Numbers”. Ignore non-numeric values in calculation.

2) Find out the last three cells with numbers in “Numbers”.

3) Find out last three values through searching for the last three positions.

FORMULA with AVERAGE & OTHER FUNCTIONS

In C2, we input the formula

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(Numbers),ROW(Numbers)),{1,2,3}), ROW(Numbers), Numbers)).

After typing, press Enter, average of the last three numbers is 12.

Average the Last N Numeric Values 7

In “Numbers” list, the last three values are 20, 15 and 1, the average is (20+15+1)/3=12, so the formula returned value is correct. The last three values are found out properly.

FUNCTION INTRODUCTION

The main function in this formula is “AVERAGE”, it can return the average of last three values; Others are supported to find out numbers (ignoring non-numeric values), mark row numbers for the last three numbers, and the through searching row numbers to return corresponding numbers.

1. ROW function returns row number for a given range reference.

Syntax:

=ROW(reference)

Example. ROW(A1:A3) returns row numbers of range A1:A3, so we get {1;2;3}.

Average the Last N Numeric Values 7

2. ISNUMBER function returns True (for numeric values) or False (for strings, errors) based on value is a numeric value or not. For blank cells, it returns False.

Syntax:

=ISNUMBER(value)

Example. You can input a value, a cell reference, or a range reference for “value”.

Average the Last N Numeric Values 7

3. IF function returns “true value” or “false value” based on the result of provided logical comparison. It is one of the most popular function in Excel.

Syntax:

=IF(logical_test,[value_if_true],[value_if_false])

Example, =IF(ISNUMBER(E1:E3),”Yes”,”No”).

Average the Last N Numeric Values 7

4. LARGE function returns the Kth largest number from a given set of numbers or a range reference.

Syntax:

=LARGE(array,k)

Example, =LARGE(E1:E3,{1;2}), an array {3;2} is returned. If you set k=2, only 2 is returned.

Average the Last N Numeric Values 7

5. LOOKUP function can through searching for a row or column to return the corresponding value in the same position but in the second row or column. It has two different syntaxes, in this case, it is for vector:

Syntax:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Example, =LOOKUP(H2,E2:E3,F2:F3), apply lookup to find out name for id1.

Average the Last N Numeric Values 7

6. AVERAGE function returns the average of numbers from a given range reference.

Syntax:

=AVERAGE(number1, [number2], …)

Example. For arguments “number1, number2,…”, they can be a set of numbers, or an array of numbers like {1;2;3}.

Average the Last N Numeric Values 7

FORMULA EXPLANATION

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(Numbers),ROW(Numbers)),{1,2,3}), ROW(Numbers), Numbers))

This formula contains 6 functions, we explain functions from inside to outside.

1. For ISNUMBER(Numbers), “Numbers” is A2:A11, so ISNUMBER function check values in each cell in this range and returns “True” for numeric values and “False” for non-numeric values or blank. ISNUMBER(Numbers) returns below array:

{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE}

2. ROW(Numbers)

 returns row numbers for range A2:A11.

{2;3;4;5;6;7;8;9;10;11}

3. With the help of ISNUMBER and ROW functions, IF function returns row numbers for cells with numeric values.

IF({TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE},{2;3;4;5;6;7;8;9;10;11})

Based on logical test result, IF returns row number for “true value” and keeps “False” for “false value” as [value_if_false] is omitted. Then we get below array from IF function:

{2;3;FALSE;5;6;FALSE;FALSE;FALSE;10;11}

4. Now above array returned from IF function participates into LARGE function calculation.

LARGE({2;3;FALSE;5;6;FALSE;FALSE;FALSE;10;11},{1,2,3})

K is an array {1,2,3}, so LARGE function returns the largest three values from array {2;3;FALSE;5;6;FALSE;FALSE;FALSE;10;11}. In this step, LARGE function returns the last three row numbers for cells with numbers.

{11,10,6}

5. In step#4, we get the last three row numbers properly. Then we can apply LOOKUP function to lookup corresponding values in row 11, row 10 and row 6 from range “Numbers”.

Row(Numbers) is applied twice in this formula, the first one is “value_if_true” for IF function, the second one is used as “lookup_vector” for LARGE function.

LOOKUP({11,10,6}, ROW(Numbers), Numbers)=LOOKUP({11,10,6}, {2;3;4;5;6;7;8;9;10;11}, {5;10;0;8;20;#NAME?;"ab$cd";"ABC";15;1})

In this step, LOOKUP function returns proper values from A2:A11 after searching for row numbers 11, 10 and 6. So we get below array at last:

{1,15,20}

6. After above all steps, we get =AVERAGE({1,15,20}). So, the returned value is (1+15+20)/3=12.

Related Functions

Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value],
Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel LARGE function
The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…

May 30, 2021

How to Average Last N Values in Multiple Columns

In Excel there are a lot of built-in functions, and AVERAGE is one of the most frequently used functions. In daily work, we may apply AVERAGE function together with some other functions to get average based on some conditions. In this article, we will let you know the method to apply AVERAGE function together with OFFSET function and COUNT function to get the average of last N values in multiple columns.

In this article, we will introduce you the syntax, arguments, and basic usage of above three functions. We will also explain how the formula works with these functions.

EXAMPLE

In this case, we want to calculate the average of last 3 numbers in Row2. The most simple way, we can directly create formula “=AVERAGE(C2:E2)” to get the average as we are already clear that the last values are from range C2:E2. If we want to create a general formula that can cover “returns the average of last N value from multiple columns”, obviously the simple formula with only AVERAGE function cannot meet our demands.

To get the average of last N values for a dynamic range with multiple columns, we can apply AVERAGE function together with other functions to help us. In this article, we also apply OFFSET function together with COUNT function to help us.

FORMULA with AVERAGE & OFFSET & COUNT FUNCTIONS

In E2, enter the formula =AVERAGE(OFFSET(A2,0,COUNT(A2:E2)-3,1,3)), then press Enter, average of last three numbers is 40.

(20+60+40)/3=40, the formula works correctly.

FUNCTION INTRODUCTION

AVERAGE function returns the average of numbers from a given range reference.

Syntax:

=AVERAGE(number1, [number2], …)

Usage:

OFFSET returns a number or a dynamic range based on given five parameters: starting point, row offset, column offset, height in rows and width in rows.

Syntax:

=OFFSET(reference, rows, cols, [height], [width])

Usage 1:

Reference: A1, it is the starting point

Rows: 3, 3 rows below the starting point

Cols: 2, 2 columns to offset to the right of the starting point

Height & Width: optional, no value

Usage 2:

Reference: A1, it is the starting point

Rows: 3, 3 rows below the starting point

Cols: 2, 2 columns to offset to the right of the starting point

Height: 2, it is used together with width, determine the size of the returned range reference, in this case is 2*2 (two rows*two columns)

Width: 2, it is used together with height, determine the size of the returned range reference, in this case is 2*2 (two rows*two columns)

COUNT returns the count of numbers from a given range.

Syntax:

=COUNT(value1, [value2], …)

Usage:

EXPLANATION

=AVERAGE(OFFSET(A2,0,COUNT(A2:E2)-3,1,3))

In this case, COUNT(A2:E2) returns 5, there are five cells with numbers in range reference A2:E2.

For OFFSET function in this case, the five inputs are:

Reference: A2. It is the starting point in row2.

Rows: 0. That means 0 row below the starting point. It determines the returned range reference is started in row2.

Cols: (COUNT(A2:E2)-3). Returned value is 2, that means 2 columns to offset to the starting point.

Based on above “Reference”, “Rows” and “Cols”, we can confirm the returned range is still stayed in row2, and two columns to offset to A2, so the first column for this range is C column, and the starting cell is C2.

Let’s verify if out thought is correct. Enter =OFFSET(A2,0,2) in B7, then press Enter, formula returns C2 value 20, see screenshot below.

Height: 1. It determines that the returned range is 1-row in height. We stayed in row2 as there is only 1-row in height.

Width: 3. It determines that the returned range is 3-columns in width. From C2, the returned range is expanded from C column to E column, total 3 columns.

Based on above “Height” and “Width”, we can confirm the returned range is C2:E2. Enter =OFFSET(A2,0,2,1,3) into B6. As it is an array formula, so press Ctrl+Shift+Enter to get result.

Select {=OFFSET(A2,0,2,1,3)} in formula bar and press F9, {20,60,40} is displayed. {20,60,40} are the values in C2:E2.

Now, =AVERAGE(OFFSET(A2,0,COUNT(A2:E2)-3,1,3)) is equal to =AVERAGE(C2:E2).

Let’s see how the formula works step by step:

Step 1:

Step 2:

Step 3:

Step 4:

Above all, to get average from last N number in multiple columns, we can use below formula:

=AVERAGE(OFFSET(Starting Point,0,COUNT(Range)-N,1,N))

SUMMARY

1. AVERAGE function is used for returning the average of some numbers from a given range in Excel.

2. OFFSET function returns a number or a dynamic range based on given five inputs. Actually, rows, cols, height and width can be input negative numbers.

3. COUNT function only returns count of numbers.

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel COUNT function
The Excel COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…

May 28, 2021

How to Average Ignore Zero Number

AVERAGE function is a frequently used function in our office work. Except this basic function, Excel also provides some other Average related functions like AVERAGEIF, AVERAGEIFS. In this article, we will show you applying AVERAGEIF function to get average with zero numbers ignored from average range. We will calculate average for the same range by AVERAGE and AVERAGEIF separately, troughing comparing the two results, you can find out the difference between them. In this article, we will introduce you the syntax, arguments, and basic usage of above two functions.

EXAMPLE

There are some numbers saved in range A1:C4. Zero numbers are included in this range. E2 is used for saving the average of given numbers in this range with zero numbers included. F2 is used for saving the average with zero numbers excluded. Through setting proper criteria for AVERAGEIF function, we can get correct average with zero numbers ignored.

FORMULA with AVERAGE & OFFSET & COUNT FUNCTIONS

In E2, enter the formula =AVERAGE(A1:C4), then press Enter, average of all numbers with zero numbers included is 46.25.

In F2, enter the formula =AVERAGEIF(A1:C4,”<>0″), then press Enter, average of numbers with zero numbers excluded is 61.67.

You can see that we apply different functions to get average. The difference is AVERAGEIF function can calculate average with condition.

FUNCTION INTRODUCTION

AVERAGE function returns the average of numbers from a given range reference.

Syntax:

 =AVERAGE(number1, [number2], …)

AVERAGEIF function returns the average of a set of numbers from a given range based on one condition or criteria. We can split the function as AVERAGE + IF.

Syntax: =AVERAGEIF(range, criteria, [average_range])

EXPLANATION

=AVERAGEIF(A1:C4,"<>0")

In this case, a set of numbers are saved in range A1:C4, some zero numbers are included in this range. As we want to calculate average with zero number ignored, so criteria range and average range are the same. For AVERAGEIF function, average range is optional, so if it is omitted, criteria range is equal to average range.

“<>0” is the criteria value in this AVERGAEIF function. “<>” is “not equals to” operator. “<>0” is not equal to 0, so set “<>0” as criteria can make zero numbers excluded in calculation.

Actually, some average related functions like AVERAGE, AVERGAEIF and AVERAGEIFS can ignore blank cells and cells contain texts automatically, so if user want to ignore blank cells or cells contain texts, you can directly apply AVERAGE function to get average ignoring these invalid cells. See example below.

But AVERAGE function cannot handle errors like #N/A automatically, so we need to apply AVERGAIF or AVERGAIFS function to filter errors by adding proper criteria.

SUMMARY

AVERAGE function is used for returning the average of a set of numbers in Excel.
AVERAGEIF function returns the average of a set of numbers refer to one given condition or criteria.
AVERAGE/AVERAGEIF/AVERAGEIFS functions can ignore blank cells and cells contain texts.

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
Excel AVERAGEIFS function
The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

May 7, 2021May 9, 2021

How to Average and Ignore Errors in Excel

This post will guide you how to average a list of values and ignoring any errors in the given range in Excel 2013/2016 or Excel office 365. You can use the AVERAGEIF function or AGGREGATE function to calculate the average of a range of cells which might include some errors in Excel.

Average Cells Ignoring Errors

Assuming you want to calculate the average of the cells that excluding any errors in range B1:B6, and you can use the following formula based on the AVERAGEIF function, like this:

=AVERAGEIF(B1:B6,”>10”)

Let’s See How This Formula Works:

The AVERAGEIF function can be used to calculate an average of some numeric values with one or more criteria. In the above example, the criteria is the expression of “>10”, and it means that you want to average all cell value in range B1:B6 which might include error values.

If you want to calculate the average of the cell values that ignoring any errors that might exist in the given range. And you can also use another array formula based on the AVERAGE function, the IF function and the ISERROR function. Like this:

=AVERAGE(IF(ISERROR(B1:B6),””,B1:B6))

Then you need to put this formula in a cell, and press Ctrl+Shift+Enter keys to make it as array formula. and you should see that the average result would be calculated.

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel ISERROR function
The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….

September 2, 2020August 30, 2020

How to Average Absolute Values in Excel

We can use AVERAGE function to calculate average of certain values. We can use ABS function to get absolute values for both positive number and negative number. If we want to get the average absolute values, we need to combine both above two functions in the formula. In this free tutorial, we will provide two formulas for you to average absolute values.

Precondition:

Prepare table below. There are some negative numbers.

Method: Average Absolute Values by Formula

Step 1: In D2 enter the formula =AVERAGE(ABS(A2:B8)).

Step 2: As it is an array formula, so press Ctrl+Shift+Enter to get value.

Step 3: If you don’t want to enter an array formula and you like apply a regular formula instead, you can enter this formula =AVERAGE(INDEX(ABS(A2:B8),0,0)).

Step 4: Just press Enter as usual.

We get the same result.

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel ABS Function
The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…
Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…

February 24, 2020

How to Calculate Average among Multiple Different Worksheets in Excel

If we want to calculate the average for all numbers from multiple worksheets, how can we do calculate? Currently we may know the way to calculate the average for a selected range on one worksheet, this article will introduce you a convenient way to calculate the average for all numbers across multiple worksheets.

As the data range on multiple worksheets may be the same or different, so we prepare two examples to do demonstration.

Calculate the Average for The Same Selected Range from Multiple Different Worksheets

First, we need to prepare two worksheets with numbers entered in the same range. See example below.

Worksheet1->Season 1 Worksheet2->Season 2

a. Select a blank cell for saving the result, in this case we select E2.

b. in E2, enter the formula =AVERAGE(‘Season 1:Season 2’!B2:B4).

Notes:

‘Season 1:Season 2’ is the worksheet range. Season 1 and Season 2 are the worksheet names. If you enter ‘sheet1:sheet3’ in this field, that means the worksheet you selected includes sheet1, sheet2 and sheet3, total three adjacent worksheets. And user need to add quotes for ‘Season 1:Season 2’.

‘B2:B4’ is the data range you want to do calculate the average.

c. Click Enter to get the result.

Calculate the Average for The Different Selected Ranges from Multiple Different Worksheets

First, we need to prepare two worksheets with numbers entered in the different ranges. See example below.

Worksheet1->Season 1 Worksheet2->Season 2

a. This time, in E2, enter the formula =AVERAGE(‘Season 1′!B2:B6,’Season 2’!B2:B5). The format is ‘worksheet!data range’.

b. Click Enter to get the result.

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….

July 2, 2019July 2, 2019

How to Round the Calculated Average result in Excel

This post will guide you how to use average and round functions at the same time to average a given range, and then round off the last result in Excel. How to use Round function to round the calculated average values with a formula in Excel.

Round the Calculated Average result with Decimal Command
Round the Calculated Average result with a Formula

Assuming that you have a list of data in range B1:B6 in which contain numeric values. And you can easily to calculate the average value with AVERAGE function. And the average value may contain two or more decimal places. And you want to round off this number to the specified number of decimal places. How to do it. You can use another function called ROUND. This post will show you tow methods to accomplish the result.

Round the Calculated Average result with Decimal Command

To round the calculated average value for your data, you can use increase or decrease decimal command with the following steps:

Step1: select the range that to be averaged. Such as: B1:B6.

Step2: type the following formula based on the AVERAGE function to calculate the average value for the given range of cells:

=AVERAGE(B1:B6)

Type this formula into a blank cell and press Enter key on your keyboard. You would see that the average value has been calculated.

Step3: select the calculated average value and go to Home tab, and click Increase decimal button or decrease decimal button under Number group.

Round the Calculated Average result with a Formula

You can also use a formula based on the ROUND function and AVERAGE function to accomplish the same result of rounding the calculated average result in Excel. Like the following formula:

=ROUND(AVERAGE(B1:B6),2)

Note: if you want to round off to the specified number of decimal places as you need, you just need to change the number 2 as you want.

Related Functions

Excel Round function
The Excel INT function rounds a number to a specified number of digits. You can use the ROUND function to round to the left or right of the decimal point in Excel.The syntax of the ROUND function is as below:=ROUND (number, num_digits)…
Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….

June 8, 2019

How to Ignore Error Values When Calculating the Average in Excel

This post will guide you how to ignore error values when getting average for a range of cells in Excel. How do I average a range of cells ignoring error values, such as: #div/0!.

Assuming that you have a list of range of cells that you want to calculate the average of those cells which including error values , such as: #div/0 or #NAME?, etc. When you use the Average function to calculate those cells, you will get an error result. So How to handle this in Excel.

If you want to ignoring any errors that might exist in the given range, you can use AVERAGEIF function or AVERAGE function with IF function to achieve the result. Like this:

=AVERAGEIF(A1:C4,”>=0”)

=AVERAGE(IF(ISERROR(A1:C4),””,A1:C4))

=AVERAGE(IF(ISNUMBER(A1:C4),A1:C4))

Note: The second and third formula need to press Ctrl + Shift + Enter keys, as those formulas are Array Formula. A1:C4 is the data range that you wish to average. And you need to change it as you need.

Let’s see how the first formula works: the AVERAGEIF function can be used to calculate an average of numeric values with one or more criteria for a given range. And In this example, the criteria is the expression “>=0”, it will filter out all error values, and just to calculate the positive numeric values in the given range.

Related Functions

Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
Excel IFERROR function
The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…

January 23, 2019January 23, 2019

How to Add Vertical Average Line to Bar Chart in Excel

This post will guide you how to add vertical average line to a bar chart in Excel. How do I add an average line to your bar chart in Excel 2013/2016.

Add Vertical Average Line to Bar Chart

Assuming that you have created a horizontal bar chart based on the original data in Range A1:B5 in current worksheet, and you want to add a vertical average line across the horizontal bar chart at a specific value. How to do it. And you can do the following steps to add a vertical line to the horizontal bar chart type in Excel.

#1 select the original data that you want to build a horizontal bar chart.

#2 go to INSERT tab, click Bar command under charts group. And select Clustered Bar chart type. A horizontal bar chart is created in your worksheet.

#3 we need to build a new series into the existing bar chart, so need to add two numbers 0 and 1 into two blank cells A6 and A7, then to the right of cells A6:A7, apply the following formula to calculate the average value for all of the sales data. Like this:

=AVERAGE($B$2:$B$5)

#4 select the chart, and go to DESIGN tab, click Select Data command under Chart Tools group. And the Select Data Source dialog box will open.

#5 click the Add button under legend Entries section to add the Average Value series in the Select Data Source dialog box. And the Edit Series dialog will open.

#6 type “Average” as the name of the series. And specify the two cells (B6:B7) that contain the average values calculation. Click OK button.

#7 click Ok button. Then the new average series has been added into the existing bar chart.

#8 right click on the average series in the bar chart, and select Change Series Chart Type from the popup menu list to turning the selected series into a vertical line. And the Change Chart Type dialog box will appear.

#9 select Combo option, select Scatter with Straight Lines chart type in the Chart Type list for the Average Series name in the Change Chart Type dialog, and select. Click Ok button to exit the dialog.

#10 right click on the bar chart again, choose Select Data from the popup menu list. And the Select Data Source dialog will appear. And then choose the Average series and click Edit button. The Edit Series dialog will appear.

#11 you need to specify Series X and Series Y values for the vertical average line. And the Series X values should be the place on the x-axis for your vertical line, so you should choose the cells that contain average calculation. And the Series Y values should be the place on the y-axis for your vertical line, so you can choose the cells that contain 0 and 1 numbers. Click Ok button.

#12 click Ok button. And you would notice that the vertical line has been added into your horizontal bar chart.

#13 you need to adjust y-axis of the average series, double-click the second y-axis on the right side of the chart. And the Format Axis pane should be shown.

#14 type number 1 into the text box in the Format Axis dialog.

#15 right click on the vertical line, and select Format Data Series from the popup menu list. You can change the colors and design of the chart as you need. Like this:

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….