Math 110 Guide to Using Excel
4. Working with Formulas
4.1 How Formulas are Constructed | 4.2 Some Common Excel Functions and Their Syntax | 4.3 Troubleshooting Formulas
The purpose of this page is to demonstrate how formulas are used to calculate results using Excel functions that perform calculations based on data in other cells.
Cells with formulas always begin with an equal sign (=)! Without an equal sign, Excel interprets contents of a cell as plain text or numerical data, and no calculations will be performed.
Simple mathematical operations such as addition, subtraction, multiplication, division, and exponentiation can be performed by using the + - * / ^ symbols. For example, the formula
=(A15+B15)^2/(C15*D15)
performs the indicated calculations on the indicated cells. Note that the result of the calculation will be displayed in the cell.Formulas contain one or more built-in Excel functions, which have specified syntax and may require a combination of singe cell references, cell range references, or numerical constants. You must enter a function using exact syntax as required by Excel.
To find out the proper syntax of function, use Excel Help by performing Help>Microsoft Excel Help and then typing in the function name and left-clicking Search. You may need to choose from a list of possible alternatives before you find the function that you want.
4.2.1 Functions that operate on a single cell
Common mathematical functions such as square roots, logarithms, exponential, and trigonmetric functions can be performed by Excel and use the following syntax. For example, to take the common logarithm of the number in cell A5, enter the formula
=LOG(A5)
into the cell you want to contain the calculated value.4.2.2 Functions that operate on a range of cells
SUM - SUM calculates the total of a given range of cells. For example,=SUM(A1:A10)
calculates the sum of the values in cells A1 through A10.
The following functions use the same syntax as SUM.
AVERAGE - calculates the average of a given range of cells.
MEDIAN - determines the median value in the specified range of cells.
COUNT - counts the number of cells in the given range that contain actual data.
4.2.3 Functions which calculate values based on conditions
SUMIF - Syntax: SUMIF(Criteria Range, Criteria, Sum Range)
Suppose you want to calculate the sum of set of values based on a specified condition? For example, consider this formula:=SUMIF(A1:A50,">10",B1:B50)This formula calculates the sum of only those cells in the range B1:B50 for which the corresponding cells in range B1:B50 have a value greater than 10
COUNTIF - counts only those cells in the specified range that have a value that satisfies the specified criterion, using the same syntax as for SUMIF
Excel does not have a built-in "AVERAGEIF" function, but the average of a range of cells based on a specified condition can be performed by dividing the sum by the count, as in the example:
=SUMIF(A1:A50,">10")/COUNTIF(A1:A50,">10")
You may often find that you do not get the desired results, or you may even see an error message in your cell. Some of the more frequently encountered error messages are:
- ##### - the dimensions of the cell are not large enough to display its value. Increase the width of a column by using the command Format>Column>Width, or change the number format as described in section 3.5
- #DIV/0! - division by zero, can arise also by reference to blank cell
- #VALUE! or #NUM! - the argument of a function was not of the proper type
- #NAME! - most likely indicates a typographic error