Defined Names for working more effectively with data Test your skills

While the calculation of Other Expenses is looking better it is still not correct. Open the Name Manager.

Have a look at the named ranges for Ex_Rate and Other, they only go to row 14, which explains the incorrect calculation. Edit Ex_Rate to go from L11:L21 and change Other to go from J11:J21. Click OK and close the Name Manager.

Other Expenses has been corrected. What is the total for Other as shown in K6?

Let’s fix Total Transportation Costs next. Open the Name Manager, there is a named range called Travel_Costs, but this is the wrong name. Change it to TravelCosts and click OK

What is the value for Transport as shown in K3 (one or two decimal places only)?

Next, Accommodation Costs, use any method you think suitable to give the name Accommodation_Costs to range F11:F21.

What is the total for Accommodation as shown in K4 (one or two decimal places only)?

And now to fix meals, let’s be efficient and use Create from Selection to name all three ranges simultaneously. Select G10:I21 and click Create from Selection.

What is the total cost of Meals as shown in K5 (one or two decimal places only)?

Our Travel expense calculator worksheet is now looking good, but we would also like to complete a breakdown of expenses by region. Start with adding the following named ranges: E11:J14 – London , E15:J18 – Paris , E19:J21 – Jakarta . Now go to the Summary By Region worksheet and observe the calculated values for London.

What is the summary value for London in the local currency? (no commas)

Stay on the Summary By Region worksheet. Enter a formula in C5 to add up the total amount spent in Paris (used the named range you just created). Then do the same in C6 for Jakarta.

Using the named range and the SUM function, what are the formulas to use here?

Note: you should have one answer for Paris and one answer for Jakarta. Just type the answer for one of these as your answer to this question.

Stay on the Summary By Region worksheet. In D5 create a calculation to convert Euros to Dollars by multiplying the Euros spent (C5) by the exchange rate for Euro (which uses the named range EUR). Perform a similar calculation to convert the Indonesian Rupees to dollar (using the correct named range).

What is the formula in D5?

Click in D7 (still in Summary By Region), and use Autosum to get the total spent in USD.

What is this value? (no commas, no characters, no dollar signs)

Click in B9 (still in Summary By Region), and use the Paste Names tool to Paste all the named ranges into your workbook.

What are the contents of cell C28? (Cut and paste your answer here to avoid errors.)

Look at the Travel expense calculator worksheet. What is the value of Total Meals in cell K5? (no characters or commas)

Still on the Travel expense calculator worksheet. What is the value of Total Other Expenses in cell K6? (no characters or commas and rounded to the nearest whole number with no decimal point)

How many rows are in your Name Manager?

In the Travel expense calculator sheet, what is the value of Total Trip Expenses in K7 (rounded to the nearest dollar, input as a number with no “$” and no commas)?

In the Travel expense calculator sheet, what is the value of L21 (to three decimal places)?