Excel 2013 offers a plethora of mathematical functions that you can use. Functions are used to create formulas. We're going to start talking about some of the more basic functions, as well as teaching you to use them.
We will discuss:
-
The basis Excel functions
-
Averages, including mode, median, and mean
-
The SUMIF function
-
The COUNTIF function
-
How to use multiple criteria in functions
-
Calculating area and value of shapes
The Basic Excel Functions
Before we start to delve into mathematical functions in Excel 2013, let's start out by reviewing the very basic mathematical functions that you are probably already skilled at using.
In the snapshot below, we have sales data for a few months' period.
In the Totals column, we want to add the sum of sales for each employee.
To do this, type in the cell: =sum(
Next, highlight the cells you wish to use to produce the sum.
Now, place the end bracket (parentheses at the end), then press Tab on your keyboard.
You can also click in the cell where you want to place the total (in our example, H4), highlight the cells you want to use to produce the sum, then click the Formulas tab. Click the AutoSum button.
This is how your formula should look in the Formula Bar:
We can now add sums to the rest of the cells in the Totals column.
To do so, we can click the little handle at the bottom right corner of the cell where we just entered the function.
Drag the handle down to select the rest of the cells where you want to copy the same function.
Excel 2013 then produces the sum for each of those rows for you.
Once we are finished with totals, we are going to add a new column called Count, as shown below.
To enter the count function, type =count( into cell I4, then drag over the cells to include. Enter in the close bracket (parethenses). Hit Tab on your keyboard.
Excel 2013 then counts the total number of months in that row.
Once again, select the cell (I4) and drag the handle to enter the count for each subsequent row.
Next, let's as a Lowest Sales column. We will use the MIN function to find the lowest sales value for the selected data.
The formula looks like this:
We can now see the lowest sale in our spreadsheet.
Now let's add a column called Highest Sale and use the MAX formula.
It looks like this:
Take a look at our data below:
Average: Mode, Median, and Mean
For any of your math buffs out there, you know that when we talk about averages in mathematics that there are three different terms: mode, median, and mean.
-
Mode is the most frequently occurring value in a range.
-
Median is the middle-most value in a range.
-
Mean is the total of the values in a range divided by the number of values.
The Mean function in Excel is Average. Here is the formula:
As you can see, we averaged the sales for each month in our worksheet below:
The function for mode is shown below.
And the formula for median:
The SUMIF Function
The SUMIF function will total the cells in a range that you've selected using criteria that you specify.
Using our worksheet below, we want the total amount in sales for all sales that are greater than 11. You can use any comparison operator that you want in SUMIF functions. We are just going to use greater than.
First, we click on the cell where we went the results to appear.
Next, we go to the Formulas tab, click on the Math & Trig button, then scroll down until we see SUMIF. Click on it.
You will then see the Function Arguments dialogue box.
In the Range field, highlight the range of cells that you want to evaluate. This is our sales for the employee Smith.
In the Criteria field, enter the criteria. Ours is <11.
NOTE: If you are writing the formula in the cell, you must use quotations around the criteria: "<11"
We will look at the Sum Range field in a minute. For now, we just want to see the total of sales for all sales that are greater than 11.
