7.5: Functions

Last updated
Save as PDF

Page ID: 46522

\( \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}}\)

Learning Objectives

Use the COUNTIF function.
Use the IF function.

Excel can perform a variety of really nice data analysis features for you. We’ve already touched upon how you can filter data. But you can also look for other connections, or screen large numbers of cells to determine how often something occurs.

COUNTIF

COUNTIF is a way for you to ask Excel to count how many times a certain piece of information appears in your worksheet. For example, perhaps you want to know how often “shirt” appear in an inventory list. All you need to do is ask Excel to count the number of cells that contain the word “shirt.”

Determine which cells you want Excel to look at. In our example, we will look at A2 though A13.
Click on the cell you wish your count to be displayed in.
Type the formula for a count
=COUNTIF(A2:A13, “shirt”)

Data has been entered in Columns A and B through row 14.

Here you are telling Excel which cells to examine—A2 through A13—and what to look for: “shirt.” Note that your text must match exactly what is typed in the cells, and if you are looking for a specific word it needs to be enclosed in quotation marks (so “shirt” instead of shirt).

Hit enter and your results will appear.

Practice Question

https://assessments.lumenlearning.co...essments/18640

IF

Another commonly used function in Excel is the “IF” function. In this case, you are asking Excel to look for something and then tell you if that something occurred. For example, perhaps you want to compare whether your monthly expenses were under your monthly budget. That is the scenario we will look at in our example.

In this case, let us just ask for a simple “yes” or “no” answer. Looking at the screenshot below, you can see how the worksheet has all the data at hand. We are looking for whether the information in the C column is less than the information in the B column. We would like the D column to display the answer (yes or no).

Data has been entered in Columns A, B, and C through row 13.

Click on D2 and enter the “IF” function for what you want Excel to compare and do.
=IF(C2<B2, “Yes,””No”)

You do not have to manually reenter the formula into the other cells in D. Instead you can copy and paste the formula from D2 into D3, D4, and so on. Each time you do this, the formula should automatically update with the correct cell number to compare.

As you can see, the D cells begin to display “Yes” or “No.” “Yes” means that the expenses in the C column were less than the monthly budget entered into the B column. “No” means that expenses were higher than the budget. Just as in the COUNTIF function, you need to enclose text in quotation marks.

Data has been entered in Columns A, B, C, and D through row 13.

Practice Question

https://assessments.lumenlearning.co...essments/18641

Contributors and Attributions

Functions. Authored by: Shelli Carter. Provided by: Lumen Learning. License: CC BY: Attribution