Performing Data Calculations in Excel

Performing data calculation in Excel.

Simple Calculations

In Excel 365, simple and complex calculations are easy. You will discover how to perform simple calculations. The cells in your Excel worksheet can be thought of as little calculator spaces. You can use these individual cells to perform simple and complex calculations. In my worksheet, I would like to use a simple addition calculation to calculate the total of the postage and packaging values found in cells F5 and F6.

To compose this calculation, first enter an equals symbol. The symbol also now displays in the formula bar. Once done, you can then enter in the values you would like to calculate. You can enter the values manually or you can click on the cells where the values are located. So I will click on cell F5. Cell F5 is now highlighted in blue, and that cell's coordinates have been placed within my calculation. Next, type in a plus symbol, and then select the second cell value. I will select cell F6. That cell is now highlighted red and its cell coordinates appear within my calculation. When you are ready, press the enter key on your keyboard. The additional calculation has now been performed, and the total value of 80 cents now appears in cell F7.

Note that if the values of the reference cells used in the calculation change, the total will automatically update to reflect this. For example, if I click on F6, and change the value from 50 cents to 40 cents, then press the enter key to save, note that the total value in cell F7 has changed to 70 cents. Also note that you can use this same method to perform simple subtraction calculations. The only difference being that you enter in a minus symbol instead of a plus one in your calculation. Below my postage and packaging total cell, I would like to calculate the postage and packaging for 11 packages.

To do this, I can perform a simple multiplication calculation. First, click on the cell where you would like the value calculated. So I will click F8. To start the calculation, enter in an equal sign, then select your reference cell. So I will select the P&P total in cell F7. Next, enter in an asterisk symbol followed by the value you would like to multiply by. As I am calculating the total of eleven packages, I will type in the number 11, then press the Enter key to calculate. Now, the total value of $7.70 is displayed in cell F8. Note, that you can also use the same method to perform a simple division calculation, but instead of the asterisk symbol, use a slash symbol.

In cell F2, I would like to calculate the total sum of the values found in column B, Purchase total. To do this, I will use the sum function. With the sum function, you can reference an entire range of cells. Click on file F2, enter in an equal sign followed by the text SUM. Note that once you start typing in the formula, a list of the formula suggestions appear below. I can either select a formula from this list to insert, or I can continue composing it myself by inserting an open parenthesis symbol. Then select your cell range, which for me is cells B2 to B12. The selected cell range coordinates now appear in the sum formula. Enter in a close parenthesis symbol and then press the Enter key to calculate. The total value of $3164 now appears in cell F2.

In cell F10, I would like to add the overall total with the P&P total for eleven packages. In Excel, you can use simple calculations to calculate complex ones. For example, I will click cell F10 and type in an equal sign. Now, instead of using reference cells with values typed in, I will use reference cells that have calculations. So I will select cell F2, compose a simple addition calculation by entering in a plus symbol, select the second reference cell F8, then press the
Enter key to perform the calculation. The total value of $3171.70 now appears in cell F10. Note also that if you make changes to the reference cells used, the total value will automatically update to reflect this.

Creating Automated Running Totals in Excel

If you have organized your data entries into chronological order, a cumulative total can be created once and then applied automatically to the rest of your data entries. You will learn how to create automated running totals in Excel. In my worksheet, I have values already entered into column D labeled Purchase total. I have organized these values into chronological order and I would like to keep a running total of them. To do this, I am first going to select cell E2 in the Running total column.

To start creating the running total, enter in an equal sign in the cell. Then select your reference cell, which will be the first value of the column you want to create a cumulative total for. So I will select cell D2, then press the Enter key to finish the formula. The value, $150 from cell D2, now displays in cell E2. Now, if I want to automatically add a consecutive value, I will select the cell in the column where I would like to add the running total to, which is cell E3. Enter in an equal sign into the cell, then select the first cell in the column which is cell E2. Insert a plus symbol, and then select the cell that contains the cell that contains the value you would like to add. So I will select cell D3. The formula now displays as equals E2 plus D3. To perform the calculation, press the Enter key. The value in E2 has now been added with the value from cell D3, giving me the running total value of $420 in cell E3.

Now, if I would like to add this running total to the rest of the cells in column E, I can do that using the autofill handle. To do this, select cell E3 and hover over the bottom right corner of the cell. When your cursor changes to a black plus symbol, double click your mouse. The running total formula has now been added to the rest of the cells in this column, and cumulative total values now display in these cells. This will save you a lot of time if you find yourself continually adding values to your worksheet. You can use the same method to create an automated cumulative total using a fixed value. In column F, I would like to calculate the fixed cost reduction.

To do this, I will first select cell F2, then insert an equal symbol, followed by the reference cell which will be cell D2 in the Purchase type column. I would like the fixed cost to be removed from this total value, so I will insert a minus symbol and then select the fixed cost value found in cell H2. Now if this fixed cost value never changes, you are going to need to make it absolute. To make an absolute value, into the formula I will click my mouse between the column letter H and the row number 2, and then type in a dollar sign. Once done, you can calculate the value by pressing the Enter key. The value has been calculated with the fixed costs taken into account, so the value that displays and cell F2 is -$850.

Again, to apply this cumulative total with the fixed value to the rest of the cells, I will use the autofill feature. Click on the cell F2. Hover your mouse over the bottom right corner of it. When your cursor changes to a black symbol, double click your mouse. The formula has been automatically added to those cells, and the corresponding row results now display. As you can see, the absolute value will stay the same contrary to the running total, which will change according to the cell reference on the left hand side and the cell just above.

Counting Cells in a Range

Excel 365 has formulas that count cells based on conditions you set. You will explore how to count cells in a range in Excel. The Count function counts the number of cells that contain numbers. This includes a text representation of a number, cell reference, range reference, or a named reference. The function will ignore any cells that contain text, that are empty, or that contain an error.

First, I will select cell B16, into it type in an equal sign followed by the text count. When you do this, a dropdown list appears below with different function options. You can select an option by double clicking it. I will continue the function manually by typing in an open parenthesis symbol. Then you can enter in your first value, which will be a cell reference or range.  You can select the cell or range using your mouse or by typing the reference into the formula. I will do it manually by typing in the range B2, a colon symbol, and then B15. Insert a closed parenthesis symbol and press the Enter key to calculate.

In cell L2, I would like to calculate the number of orders I have. To do this, I will use the Counta formula. The Counta formula is used in Excel to count the number of cells that contain values. First, I will select cell l2, then insert an equal symbol followed by the text Counta. From the suggestion list that appears, I will double click on the Counta option and the Counta formula automatically inserts with the open parenthesis symbol. Next, I need to select the range of cells. Since I am just counting the number of orders, I am going to go down to any of my columns and I am going to simply select the table range. Once done, I will insert a close parenthesis symbol, and then press Enter to calculate. The formula result 14 now displays in the cell. This has saved me a lot of time from having to count each row.

In cell l5, I would like to calculate the number of people who have not paid in advance. The empty cells in column F indicate people who have not paid. To calculate, click on cell L5. insert an equal sign followed by the text Countblank and an open parenthesis symbol. I will then select the cell range F2 to F15, insert a close parenthesis symbol, then press the Enter key to calculate. The formula value 6 now displays, meaning there are six blank cells in column F. If I am looking for a particular order type, I can use the Countif formula. This formula can be used to perform a count on certain values that meet your conditions.

For example, I will click on cell L3, insert an equal sign followed by Countif and an open parentheses. Next, I will need to select the range. I am looking for the total number of kitchen order types, so I will select the cell range B2 to B15, then insert a comma. Next, I need to select the criteria or the value I am looking for. I want the value of Kitchen to be counted in my range.  So I will select cell B2, which contains the value Kitchen. Insert a closed parenthesis and press Enter.

The total number of kitchens found in the order type column, which is 3, displays in the cell. If I want to change the criteria value, I will select cell L3 again, and highlight the criteria cell reference B2. Then from the order type column, select cell B7 with the value Lounge in it. Then press the Enter key to calculate. The value in the cell updates and changes to 4. This could save you a lot of time from having to reselect your cell range. The Countifs function can be used to count the number of cells that meet one or more criteria. I would like to calculate the number of times blinds were bought under the Order type Lounge.

To do this, select cell L4, type in an equal sign followed by the Countifs function and an open parenthesis. Select the first range, which will be cells B2 to b15. Insert a comma followed by the criteria Lounge in quotation marks, followed by another comma.

Select the second cell range c2 to C15. insert a comma and then the second criteria, which is Blinds in quotation marks. Insert a close parentheses symbol and press Enter to calculate.

The value 2 now displays, meaning two values were found in the selected cell range that meet my two criteria.

Using the Ifs and Switch Functions in Excel

Let us start with the function Ifs. Now this function will test multiple conditions in order, and it will return a value that you specify based on the results of those logical tests. So, for example, I have got a grading sheet open in Excel, and here I would like to return a letter grade in column C based on the numeric score in column B. I am going to use the grading scale here on the right to help me.

Now to start, I will select cell C2, enter the equal sign and the function name Ifs, followed by an open parenthesis. In the screen test, we can see the syntax. Now, I need to start with the first logical test or the first condition, and then the value that will be returned if this condition is true. So the first logical condition is simply whether or not the score is above the cutoff score for an A, which is a 79. So I am going to enter the cell reference b2, then ">79". And follow that with a comma. Next, I need to enter the value to return. So that is going to be the grade A. Since this is a string, I need to enter the letter A between quotes, and once again, follow that with a comma.

Next, I will repeat the same process with other pairs of logical tests and the value if the test is true. So the next test will be if B2 is greater than 59, then I will type a comma, and the value if true will be the string B. Follow this by a comma again, and enter the last logical test, B2 is greater than or equal to 0, comma, and then enter the value "F". I will close this formula with a closed parenthesis, and press Enter, and this returns a result or a value of A based on the score 92 in column B.

So far, so good. To apply this formula to the rest of the rows, I am going to select C2 and then double click in the bottom right corner of this cell to use auto fill. Now I can see the formula has been correctly applied to the other rows in this table.

The Switch function works a little differently. This function compares an expression, which is usually a cell reference, to a list of values that you give it, and then it returns a different result depending on that value. So here in column D, I would like to give a pass or fail mention based on the grade in column C. A grade of A or B should return the result of Pass, and a value of F should return the result of Fail.

So I will select the cell D2 and begin entering the formula, so equal sign and now the function name Switch, followed by an open parenthesis. This time, we need to start with the expression which is our cell reference, C2, which I will select. Follow that with a comma. Then we will enter our pairs of values and results. In this case, the values are the different grades that I want to check against the reference cell. So the first value will be A as a string. I will follow that with a comma and type the first result, which should be the string "Pass". I will type a comma again and do the same thing with the other pairs of values and results. So for the value B, I would like to return the result of "Pass" as well. But for the value F, I would like to return a value of "Fail". Close this with a closed parenthesis. Once again, I will press enter.

Again, I will select cell D2 and double click in the bottom right corner to auto fill this formula to the other rows in the table. Now, I can see wherever I had a value of A in the reference cell, the result is Pass. Same thing for B. Wherever I had a value of F, the result was a failure.

Using the Maxifs and Minifs Functions in Excel

If you are looking for the minimum and maximum values that meet a specific criteria, Excel has two functions that can help you find it. You will explore how to use the Minifs and Maxifs functions. The Minifs and Maxifs function allow you to find the minimum and maximum value in a range after applying one or more criteria. On the right hand side of my worksheet, I have composed a small table where I would like to calculate the lowest and highest grades with specific criteria applied.

For example, if I would like to calculate the lowest grade for the fall semester, I can easily do this using the Minifs function. Click on cell L2 and insert an equal sign followed by the Minifs function and an open parenthesis symbol. After I have inserted my function, I am going to need to select the minimum range. This will be my score range found within column E, so I will select those cells. Next, insert a comma followed by the criteria range, which will be the semesters.  So I will select the column D cell range. Then I am going to insert another comma.

Now I am going to establish my criteria.

Since I want to calculate the lowest grades for Fall, I am going to insert the text Fall in quotation marks, and then to close off the formula, I am going to insert a close parenthesis symbol and press the Enter key.

So now I can see that the lowest grade for fall is 68. Note that you can also add more than one criteria for the Minifs function if you wish. If I would like to calculate the opposite, which happens to be the highest, I can use the Maxifs function. The Maxifs function lets you determine the largest value within a specified range based on one or more conditions or criteria. To do this, I will first select cell L3. Then insert an equal symbol followed by the Maxifs function and an open parenthesis symbol. After I have inserted this function, I am going to select values for my max range, criteria range, and criteria. For my max range, I will select my column E table cell range, insert a comma and then, from my criteria range, which is my semesters, I will select the cells in column D and then insert another comma. My criteria for this function will be Spring instead of Fall.

So I am going to insert the text spring within quotation marks, close off the formula with a close parenthesis, and then select Enter.

Displayed now is the highest grade for the Spring semester, which is 95 percent. If I want to see the highest grade for Fall in a specific year, I can insert two criteria. I am going to use the Maxifs function, but you could also use more than one criteria with the Minifs function. I am going to select cell L4 and insert an equal sign, followed by the Maxifs and an open parenthesis. Now I am going to need to select my max range again, which is the cells in my Scores column followed by a comma. Then my criteria range, which again will be the cells in the semester column followed by another comma. Then I am going to need to insert my first criteria, which is Fall, so insert that in quotation marks. Insert another comma and then the second criteria. I would like to calculate for Fall. So I am going to select the criteria range, which is the cells in the Year column. Insert a comma followed by the text 202 in quotation marks.

Close the formula with a close parenthesis and then press the Enter key.

Using the Offset Function in Excel

With Excel, there is no need to constantly modify formulas as you are updating your tables and adding new data entries. You will discover how to use the Offset function in Excel. If you are constantly updating your tables and adding new data entries to your worksheet, you can create conditional formulas to keep up to date with your new additions. Once you have inserted these calculations using conditional formulas, you will not have to modify them again to adjust the data ranges that they reference. One conditional formula you can use is the Offset function. In the table on the right hand of my worksheet, I would like to keep an eye on my last order value. To do this, I can use the Offset formula.

First, I am going to select cell H3 and insert an equal sign followed by the Offset function and an open parenthesis symbol. Now I am going to need a reference. To determine the last value in a column, the reference will be the first value in that column. So I am going to select the first value in column E and then insert a comma. Next, I need to insert the number of rows in my table. This total row value is displayed in cell H2, so I will select that. Then I am going to need to determine the height and width. Since I want to keep an eye on the last total, I am going to insert a minus symbol and the number 1, and then insert a comma. The width of the value will be entered in as zero since I am calculating just for this one column.

Close off the formula with a close parenthesis and then press the Enter key.

The formula has now calculated the last order value as $20.00, and I can confirm that this is correct by going to the last cell in column E, and displaying the value $20.00. For example, if I add an additional row to my table by selecting row 13, then right click and from the shortcut menu selecting the insert option, then select cell E13 and, for example, insert the currency value $45.00, and then press the Enter key. Now my total orders value and cell H2 has updated to 12 as I have increased the number of rows and my last order value and cell H3 has also been updated to the now displayed value of $45.00. This will help me keep an eye on my orders as well as the total number of orders as I add and arrange those values within my table.

Configuring your Default Formula Options in Excel

In Excel, you can customize various elements, including formulas for your own convenience. Here, you will learn how to configure default formula options. As you may already know, by default, columns and rows are represented by letters and numbers, respectively. A cell's coordinates are then where the columns and rows intersect. We can view a selected cell's coordinates from the name box at the top left of the interface. Using this name box, we can jump to calls manually in the worksheet. For example, if you type A3 into the name box and hit enter, note the new cell selection in the worksheet. In Excel, you can configure the default cell reference format which will change the way cells are referred.

To do this, click the file tab at the top left of the tools ribbon.

From the file backstage view menu on the left, go down to the bottom and click More…From the side menu that appears to the right, click on Options. The backstage view closes and an Excel Options window displays. The General tab at the top left is selected.

Below this, click the Formulas tab. From this tab, go to the Working with formulas section in the center. At the top of this section are the options to enable R1C1 reference style. Enabling this option will change the way Excel formulas refer to cells. Instead of using letters for columns and numbers for rows, this option enables numbers for both columns and rows. Cells will then be displayed in the format such as R1C1. to enable this option, check the box to the left of it. To save the changes, click the Ok button at the bottom right. Now, if we look in the name box at the top left, instead of A3, the cell reference has changed to R3C1, meaning row 3 column 1.

You may already have noticed that when you type a formula, autocomplete can be used to quickly complete the formula. For example, if I select cell E15 and insert an equal sign followed by the letters AV, a list of possible formulas based on what we have typed so far displays below. We can select one by double clicking on it from the list. I will select the top option average by double clicking. That formula automatically inserts into the selected cell. You have the option to disable this formula autocomplete feature. To do this, click the file tab at the top left again. From the bottom left of the backstage view menu, select More…and from the side menu, click on Options. The Excel Options window opens.

From the list of tabs on the left, switch to the Formulas tab. Go to the Working with formulas section in the center, and here you will find the option Formula Autocomplete. The box to the left of this is already checked, indicating this feature is activated. So I will click on the box to uncheck it, and disable the feature. To save the changes, click the Ok button at the bottom right. So now, with cell E15 still selected, if I type in an equal sign followed by the text AV, note that no list of formula suggestions appears. Another formula tool is the ability to refer to data ranges using table names and formulas as long as the data has been formatted as an Excel table.

To do this, click the File tab again. From the backstage view, select More…From the side menu, click on Options. In the Excel Options window, switch to the Formulas tab. In the Working with formula section is also the option, use table names and formulas. The box to the left of this is checked, meaning it is activated. To disable it, I will click the box to uncheck it. Save your changes by clicking the Ok button at the bottom right.

Calculating Average with Different Conditions 

If you want to find out an average, but you want to limit the criteria, Excel has a function you can use. You will explore how to calculate the average with different conditions in Excel. If you need to calculate the average in a certain range, but have more than one criteria to apply, you can use the average Ifs functions. On the right hand side of my worksheet, I have a table where I am calculating the average scores under specific criteria. In cell L3, I would like to calculate the average score for students in Fall of 2020. To do this, I will first select cell L3, then insert an equal sign followed by the function average Ifs and an open parenthesis symbol. Next, I will have to select my average range. This will be the column that contains my score values. So I will select the cell range of column E in my table, then insert a comma.

Next, I will need to select my first criteria range so I will select the cells in column D which contain my semester values. Then insert another comma. The criteria for this cell range is Fall, so into the formula I will type in the text Fall in quotation marks followed by a comma. Next, I have to select my second criteria range. So I will select the column A cells that contain the year values followed by a comma. The criteria for this range is the year 2020. So, in quotation marks, I will type in the text 202. Note that you can add as many criteria as you wish. With all of my criteria entered into the formula, I will close it off by typing in a close parenthesis, then press the Enter key to calculate.

As you can see, this will save you a lot of time if you need to calculate average with specific criteria applied.

Determining the Remainder of a Simple Division

If you want to perform a simple division in Excel, you can use the Quotient function and then use the Mod function to determine the remainder. You will discover how to factorize the results of a division. If you want to perform a simple division in Excel, but you want to obtain a whole number result, you are going to need to use the Quotient function. To perform this formula, I will first click on cell D2, and then insert an equal sign followed by the text Quotient and an open parenthesis symbol. Next, I am going to need to choose my numerator value, which is the number I will be dividing from. I will select the value 36 located in cell A2 followed by a comma in the formula. Next, I need to select my denominator value, so I will select the value 5 located in cell A3. Once I have selected numerator and denominator values, I will close off the formula by inserting a close parentheses symbol, and then press the Enter key to calculate.

The result of the formula is 7 and that now displays in cell D2.

Now I know that 36 divided by 5 does not result in a whole number. So, if I want to find the remainder of the equation, I will need to use the Mod formula. To perform this formula, I will first select my cell E2. insert an equal sign followed by the function mod and an open parentheses symbol. Next, I will need to select my number and divisor. So again I will select the number 36 in cell A2 followed by a comma, and then select my divisor number, 5, in cell a3. Close off the formula with the close parenthesis symbol, and press the Enter key to calculate. The formula calculates the result 1 and displays it in cell E2.

So I can see that the number 5 goes into 36 seven times with the remainder of 1. As you can see, these formulas are quite useful if you want to retrieve whole number integers from your values within your worksheet.