Exploring statistical functions
In Chapter 9, New Features, Filters, and Cleaning Data, of this book, we learned about using the COUNTIFS statistical function. A list of statistical functions can be found in the Excel library. Click the Formulas tab, then click on More Functions | Statistical to see all types available, as illustrated in the following screenshot:
Figure 12.23 – Statistical functions
We will now learn about a few more statistical functions. In the next section, we will discuss the MEDIAN function.
Finding the middle ground
The MEDIAN function is used to locate the average of the two middle numbers in a range of cells. MEDIAN is like the 19 function argument options listed in a previous function in the Maths & Trig section—namely, AGGREGATE:
- Open the Training Schedule.xlsx workbook. Make sure you are on the
Median worksheet.
- Click into cell C2, then type the following formula: =MEDIAN(.
- Select the range A2:A8 to find the average of the two middle values in the range, as illustrated in the following screenshot:
Figure 12.24 – MEDIAN syntax
- Press Enter to see the result. In this case, the answer should be 5. The best way to see if this is calculating correctly would be to sort the range.
In the next section, we will learn about the COUNTBLANK function. In the previous edition of this book, we introduced COUNT functions, and in earlier chapters in this edition of the book, we concentrated on statistical functions such as COUNT, COUNTIF, SUMIF, and SUMIFS.
Counting empty cells
In this function, the COUNTBLANK function only counts blank or empty cells in a range. The syntax is simple to construct. Let’s work out which companies do not have any discounts applied next. Proceed as follows:
- Open the SSGPet.xlsx workbook.
- On Sheet1, there is a list of discounts in column N. Click into P2 to construct the formula.
- Type =COUNTBLANK( then select the range N2:N124, as illustrated in the following screenshot:
Figure 12.25 – COUNTBLANK function
- Press Enter to see the result. 10 companies do not have discounts applied.
The next section will concentrate on the AVERAGEIF function. This function locates the arithmetic mean (the average) for a range specified by a condition or criteria.
Averaging using one condition
The AVERAGEIF function returns the arithmetic mean (average) of all cells in a range that meet a condition or criteria. In layman’s terms, it calculates the average of cells meeting one criterion. Use the AVERAGEIFS function if you wish to calculate the average of cells meeting multiple criteria.
The syntax of the AVERAGEIF functions is described in more detail here:
Table 12.4 – Syntax and explanation
Let’s look at an example of the AVERAGEIF function to work out the average revenue for a particular winery. Proceed as follows:
- To follow along, open the WinerySales.xlsx workbook.
- This workbook consists of sales data for different wineries. Let’s build the function.
- Click into cell M4, as this is where we will build our calculation.
- Start constructing the formula, as follows: =AVERAGEIF(.
-
For the first argument, select the range in which the criteria can be located. In the case of our example, we need to select the Winery range B2:B23. Press the comma key to move to the next argument.
- Adding criteria is the next argument. We can use one of two methods to collect the winery name—either type the name directly into the criteria argument using inverted commas on either side of the winery name (that is, “Fortesque Vino”) or use a cell reference on the worksheet to collect the relevant winery criterion. The latter option is useful should you want to change the criterion at any time to make the formula more dynamic. For this example, we will use cell M2 to collect the winery name, Fortesque Vino.
- The last argument is the range you wish to average should the criteria be successful. In this case, we will average the Revenue range, J2:J23.
- Press Enter on the keyboard to see the result, which is displayed in the following screenshot:
Figure 12.26 – AVERAGEIF function
- Should you wish to see the average for any of the other wineries, simply type the company name into cell M2. Let’s see what the average is for Matts Winery. Replace the text Fortesque Vino with Matts Winery, then press Enter on the keyboard.
- Experiment with different scenarios using wildcard characters in the criteria argument. If we are interested in the average revenue for all wineries except Prominent Wines, we would build the formula using the not equal to sign, <>, as follows: =AVERAGEIF(B2:B23,”<>Prominent Wines”,J2:J23).
When a situation arises where you want to include more than one single criterion as an argument, you can make use of the AVERAGEIFS function.
Averaging using more than one condition
The main difference between AVERAGEIF and AVERAGEIFS is noted here:
- The AVERAGEIF function averages the number of cells within a range that meet a single condition you specify.
- The AVERAGEIFS function counts the number of cells by evaluating different criteria in the same or different ranges.
In this example, we will look for the average profit for discounts applied over 25%, as well as the cases sold being equal to or greater than 250. Proceed as follows:
- Using the same workbook as for the Averaging using one condition section, click into cell M8. We will build our function here.
- For this example, we will use the Insert Function tool to help build our formula. The Insert Function tool is the fx button located to the left of the formula bar. Click the fx button to populate the Function Arguments dialog box.
-
The first part of the function syntax is to provide the Average_range value. Click into this area, then select the range K2:K23 on the worksheet. This range is the profit range as we will be finding the average profit according to a certain criterion.
- Click into the Criteria_range1 area, then select the range G2:G23, then specify the “>=250” Criteria1 detail to locate instances of cases sold greater than or equal to 250.
- Finally, enter the second criteria by selecting the range H2:H23, after clicking into the Criteria_range2 area. Specify the Criteria2 detail “>25%” to locate discounts greater than 25%. The process is illustrated in the following screenshot:
Figure 12.27 – AVERAGEIFS using the Function Arguments dialog box
- The dialog box will report if there are any errors found. You will see a Formula result value at the bottom left of the dialog box. Click on the OK button to confirm and see the result on the worksheet.
- Using the dialog box for more complex calculations aids you, as you can see the relevant worksheet data presented to the right of each selected range from the workbook.
We hope that we have covered enough ground here for you to experiment with different criteria to suit your specific requirements.
Summary
You now have the skills to work with Math & Trig and Statistical functions using Excel 2021. You have built on prior knowledge by learning valuable functions, such as being able to generate random numbers using RANDBETWEEN and RAND functions. In addition, you went through examples of working with PRODUCT functions and explored the SUMPRODUCT, MROUND, FLOOR, TRUNC, AGGREGATE, and CONVERT functions.
We also investigated the MEDIAN, COUNTBLANK, and AVERAGEIFS statistical functions in this chapter, from which you can experiment and apply to existing or new scenarios when working with workbook data
This next chapter will take you through Outlook 2021 enhancements, where we will explore and configure objects such as mail, contacts, tasks, notes, and journals. You will set some advanced and language options in the interface and learn how to manipulate item tags and arrange the content pane.
In addition, we will apply some search and filter tools, and print Outlook items. You will learn best practices for sending email messages, as well as configure send and delivery options to improve productivity in the Outlook application. You will also learn to professionally format item content and attach content to an email.
