1.1.10: Basics- Functions and Formulas
- Page ID
- 56169
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\dsum}{\displaystyle\sum\limits} \)
\( \newcommand{\dint}{\displaystyle\int\limits} \)
\( \newcommand{\dlim}{\displaystyle\lim\limits} \)
\( \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}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\(\newcommand{\longvect}{\overrightarrow}\)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)Functions
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 
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.
Date and Time Functions
Excel can produce the current date and/or time, based on the computer’s regional settings. Excel can also add and subtract dates and times. For example, it may be necessary to keep track of how many hours an employee worked each week, and calculate their pay and overtime. There are numerous Date and Time functions in Excel to help calculate the addition or difference between dates or times. Excel stores all date and time values as sequential serial numbers. For example, January 1, 1900 is represented by the serial number 1, and January 1, 2020 is represented by serial number 43831 because it is 43,831 days after January 1, 1900. To view the serial number of an existing date, format the date using the General Number format. The conversion of dates and times to serial numbers simplifies the process of using dates and times in calculations. The key to displaying the results of these calculations it using the appropriate Number format, which may require diving into the world of creating custom number formats to display only days, hour and/or minutes. For example, if only the number of years is desired to be displayed when calculating the difference between two dates, the Custom Number format should be yy.
The TODAY and NOW functions are very similar since both return the current system date. However, the NOW function also returns the current system time. Many users format the results of the NOW function to only display the current system time. One characteristic of these functions versus the DATE function is that TODAY and NOW are volatile functions, so if the workbook is opened tomorrow, these functions will update automatically, whereas the DATE function will not change. These functions are for use within the grid of the worksheet, typically to calculate durations. There are separate Date and Time fields for use in worksheet headers and footers.
Creating Formulas
Perhaps the biggest benefit of using Excel is its ability to create formulas that, when written correctly, can dynamically update when predecessor data is updated. Formulas are mathematical calculations using data in the existing workbook to calculate new values. All formulas in Excel must begin with an equal sign (=), and can contain cell references, ranges of cell references, arithmetic operators, and constants as part of the formula’s syntax. Calculations in Excel follow normal math rules as it pertains to the order of operations rule. There are mnemonics available in various dialects to help decipher the order. The following graphics illustrate the order of operations rules and different formula syntax examples:
Formulas typically reference values stored in other cells. For example, in the Accounting equation: Assets = Liabilities + Owner’s Equity, one could calculate the Owner’s Equity using a formula. Assume that the value for Total Assets resides in cell D30, and the value for Total Liabilities resides in D60. The formula for Owner’s Equity in D65 using cell references would be =D30-D60. Using cell references is advantageous because if any of the cells that generate either the Total Assets or Total Liabilities is changed, the formulas in D30, D60 and D65 will also get updated. Another advantage is when a cell containing a formula is copied to another location, the formula will dynamically update to the new location’s reference information. This is called Relative Cell Referencing. If the formulas in column D are copied to column E, the new formula for Owner’s Equity in column E will be =E30-E60. Additionally, if rows are added or deleted in the range that the Total formulas reference, the formula will automatically update to the new formula range.
Entering the syntax for a formula can be accomplished through typing the formula components, using a pointing technique or a combination of both. The pointing method can help avoid typing errors. In the Owner’s Equity example, the user would start by typing the equal sign, and then instead of typing D30, just click on D30. Continue by pressing the minus sign, and then click on cell D60, and the click the Enter check mark on the formula bar. Using the point method is even more practical when defining large cell ranges in formulas. Just make sure not to reference the active cell in the formula. This will likely create a circular reference error, which is a formula in a cell that directly or indirectly refers to its own cell.
Copying and Pasting Formulas
Data in Excel can be cut, copied and pasted using similar shortcut menu options, keyboard equivalents, and ribbon icon commands as seen in Microsoft Word. Following operating system file management principles, moving data is synonymous with using the “Cut” and “Paste” commands, while duplicating data is the same as using the “Copy” and “Paste” commands. Other terminology that should be recognized is “source”, which is the cell or range to be moved or duplicated, and “destination”, which is the upper-left cell in a worksheet where the data is to be pasted.
Copying and pasting formulas contain cell references is a very powerful feature, and to expedite this process, Excel has a nifty feature called AutoFill. The AutoFill procedure utilizes the fill handle, which is the solid, green square in the lower-right corner of the active cell or cell range. Clicking and dragging the fill handle to adjacent cells (horizontally or vertically) will copy and paste the contents and update the destination cells with relative cell references or sequential data aligned with built-in Custom Lists. This should save significant time, and likely improve accuracy, and AutoFill has many uses.
Not only can AutoFill copy formulas, but it also be used to complete lists that have a recognizable pattern. It is helpful to select the source cells that demonstrate a pattern, then hover the mouse pointer over fill handle. The mouse pointer will change from the default pointer icon to a thin, black plus sign. The patterns can be numbers, dates, months, days of the week, etc. If AutoFill incorrectly guesses the series pattern, the AutoFill Options icon is available to change the paste actions. In the screenshots below, the fill handle in cell D5 will by copied to cells D6:D13. The results shown in the second screenshot will update the formula from D5 with relative cell references to rows 6:13. What is happening in cells D7:D8?
Practice 5: Test Scores
- In a new Excel workbook, enter the above data.
- Make sure to apply bold formatting to row 1, and text wrapping to cell E1.
- Format the data as a table with a header row and banded rows, but no Filter buttons.
- Apply the Facet theme.
- Ensure that cell E1 has the text wrapping format applied and then resize the column width of column E to be 7 points wide and the height of row 1 to 36 points.
- AutoFit columns B, C & D.
- In cell E2, enter the following formula: =B2+C2+D2 and click Enter. Your worksheet should look like the screenshot at right. Notice that Excel automatically filled in formulas for the rest of column E cells using relative cell references! If this action is unwanted, click the lightning icon, and choose to Undo the Calculated Column.
- Press F12 to invoke the Save As command and save the file as Test Scores.xlsx.