USING MICROSOFT EXCEL 2016 Independent Project 6-5 (Mac 2016) Independent Project 6-5 (Mac 2016 Version) Classic Gardens and Landscapes counts responses to mail promotions to determine effectiveness. You use SUMIFS and a nested IF formula to complete the summary. You also calculate insurance statistics and convert birth dates from text to dates. Skills Covered in This Project Nest MATCH and INDEX functions. Create DSUM formulas. Build an IFS function. • Build SUMIFS formulas. • Use DATEVALUE to convert text to dates. Step 1 Download start file Open the ClassicGardens-06 start file. Click the Enable Editing button. 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. Create a nested INDEX and MATCH function to display the number of responses from a city. Click the Mailings sheet tab and select and name cells A3:D28 as Responses Click the Mailing Stats sheet tab. Click cell B21 and type Carthage. Click cell C21, start an INDEX function, and select the first argument list option. Choose the Responses range for the Array argument. Click the Row_num box and nest a MATCH function. Select cell B21 for the Lookup_value and cells A3:A28 on the Mailings sheet for the Lookup_array. Click the Match_type argument box and type 0. Click INDEX in the Formula bar. Click the Column_num box and nest a second MATCH function to look up cell D3 on the Mailings sheet in the lookup array A3:D3. Click the Match_type box and type 0 (Figure 6-105). Important: There is a known bug in Excel for Mac that places plus signs ( + ) instead of commas ( , ) between the arguments when using the Formula Builder. If this is the case in your Excel for Mac version, replace the plus signs with commas. Excel 2016 Chapter 6 Exploring the Function Library Last Updated: 3/27/19 Page 1 USING MICROSOFT EXCEL 2016 Independent Project 6-5 (Mac 2016) Format the results to show zero decimal places. Type Smyrna in cell B21. Use DSUM to summarize mailing data. On the Mailings sheet, note that number sent is located in the third column and response data is in the fourth column. Click the Criteria sheet tab. Select cell B2 and type lan* to select data for the Landscape Design department. Click the Mailing Stats sheet tab and select cell B7. Use DSUM with the range name Responses as the Database argument. Type 3 for the Field argument, and use an absolute reference to cells B1:B2 on the Criteria sheet as the Criteria argument. Copy the formula to cell C7 and edit the Field argument to use the fourth column. Complete criteria for the two remaining departments on the Criteria sheet. Click the Mailing Stats sheet tab and select cell B8. Use DSUM in cells B8:C9 to calculate results for the two departments. Use SUM in cells B10:C10. Format all values as Comma Style with no decimal places. Create an IFS function to display a response rating. IMPORTANT: If you are using a version of Excel that does not include the IFS function, create a formula using nested IF functions instead where each Value_if_false argument is the next IF statement. The innermost nested IF statement should have a Logical_test argument of C7/B7<10%, Value_if_true argument of $C$18, and Value_if_false argument of 0. Click cell D7. The response rate and ratings are shown in rows 14:18. Start an IFS function and select C7 for the Logical_test1 argument. Type / for division and select cell B7. Type >= 20% to complete the test. Click the Value_if_true1 box, select C15, and press F4 (FN+F4) (Figure 6-106). Excel 2016 Chapter 6 Exploring the Function Library Last Updated: 3/27/19 Page 2 USING MICROSOFT EXCEL 2016 Independent Project 6-5 (Mac 2016) Click the Logical_test2 box, select C7, type /, select cell B7, and type >=15% Click the Value_if_true2 box, click cell C16, and press F4 (FN+F4). Complete the third and fourth logical tests and value_if_true arguments (Figure 6-107). Copy the formula in cell D7 to cells D8:D10. Use SUMIFS to total insurance claims and dependents by city and department. Click the Employee Insurance sheet tab and select cell E25. Use SUMIFS with an absolute reference to cells F4:F23 as the Sum_range argument. The Criteria_range1 argument is an absolute reference to cells E4:E23 with Criteria1 that will select the city of Brentwood. The Criteria_range2 argument is an absolute reference to the department column with criteria that will select the Landscape Design department. Complete SUMIFS formulas for cells E26:E28. Format borders to remove inconsistencies, if any, and adjust column widths to display data. Use DATEVALUE to convert text data to dates. Click the Birth Dates sheet tab and select cell D4. The dates were imported as text and cannot be used in date arithmetic. Select cells D4:D23 and cut/paste them to cells G4:G23. Select cell H4 and use DATEVALUE to convert the date in cell G4 to a serial number. Copy the formula to cells H5:H23. Select cells H4:H23 and copy them to the Clipboard. Select cell D4, click the arrow with the Paste button [Home tab, Clipboard group], and choose Values (Figure 6-108). Format the values in column D to use the Short Date format. Hide columns G:H. Apply All Borders to the data and make columns B:D each 13.57 wide. NOTE: Some versions of Excel 2016 for Mac use inches for row height and column width. When viewing the column width, if double quotes appear when displaying the value, enter 1.17” instead of 13.57. Excel 2016 Chapter 6 Exploring the Function Library Last Updated: 3/27/19 Page 3 USING MICROSOFT EXCEL 2016 Independent Project 6-5 (Mac 2016) Step 2 Upload & Save Step 3 Grade my Project 9. Save and close the workbook (Figure 6-109). 10. Upload and save your project file. 11. Submit project for grading. Excel 2016 Chapter 6 Exploring the Function Library
Why Choose Us
Quality Homework Writing
Do you need to ace your test? We have qualified writers to assist you in writing 100% quality homework writing. Mycoursewriter offers students unique and personalized custom papers that help them in getting good grades. Our academic experts will assist you with your dissertation papers, thesis writing, research papers, term papers, and case study homework help. Our writers are experienced and have perfected the art of crafting the best essay writing papers regardless of your academic level. Order now and get our superb homework writing services.
Expert Academic Writers
A student needs professional assistance outside class time. Before hiring our writers, they undergo thorough tests and strict educational backgrounds. After passing a test, our writers undergo training to ensure they deliver expert professional papers to our customers. We ensure they meet the timely delivery of papers and follow our customers' provided instructions. Every writer is an ENL writer and is from the USA, Canada, UK, or Australia. Our experts approach papers effectively depending on their area of specialization.
Affordable Price Guarantees
Is the deal too good to be true? Mycoursewriter is the most affordable assignment help website on the market. We consider students' daily needs for academic help. Our paper costs are reasonably priced to approximately 90% of students around the globe. Our main aim is to ensure students achieve academic excellence, and we believe inconsiderable prices should not hinder students from getting quality homework writing help. In case of any dissatisfaction, we offer unlimited revisions and rewrites without extra charges.
Timely delivery of papers
Tight deadline? Is your paper due in the next few hours? Worry no more. Our writers are here for your rescue. Many college students find it hard to balance their social and academic life and get caught up with academic deadlines. Drafting your paper at the last minute does not allow you to conduct extensive research and craft the correct answers. We have expert writers ready to work on your assignment writing. Urgent writing services are the ultimate solutions you need. We guarantee you timely delivery of papers at your convenience and specified time delivery of the customer.
100% Original Tasks
The ultimate deal-breaker in achieving good grades is submitting imitative papers. Plagiarism is a breach of examination standards and has serious consequences. Students need to deliver papers written in their understanding by paraphrasing their research findings and relevant citations. Our expert's primary consideration is providing free plagiarized papers that meet the lecturers' requirements. Our editors will proofread your paper before delivery ad ensure your papers will not be substandard. We guarantee extreme originality, and in case of any customer concerns, we offer a plagiarism report.
All Round the clock Customer Support
Our website is user-friendly and super useful! Mycoursewriter is the most trusted online writing website always available to provide you with assignment writing services that you need. Our primary concern is meeting customer need specifications by our friendly customer support team that Is available all the time. Our website usability is self-learnable but in case of any problem, feel free to contact us directly through our website email: firstname.lastname@example.org. We have diligently satisfied thousands of students, and you can witness this on our sites' review page.
Try it now!
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.
Advantages of Using Our Assignment writing Services
Mycoursewriter welcomes you for superb essay writing services. We are a legit site offering help with your most challenging homework writing assignments at affordable prices. Your essay paper will be assigned to a professional writer who will craft the paper from scratch according to your provided instructions.
Essay writing is a common assignment writing for most college students. It takes a lot of time to craft a complete perfect essay. Mycourserwiter offers the best essay writing and proofreading services for most college students. Your paper is assigned to an expert writer who does thorough research on fresh ideas and personalizes your essay paper according to your assignment writing guidelines. Our essays are entirely exceptional and undergo a strict procedure that includes papers free from plagiarism and thorough proofreading.
Admission and Business Papers
Writing an admission paper is not every student's desire, and this should not hinder any student from earning an admission. Writing a perfect personal statement to earn a position in a dream school or job can be stressful. Personal statements should display a high level of confidence and uniqueness. Admission essays require you to have a vast knowledge of the current happenings in the world and knowhow of the business world. We have qualified writers with skills who will write your admission paper through comprehensive research. All you have to do is provide us with little details about yourself, instructions and our writers will craft a perfect paper for you.
Editing and Proofreading
Mycoursewriter offers you assignment writing services free from grammatical and formatting mistakes. An error-free paper is significant in scoring the desired maximum grades. We have a team of editors who will go through your paper after our expert writers have delivered their fully crafted papers. Our writers will cover all the aspects of your paper instructions, such as fonts, spacing, and educational formats. Our team of editors will check through your paper to make sure it meets the formatting style provided in the paper instructions. Under no circumstances will a poorly formatted paper will be forwarded to you as our team of editors will ensure careful proofreading.
Assignment writing is part of a learning process, and one may not have the knowledge to answer all questions correctly. This should not be a stress to you as we have qualified essay writing experts to help you. Our qualified writers are holders of PhDs, Degrees, and Masters in different areas of study and can handle any papers regardless of the complexity. Multiple assignments may be challenging to finish on time. Our skilled professional writers are here to assist you in crafting all the correct answers for your assignment. Stop stressing about your homework writing and place an order with us, and our expert writers will provide you with the best assignment help.