Excel Math Formulas Basics

A function in Microsoft Excel is a predefined formula–arithmetic, textual or statistical–that perform calculations using data values in a particular order. Excel contains various built in functions that can be used to quickly compute the sum, average, count, range, percentage, maximum and minimum value for a range of cells. In order to use these functions efficiently, you will need to understand different parts of function as well as the process of creating arguments upon which the operation can be performed, that can be explicitly entered or be provided as cell references, to compute a value.

Functions are important because they make calculations more efficient. For example, consider the following formula: =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10</strong. This adds up the values in cells A1 to A10.

Instead of specifying each value that needs to be added, like in the above formula, a shorthand notation or function can be used to perform long calculations with ease and efficiency while providing stability to the spreadsheet. The SUM function can be used to add up a range of cells: =SUM(A1:A10)

All functions of MS Excel are available under Function Library on the Formulas Tab.

Parts of a Function

In order to correctly use a function, such that effective operation can be performed and the desired value can be computed, a function must be written in a specific manner, which is also called the syntax of a function. The basic syntax of a function is the equal sign (=), followed by the built-in function name, such as PRODUCT, and one or more arguments. The function arguments contain the information of the values upon which the function works and produces the result. The function arguments can be data values separated by commas (For Example: PRODUCT (4, 7, 9)), cell references separated by commas (For Example: PRODUCT (A1, A2, A3)) or cell ranges (For Example: PRODUCT (A1:A3)). The function arguments need to be enclosed within brackets and can contain one argument or multiple arguments, depending on the logic performed by the function.

For example, going back to the SUM function, multiple arguments may also be separated by comma: =SUM (A1, A2, A3, A4, A5, A6, A7, A8, A9, A10)

Though there exist approximately 400+ functions in MS Excel, only a few of the functions are needed to perform calculations in day-to-day life. The functions which are relatively less used can also be performed by following a detailed procedure further discussed in upcoming sections.

Common Functions Used in Excel

  1. SUM: This function is used to add all the values of the cells specified in the function argument.
    For example:

    =SUM (A1:A10)

  2. AVERAGE: This function is used to compute the mean or average of all the values of the cells specified in the function argument by calculating the sum of the data values and dividing the sum by the number of data values added to produce the result.
    For example:

    =AVERAGE (A1:A10)

  3. COUNT: This function is used to count the number of cells with numerical data values in the function argument.
    For example:

    =COUNT (A1:A10)

  4. COUNTA: This function is used to count the number of cells with non-void or filled data values in the function argument.
    For example:

    =COUNTA (A1:A10)

  5. COUNTBLANK: This function is used to count the number of cells with filled or void data values in the function argument.
    For example:

    =COUNTBLANK (A1:A10)

  6. COUNTIF: This function is used to count the number of cells based upon a specific criteria in the function argument.
    For example:
    =COUNTIF (B2:B5,”Argentina”)
    =COUNTIF (B2:B5,B9)
  7. COUNTIFS: This function is used to count the number of cells based upon multiple criteria in the function argument.
    For example:
    =COUNTIFS (B2:B5,”Argentina”, B12:B15, “India”)
    =COUNTIFS (B2:B5, B9, B12:B15, B19)
  8. MAX: This function is used to determine the highest cell value included in the function argument list.
    For example:
    = MAX (A1:A10)
  9. MIN: This function is used to determine the lowest cell value included in the function argument list.
    For example:
    =MIN (A1:A10)
  10. IF: This function is a conditional mathematical function used to return a result to an IF condition. This is logically similar to a computation of a condition and corresponding result, as defined by the user.

    For example:
    The following IF statement creates a conditional statement in MS Excel to check the value in B2 and return “OK” if it’s greater than or equal to 3, “Not OK” if it’s less than 3:
    =IF (B2>=3, “OK”, “Not OK”)

  11. TRIM: This Excel function, though not exactly mathematical, can minimize errors in the results of mathematical formulae due to trailing or in-between spaces that lurk in the spreadsheet when an Excel command is carelessly used.

    For example: In order to trim extra spaces in Column A of the spreadsheet, the following formula can be entered in the formula bar of Cell A1, and can further be copied down in the column:
    =TRIM (A1)
    It will eliminate all extra spaces in cells but a single space character between words.

  12. LEN: This function of MS Excel is used to calculate the number of characters or simply, the length of a cell. This is especially useful when we are handling with arrays or strings of characters.

    For example: In order to calculate the length of a string contained in cell B5, the following command can be used to compute the result:
    =LEN (B5)

  13. AND and OR: This is a mathematical function commonly used in MS Excel in order to compute Logical Statements and their corresponding results. They are widely used in MS Excel and facilitate the working of logical statements in Excel. The working of this formula is closely related to Boolean statements on multiple criteria.

    Working:
    AND returns TRUE (Or 1) as the result if all of the conditions are satisfied, FALSE otherwise.
    OR returns TRUE (Or 1) as the result if any of the conditions are satisfied, FALSE otherwise.

    For example, to check the values in 2 columns and return “Nice” if both values are greater than 10, the following IF formula can be used, using an AND statement:
    =IF(AND(A1>10, B1>10), “Nice”, “”)
    If only of the values in 2 columns need to be checked, and “Nice” can be returned, (either A1 or B1), then the IF formula along with the OR statement can be used:
    =IF(OR(A1>0, B1>0), “Nice”, “”)

Creating Function Using the AutoSum Command

As the name suggests, AutoSum command is used to automatically compute the SUM of the data values present in a specified range of cells. However, contrary to the name, the AutoSum command allows the user to automatically use some built-in functions including SUM, AVERAGE, COUNT, MIN, MAX, etc.

The following steps need to be performed sequentially in order to perform the AutoSum function:

  1. Select the cell where the final result of the AutoSum command would be computed.
  2. Click on AutoSum command available in the last Editing section under Home Tab of Menu Bar, and click on the arrow next to AutoSum command to perform the desired function.
  3. MS Excel will automatically select a cell range as the argument for the chosen function and the average would be computed. However, if Excel selects wrong cell range, the desired cell range can be manually entered as the argument for the function.
  4. Press Enter on Keyboard so that the function result would be computed and the result would appear in the chosen cell.

NOTE: The AutoSum command can also be obtained or accessed from the Formula Tab on Menu Bar under Function Library Section.

Creating Function Manually

In case you already know the function name, then the function can easily be created by typing it manually, abiding by the syntax rules.

The following steps need to be performed sequentially in order to create function manually:

  1. Select the cell where the final result of the function would be computed. Here, we choose H21 as the resultant cell to compute the average of working hours per month of the members of the society.

  2. Go to the Formula Bar, available under the Menu Bar, and start creating the function by typing the equal to sign (=) followed by the function name, which in this case is AVERAGE. You can also select the desired function name from the list of suggested functions that appear as soon as the equal to sign (=) is typed.
  3. Enter the cell range for the function as the argument within the parentheses. In this case, the desired cell range would be G19:R19.
  4. Press Enter on the keyboard so that the function result would be computed and the result would appear in the chosen cell.

Using the Function Library

While there is a large diversity of pre-defined functions contained in Excel, only a few of them are actually required in practical life. Nonetheless, the method of using Excel functions should be known in order to use them, whenever the need arises.

A Function Library is a collection of Excel pre-defined functions based upon an argument list, that can be used to perform a variety of operations and trigger a value.

To access the Function Library, select the Formulas Tab on the Menu Bar and use the options available under Function Library Section to use the diverse variety of functions.

Creating Function using the Function Library

The Function Library section under Formulas can be used to perform a variety of diverse functions. Let us exploit this functional feature using an example of calculating the number of cells containing no data values (void cells) in a range of cells. The function that will be used to perform this feature will be COUNTBLANK, which is a Statistical Function.

The following steps need to be performed sequentially in order to count the number of empty/blank cells in Excel:

  1. Click on the cell in Excel where you want the result to appear.
  2. Click on Formulas Tab on Menu Bar, click on More Functions option under the Function Library section, and further click on Statistical.
  3. Click on COUNTBLANK function.
  4. Select the range of cells on which the built-in formula needs to be applied under the Function Arguments dialog box.
  5. Click on OK.

The result or count of the number of empty or blank cells would be computed.

The following value can also be computed using a shorter method, described here:

  1. Click on the cell where you want the result to appear.
  2. Click on Formula Bar, available under Menu Bar and type the function following the syntax:
    =COUNTBLANK(Range_Of_Cells)
    where Range_Of_Cells is given by CellAddress1:CellAddress2
  3. Press Enter key on keyboard.

The result or count of the number of empty or blank cells would be computed.

Using Insert Function Command

When a user does not know the category of the function to which it belongs, the usage of the formulae may pose a problem for the user which can be solved by using the Insert Function command. Although this function can be found simply by using a simple trial and error method, the Insert Function command can also be used.

To access the Insert Function, select the Formulas Tab on the Menu Bar and click on Insert Function command under Function Library.

Let us exploit this functional feature using an example of calculating the number of cells containing data values (non-void cells) in a range of cells. The function that will be used to perform this feature will be COUNTA, which, otherwise, is a Statistical Function but will be performed using Insert Function Command.

The following steps need to be performed sequentially in order to count the number of non-empty cells in Excel using the Insert Function command.

  1. Click on the cell in Excel where you want the result to appear.
  2. Click on the Formulas Tab in the Menu Bar, and further, click on the Insert Function option under the Function Library section.
  3. The Insert Function Dialog Box opens up. Select the function to be used from the Select a Function list and click on OK.
  4. Select the range of cells on which the built-in formula needs to be applied under the Function Arguments dialog box.
  5. Click on OK.

The result or count of the number of non-empty cells would be computed.

The following value can also be computed using a shorter method, described here:

  1. Click on the cell where you want the result to appear.
  2. Click on Formula Bar, available under Menu Bar and type the function following the syntax:
    =COUNTA(Range_Of_Cells)
    where Range_Of_Cells is given by CellAddress1:CellAddress2.
  3. Press Enter Key on keyboard.

The result or count of the number of non-empty cells would be computed.

Advantages of Using Mathematical Excel Functions

Excel functions are often used to store, manipulate and organize financial data. Thus, a variety of operations can be performed using Excel mathematical functions. Some of them are listed below:

  1. Arithmetic calculations: Summing, Subtracting, Multiplying, or Dividing cells or range of cells using SUM, SUBTRACT, PRODUCT and so on, in Excel function library.
  2. Obtaining Profit and Loss in Financial Data using SUM and SUBTRACT functions in Excel.
  3. Obtaining Mean, Mode, Maximum and Minimum values in a range of statistical or mathematical values using AVERAGE function or a combination of mathematical functions.
  4. Sorting data on the basis of a desired feature to find specific information.
  5. Formulating conditions and organizing data on the basis of a specific condition applied to a cell or range of cells.
  6. Carrying out a ‘What-If’ Analysis on data using IF function.
  7. Trimming lurking spaces and avoiding redundancy errors using TRIM function.
  8. Organizing statistical data in the form of graphs or chats, to make it presentable and for simplified analysis of data.
  9. Importing Data from other database programs for simplified analysis.
Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!