2 Formulas and functions
2.1 What is a formula?
You are creating a formula in a worksheet called Summary. This formula also includes a reference to cell H2 in a worksheet called Products. How will this reference appear in the formula?
β¬ ProductsH2
β Products!H2
β¬ H2
2.2 Controlling calculations
You are creating a calculation in your worksheet. You want Excel to subtract a figure held in C5 from a total held in H2 and then for the result to be divided by four. Which formula will calculate this correctly?
β¬ =H2-C5/4
β =(H2-C5)/4
β¬ =C5-H2/4
2.3 Controlling calculations in action
A Microsoft Excel worksheet contains the following formula:
=A2*B3/C2+D4
Which operator in this calculation will Excel process first?
β Multiplication
β¬ Division
β¬ Addition
2.4 Self-Review: Calculating Profit and Margin
Question 1
When you created the calculation in cell G4, you used the double-click Autofill shortcut to copy the formula into the cells below.
What visual reference did Excel use to determine when to stop copying?
β¬ The block of information in column K.
β The block of information in column F.
β¬ The block of information in row 201.
Question 2
You created a formula in cell H4 to calculate the total shipping cost.
Cell P1 contained the shipping charge ($5 per item).
Column F contained multiple item quantities. Which formula ensures that Autofill copies it correctly across multiple rows?
β
=F4*$P$1
β¬=$F$4*P1
β¬=F4*P1
Question 3
In cell J4, you created a formula using parentheses to calculate a new retail price by adding two costs together and applying a percentage increase.
What was the correct result?
β¬
$222.16
β$333.24
β¬$224.66
2.5 Knowledge Check: Formulas in Excel
Question 1
You are creating a formula in cell C2 in your worksheet. The calculation only needs to appear in cell C2. You have typed the following formula:
A3*D15/45*F17
However, there is an error in the syntax of this formula. What part of the formula is missing?
β¬ Parentheses
β An Equals Sign
β¬ Dollar Signs
Question 2
Cell D2 of your worksheet contains the weekly sales total. Cell D7 contains the overall monthly sales.
You need cell E2 to show what percentage the week one total is of the overall monthly total.
Youβve already applied the percentage format to E2. Which of the following options is the correct syntax for the formula?
β¬
=D7/D2
β¬=D2/D7*100
β=D2/D7
Question 3
True or False:
When Excel processes the following formula, it will first multiply cell A1 by cell C3 before it processes the addition.
=(B15+D45)-(A1*C3)
β¬ True
β False
Question 4
You have created the following formula in your worksheet:
=D4+$C$6-E4+F6
You use the Autofill feature to copy the formula down the column. Which of the cell references will update with new row numbers?
β D4, E4, F6
β¬ D4, C6, E4
β¬ C6, E4, F6
Question 5
What mathematical sign does Excel process last when calculating the results of the following formula?
=D4+C3/N5*B3
β The plus sign.
β¬ The Division sign.
β¬ The Multiplication sign.
2.6 What is a Function Formula and How to Write It
You need to access the full range of functions available in Excel.
Which ribbon do you need to select to access the full library of functions?
β¬ The Data ribbon.
β¬ The Home ribbon.
β The Formula ribbon.
2.7 Using the INSERT Function
You need to access the Insert Function feature to create a function formula.
What actions can you take to access this feature? (Select all that apply)
β¬ Use the Name box beside the Formula bar.
β Use the FX symbol to the left of the Formula bar.
β Use the Insert Function choice on the Formulas ribbon.
2.8 Self-Review: Preparing a Monthly Sales Report
Question 1
Cell C34 in your worksheet is blank, and cell C35 contains a formula. The cursor is on C36.
If you use the AutoSum shortcut to add a SUM formula, which cell range does Excel automatically suggest as the argument for the function?
β¬ C34
β C35
β¬ C36
Question 2
True or False: When you created the Total Revenue formula in C35, the results appeared as dollar amounts because the Accounting format had been applied to the cell in advance.
β False
β¬ True
Question 3
When you created the formula in C39 to count how many days there were in the month, what answer did the formula produce?
β¬ 31
β¬ 0
β 30
2.9 Knowledge Check: Getting Started with Functions
Question 1
Which of the following items are part of a function formula? (Select all that apply)
β Parentheses
β¬ Periods
β An equals sign
β The function name
Question 2
You need to count the number of cells with content in a selected range. The range includes both text and numbers. Which function should you use?
β¬ COUNTBLANK
β COUNTA
β¬ COUNT
Question 3
Cells D7, D8, and D9 contain the numbers 10, 20, and 30, respectively.
You enter the following formula in cell D10:
=MAX(D7:D8)
What result will Excel display in D10?
β¬ 30
β 20
β¬ 10
Question 4
Cells B1 to B3 contain numbers, while cell B4 contains text.
You apply the following formula in B5:
=AVERAGE(B1:B4)
How does Excel calculate the average?
β Total the numbers in B1 to B3 and divide by 3.
β¬ Total the numbers in B1 to B3 and divide by 4.
β¬ Total the numbers in B1 to B3 and divide by 5.
Question 5
Cells E4, E5, and E6 contain the values 300, 300, and 0, respectively.
You apply the following formula:
=AVERAGE(E4:E6)
What is the correct result?
β¬ 300
β 200
β¬ 100
2.10 Module Quiz: Formulas and Functions
Question 1
When you open a workbook, what does Excel recalculate?
β All formulas in the entire workbook that require recalculation.
β¬ Only the formula in the currently selected cell.
β¬ All formulas in the active worksheet.
Question 2
Cell A4 in your worksheet contains the value 10.
Cell A5 contains the value 100.
Cell A6 contains the value 2.
In cell A7 you have created the following formula:
=10*100/2
If you change the value in A6 to 4, then what would the result of this formula be?
β 500
β¬ 750
β¬ 250
Question 3
Row one of your spreadsheet contains the following formula:
=A1*C1/$B$1
If you use the Autofill feature to copy the formula down to the next row, what will the formula in row two be?
β **=A2*C2/$B\(1** β¬ =A2*C2/\)B\(2 β¬ =A2*C1/\)B$1
Question 4
You have created a formula in your worksheet which reads:
=(300+30)/(10-7)
What would the answer be?
β 110
β¬ 296
β¬ 330
Question 5
Cell C7 in your worksheet contains the value 300, and cell A4 has the entry 12.
Which of the following calculations would correctly calculate 12% of the value in C7?
Both A4 and the formula cell have the General format applied. (Select all that apply)
β **=C7/100*A4**
β¬ =C7/100*12%
β **=C7/100*12**
Question 6
You are creating a worksheet to track team performance.
You know that the team increased their sales in May.
The teamβs April sales figures are in cell C5, and their May sales figures are in D5.
Which one of the following calculations would show the percentage increase?
β =(D5-C5)/C5
β¬ =(C5-D5)/D5
β¬ =(D5-C5)/D5
Question 7
Cell F7 contains a value of 100.
Cell G7 has a value of 200, and cell H7 is empty.
What is the correct answer for the following formula?
=AVERAGE(F7:H7)
β 150
β¬ 200
β¬ 100
Question 8
You are working with the Number 1 and Number 2 boxes in the Function Arguments dialog in the Insert Function wizard to add a SUM function formula.
The Number 1 entry is bolded. What does this indicate?
β You must enter a value or cell reference in this box.
β¬ You do not need to enter anything in this box.
β¬ You have entered an incorrect cell reference in this box.
Question 9
True or False:
You are using the Insert Function feature to add a formula to your worksheet.
Youβve switched to the category Math & Trig to find a function.
The list of functions is organized in the order that they were recently used.
β¬ True
β False
Question 10
As you type a function formula into a cell, a help message appears to remind you of the arguments required.
How does the message display the optional arguments?
β The arguments are contained in square brackets.
β¬ The arguments are italicized.
β¬ The arguments are in bold.
2.11 What features were new to you and which were most useful?
While learning about formulas and functions in Excel, I discovered several useful techniques that will make my data analysis tasks more efficient. The use of absolute and relative cell references was particularly valuable, as it helps maintain accuracy when copying formulas across multiple cells.
I also found the Autofill and Insert Function (fx) features extremely helpful for saving time and reducing errors when working with large datasets.
Among the functions, I found COUNT, COUNTA, and AVERAGE immediately applicable to my daily work. Exploring the Formula ribbon also introduced me to new categories like Logical and Text functions, which I plan to use in upcoming reports.
Overall, this module improved my confidence in writing and managing formulas effectively, and I look forward to applying these techniques in real-world Excel projects.