Excel 2019 Formulas and Functions

Every formula in Excel starts with a ______

• =

• !

• ?

• $

Choose the answer that shows a RELATIVE cell reference

• =C4

• =$C$4

• =4^3

• =($C$1*.5)+$D$1

Choose the answer that shows a formula with an ABSOLUTE cell reference

• =B1*$C$4

• =A3

• =B1*C4

• =4^3

A RELATIVE reference in Excel means that the cell reference does not change when the formula is copied

• True

• False

Answer explanation

A RELATIVE reference changes when a formula is copied in Excel. When this formula is cpied down, Excel will change the formula automatically. If you copied it down one more cell, it would enter "=B7*C7".

An ABSOLUTE cell reference in Excel means that the cell reference does change when the formula is copied.

• True

• False

Answer explanation

An ABSOLUTE cell reference is used in Excel when you do not want the cell reference to change. You can make a cell reference ABSOLUTE by adding the $ before each character- $D$11. Use the F4 button to make a cell reference ABSOLUTE.

Choose the answer that references a cell

• C4 or $C$4

• 4*12

• Mitochondria

• Red Blood

Which answer references a cell range

• =SUMIF(A1:A13,"Brass",C1:C13)

• B4

• B1+C1

• Archery

Excel can find the average of a range of numbers by using the _________ function.

• AVERAGE

• MAX

• MIN

• SUM

The _______ function counts the numbers in a range of cells.

• COUNT

• AVERAGE

• SUM

• COUNTIF

The _______ function joins multiple items using a delimiter (like a space, period, or comma). The first argument defines the delimiter, the second determines if blank cells will be ignored, and the remaining arguments are the cells that have the data that will be concatenated.

• TEXTJOIN

• LEFT

• MID

• PROPER

Answer explanation

Example:

=TEXTJOIN(" ",TRUE,B1:B2)

If cell B1 had "Mr" and B2 hd "Montague", the formula would show "Mr Montague".