Excel project 3 instructions: part 1: functions covered in this

Excel Project 3 Instructions:
Part 1: Functions covered in this exercise: Grouping & formatting worksheets, create static
data consolidation with SUM and AVERAGE functions and create and edit hyperlinks.
 
Open the Project 3 workbook in Excel. Follow the directions below. 
1.  Group the first three worksheets (San Diego, La Jolla and Carlsbad).
2.  Edit and format grouped worksheets.
a.  In A28, type First Quarter, 2015 in bold font.
b.  Merge and center across cells A1:B1. Merge and enter cells A2:B2.
c.  Edit A10 to read Cash paid for publicity.
d.  Ungroup sheets.
3.  Click the Summary sheet tab.
4.  Consolidate the case received amounts in the Cash flow from operations section.
a.  Select cells B4:B6.
b.  Use Sum to consolidate the static data from the three location sheets without links to
the source data.
5.  Consolidate the cash paid amounts in the Cash flow from operations section.
a.  Select cells B7:B12.
b.  Use Sum to consolidate the static cash paid amounts on the Summary sheet.
6.  In the Cash flow from banking and investment section on the Summary sheet, use Sum to
consolidate the static cash received amounts in cells B15:B19 without links to the source data.
7.  Consolidate the cash paid amounts for the CashFlow from banking and investment section on
the Summary sheet.
a.  Use cells B20:B21 for the static consolidation.
b.  In the message that no data was consolidated, click OK.
8.  Use Sum to consolidate the cash balance at the beginning of the quarter amounts in B24 on
the Summary sheet.
9.  Insert a hyperlink on the Summary worksheet.
a.  Click cell D3 on the Summary worksheet.
b.  Create a hyperlink that displays San Diego Data and switches to the San Diego
worksheet
10. Copy and edit a hyperlink.
a.  Right-click the hyperlink in cell D3 and choose Copy from the menu.
b.  Paste the hyperlink in cell D5.
c.  Right-click cell D5 and choose Edit Hyperlink from the menu. 2
 
d.  Edit the hyperlink to read La Jolla Data and to switch to the La Jolla worksheet.
e.  Right-click the La Jolla Hyperlink and choose Orange, Accent 6, Darker 25% as the
Font Color from the mini toolbar.
f.  Copy either hyperlink to create a third hyperlink for the Carlsbad sheet in cell D7. Use
Purple, Accent 4, Darker 25% as the font color.
g.  Test the hyperlinks.
11. Continue work on the next exercise in the workbook.
 
Part 2: Functions covered in this exercise: Nested IF, COUNTIFS, MATCH and INDEX
 
Click in Refurbish Sheet and follow the directions below.
1.  Nest INDEX and MATCH functions in N3 on the Refurbish tab to determine the list price of cars
based on the Stock No. number in N2.
a.  Select N2 and type the number 125.
b.  Click cell N3 and enter the INDEX function.
c.  Select A3:K15 for the array argument.
d.  Use MATCH(N2,B3:B15,0) for the MATCH function row_num argument.
e.  Use MATCH(“List Price”,A3:K3,0) for the MATCH function column_num argument. The
result is $11,800.00
f.  Click N2 and type 127. The result changes to $22,000.
2.  Use typing to create another nested INDEX and MATCH function in cell N4 to determine the
Compression based on the Stock No. number in N2.
a.  Use the same argument ranges listed in step 2
b.  Hint: use absolute reference symbols to the original function as required; then copy the
formula to N4 for easier editing.
c.  Apply Comma formatting to the result in N4. The Compression for Stock No. number 127 is
3,000.
3.  Nest an AND and an IF function in A4 to determine if you refurbish a car based on the number of
Cylinders, Valves and list price in E4, F4 and I4, respectively.
a.  Select A4 on the Refurbish tab.
b.  Use E4=N12, F4=N13, and I4<N14 for the AND function arguments.
c.  For the IF function, type “Yes” for value_if_true argument and “No” for value_if_false
argument.
d.  Edit the formula to include absolute symbols to all references necessary within the formula.
e.  Copy the formula in cell A4 to cells A5:A15 without formats. Center align the column to
show the results. The result in A4 is No.
4.  Apply the AVERAGEIFS function to cell N7 on the Refurbish tab to determine the average list
price for car on more than .75 size containing more than two Cylinders. 3
 
a.  For the Average_range argument, select cells I4:I15.
b.  For the Criteria_range1 box, select cells K4:K15 for sizes.
c.  For the Criteria1 box, type >.75
d.  For the Criteria_range2 box, select cells E4:E15 for the number of Cylinders.
e.  For the Criteria2 box, type >2. The result is $18,600.00.
5.  Copy the AVERAGEIFS function in cell N7 to N9 on the Refurbish tab to determine the average
list price for a car on less than .75 size containing more than two Cylinders.
a.  Apply absolute references as needed in the AVERAGEIFS in cell N7 and copy it to N9. 
b.  Edit the formula in N9 so the Criteria1 is “<.75”. The result is $11,866.67.
6.  Apply COUNTIFS to cell N8 on the Refurbish tab to determine the number of cars less than four
engine with over 2000 compression.
a.  For the Criteria_range1 argument, select cells G4:G15 for the Compression. 
b.  For the Criteria1 box, type >2000.
c.  For the Criteria_range2 box, select cells H4:H15.
d.  For the Criteria2 box, type <4. The function result is 3.00.
7.  Save and upload your Project 3 workbook to Blackboard.
 

Academic Brigade
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.