4.9: Adding Basic Functions to Formulas

Last updated
Save as PDF

Page ID: 14314

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

Excel contains over 400 built-in functions that can be included in a formula to perform common calculations. A function performs a calculation on data called arguments to compute a result. Arguments are variables or values the function requires and are contained within parenthesis, and usually consist of cell references, but can also contain constants, text, cell ranges, and even other functions! The syntax of a formula using a function is: function name(argument1, argument2, etc.)

Functions can be typed with the help of a ScreenTip, or added from the Insert Function window, which can be opened by clicking the Insert Function symbol symbol found at the left of the formula bar, or at the left side of the Function Library found in the Formulas tab of the ribbon.

The most popular functions are also the easiest to comprehend, which might be correlated! Some of these most popular functions are easily accessible from the AutoSum button in the Editing group on the Home tab of the ribbon. Look for the Greek sigma symbol. This feature is invaluable when it comes to entering functions, especially for common calculations computed via the SUM, COUNT, AVERAGE, MAX or MIN functions.

There are multiple ways to utilize AutoSum, but the following procedure is probably the simplest. In the screenshot below, assume the desire is to total the number of boxes of Thin Mints cookies were sold the first 15 days of February. Make B19 the active cell (the destination cell), then click the AutoSum button. The defaulting SUM option will be inserted into the formula bar with a defaulting cell range (the source cells) inside parenthesis. The cell range that defaults appears on screen with a scrolling marquee surrounding the cell range. This range is usually correct, but not always. If Excel chooses the wrong range, simply type in the cell range, or use the mouse to re-select the correct range (pointing method), and then click the Enter check mark.

The formula that defaults should be =SUM(B4:B18). Once this formula is submitted, the result, 97 will display. The intent of the SUM function is pretty self-explanatory. The AVERAGE function takes the SUM function one step further by dividing the sum of the values by the number of cells in the range. The MIN and MAX functions are used to find the smallest and largest values in a range of cells. These can be useful to identify outlier data that might skew the results. Each of these functions ignore cells that contain text or are empty. Alternatively, the COUNT function totals the number of cells that contain values. If a cell contains the value 0 (zero), it will be counted. However, if the cell is blank or contains the text zero, the cell will not be counted. This function is useful for determining the number of people who mark a checkbox in an Employee roster. For example, how many employees identify as a Veteran? Those who don’t, wouldn’t have a value, and therefore, would not be counted. If the desire if to count all cells that contain any data – use the COUNTA function.