Question

Task Instructions In cell B7, create a formula using external and internal worksheet references, that subtracts cell B9 on the Expenses worksheet of the PB Expenses.xlsx file, from cell B5 on the Income Statement worksheet of the PB Financials.xlsx file. 9:13 PM 3/25/2020 9:13 PM

Answer 1

The student must create an Excel formula in cell B7 that subtracts the value in cell B9 on the Expenses worksheet of the PB Expenses.xlsx file from cell B5 on the Income Statement worksheet of the PB Financials.xlsx file. The formula should use external references to different workbook files and internal references to specific cells.

Step-by-step explanation:

The student is asking how to create a formula in Excel that references cells across different worksheets in different workbook files to calculate a value. Specifically, the task is to subtract the value in cell B9 on the Expenses worksheet of the PB Expenses.xlsx file from the value in cell B5 on the Income Statement worksheet of the PB Financials.xlsx file.

To accomplish this, the formula in cell B7 of the worksheet where the result will be displayed should be something like the following, assuming that both files are open in Excel:

=[PB Financials.xlsx]Income Statement'!$B$5 - [PB Expenses.xlsx]Expenses'!$B$9

This formula will produce the desired calculation, incorporating both external references (to a different workbook file) and internal references (to a specific cell within a worksheet).

Answer 2

An example of a way to create a formula using external and internal worksheet references, in cell B7 is shown below:

The steps and signs to use are:

1. = - This is the equal sign that tells the start of a formula.

2. ('PB Expenses.xlsx'!'Expenses'!B9) references cell B9 on the Expenses worksheet of the PB Expenses.xlsx file.

3. - - is a sign of the subtraction operator used to subtract the value in cell B5 on the Income Statement worksheet of the PB Financials.xlsx file from the value in cell B9 on the Expenses worksheet of the PB Expenses.xlsx file.

4. )) - These parentheses enclose the entire formula to ensure the order of operations is executed correctly.

So, after one have entered this formula in cell B7, one can enter the path and filename of the PB Expenses.xlsx file in the "References" tab of the Formulas dialog box to link the formula to the external workbook.