# Homework Assignmet

**Queen Mabella**

These instructions are compatible with both Microsoft Windows and Mac operating systems.

San Diego Sailing keeps data about its fleet of rental and charter boats. One of the sheets is missing a piece of data and another sheet has circular reference errors. You will complete work on these sheets, calculate projected rates for each boat, and build basic statistics about past rentals.

**[Student Learning Outcomes 2.1, 2.2, 2.3, 2.4, 2.5, 2.6, 2.7]**

File Needed: **SanDiegoSailing-02.xlsx***(Available from the* Start File *link.)*

Completed Project File Name: **[your name]-SanDiegoSailing-02.xlsx**

**Skills Covered in This Project**

**Skills Covered in This Project**

- Create and copy formulas.
- Use formula auditing tools.
- Set mathematical order of operations.
- Use relative, mixed, and 3D cell references.
- Use
*COUNTIF*and*SUMIF*functions. - Build an
*IF*formula. - Insert the
*TODAY*function.

**NOTE**: If group titles are not visible on your *Ribbon* in *Excel for Mac*, click the **Excel** menu and select **Preferences** to open the *Excel Preferences* dialog box. Click the **View** button and check the **Group Titles** check box under *In Ribbon, Show*. Close the *Excel Preferences* dialog box.

- Open the
**SanDiegoSailing-02***.*workbook.**xlsx** - If the workbook opens in
*Protected View*, click the**Enable Editing**button so you can modify it. - The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
- Review a formula.
- Click the
**New Prices**sheet tab and review the*Formula bar***.** - Click cell
**D5**. The formula begins with**1+D4**.

- Click the
- Edit and copy a formula with mixed references.
- Edit the formula in cell
**D5**to show**D$4**instead of "D4." The formula should multiply one plus the percentage value in cell**D4**by the current rate on the**Fleet**sheet**($H5)**. With an absolute reference to row 4 and column H on the**Fleet**sheet, you can copy the formula down the column (Figure 2-105).**Figure 2-105 Mixed reference in the edited formula** - Copy the formula in cell
**D5**to cells**D6:D19**without formatting to preserve the border. - Select cells
**D5:D19**and drag the*Fill*pointer to copy the formulas to cells**E5:E19**. - Click cell
**E6**. The formula is adjusted to use the percentage value in cell**E4**in place of cell**D4**. Note also that the reference on the**Fleet**sheet**($H6)**is adjusted to show the correct row.

- Edit the formula in cell
- Build a formula with mixed references.
- Click cell
**F5**and type =(1+ to start the formula. - Select cell
**F4**and make it an absolute reference to the row but not the column. - Type
**)***for the closing parenthesis and multiplication. - Click cell
**I5**on the**Fleet**sheet, and make the reference absolute for the column but not the row (Figure 2-106).**Figure 2-106 Mixed references in the new formula** - Copy the formula down column
**F**without formatting to preserve the border. - Format cells
**F5:F19**as**Currency**and then copy cells**F5:F19**to cells**G5:G19**.

- Click cell
- Build an
*IF*function formula.- Click the
**Fleet**sheet tab and select cell**G5**. Rental boats with a stove in the galley must seat 8 or more people. - Create an
*IF*function in which the*logical_test*argument determines if there are 8 or more seats. - Use Yes for the
*value_if_true*argument. Use No for the*value_if_false*argument. (If you type the formula, enclose the text arguments within quotation marks.) - Copy the formula to cells
**G6:G19**without formatting and then center the data in column G.

- Click the
- Insert the
*TODAY*function in cell**B21**. Format the date to show the month spelled out, the date, and four digits for the year (January 1, 2020). - Create a division formula.
- Click the
**Bookings**sheet tab and select cell**F5**. Calculate average revenue per passenger by dividing the fee by the number of passengers. - Build the division formula.
- Copy the formula in cell
**F5**to cells**F6:F19**.

- Click the
- Create and copy a
*COUNTIF*function to count bookings by boat manufacturer.- Select cell
**D27**. - Start the
*COUNTIF*function from the*Statistical*category by clicking the**More Functions**button in the*Function**Library*group. - Use cells
**$C$5:$C$19**as the*Range*argument. - Set a
*Criteria*argument that will select all boats in the “Beneteau” group. The criteria is**ben***. If you type the formula, include quotation marks. - Copy the formula in cell
**D27**to cells**D28:D30**. - Edit the criteria in each copied formula in cells
**D28:D30**to reflect the boat make.

- Select cell
- Create and copy a
*SUMIF*function to calculate total revenue by boat make.- Select cell
**E27**. - Start the
*SUMIF*function with cells**$C$5:$C$19**as the*Range*. - Set the
*Criteria*argument to ben*. - Set the
*Sum_range*argument to cells**$E$5:$E$19**. - Copy the formula in cell
**E27**to cells**E28:E30**. - Edit the criteria in each copied formula in cells
**E28:E30**as needed.

- Select cell
- Complete formatting.
- Apply the
**Currency**format to all values that represent money. - Format the labels in cells
**A1:A2**as**18**point. - Select cells
**A1:F2**and click the**Alignment**launcher [*Home*tab,*Alignment*group]. Center the labels across the selection. - Merge and center the label in cell
**C25**over cells**C25:E25**and format it at**16**points. - Bold and center the labels in rows
**4**and**26**. - Select cells
**A4:F19**and apply**All Borders**. Do the same for cells**C25:E30.** - Center the page horizontally.

- Apply the
- Save and close the workbook (Figure 2-107).
**Figure 2-107 Excel 2-5 completed** - Upload and save your project file.
- Submit project for grading.

- 3 years ago
- 25

**Answer(1)**

Purchase the answer to view it

- Jennifer.Anderson-SanDiegoSailing-021-1.xlsx

**other Questions(10)**

- short video report
- job Dissatisfaction
- Q-1 What are the emerging technologies? Q-2 What is the need for new management approach? Note: 1. Write an assignment at least 300 TO 350 words with the cations. 2. Indicate at two source or reference in your original post. 3. I uploaded in Turnitin pape
- Spanish test
- Write a 350 essay on low-cost provider strategy. See details below for further guidance.
- Mathematics
- bus control mid exam
- Discussion.2
- App
- due in 24 hours