Creating Range Names; Inserting Statistical, Date, and IF Functions; Changing Page Layout Options (40 points)

Creating Range Names; Inserting Statistical, Date, and IF Functions; Changing Page Layout Options (40 points)

1. Open the Excel file – “Excel HW1.xlsx” and save the workbook as “Excel HW1 – your Last name.xlsx”.

2. Select and name the ranges indicated: B4:B8 Quarter1; C4:C8 Quarter2; D4:D8 Quarter3; E4:E8 Quarter4; F4:F8 TotalRev.

Save your time - order a paper!

Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlines

Order Paper Now

3. Type labels in the cells indicated A14: Average revenue A15: Maximum revenue A16: Minimum revenue

4. In B14, B15, and B17, enter the functions that will calculate the average, maximum, and

minimum revenue values using the Quarter1 range name in each function

5. Insert average, maximum, and minimum functions in C14:F16 using the Quarter2,

Quarter3, Quarter4, and TotalRev range names, respectively, in each column’s functions

6. Make A18 the active cell and then type Date created

7. Enter in B18 a Date function that will insert the current date. Note: you do not want to use

TODAY or NOW functions, because the date will update each time you open the file

8. Format B18 to display the date in the format 15-Mar-2017

9. Type the label Next revision date in A19 and then enter a formula in B19 that will add

350 days to the date in B18

10. Type the label Quarterly minimum target in A21 and the value 350000 in B21

11. Format B21 to Comma Style with no decimals and name the cell MinTarget

12. Type the label Revenue target not met by in A22

13. ClassHead set the minimum target of \$350,000 for each quarter’s revenue. Calculate in

B22 the amount under target the quarter’s total revenue is by entering the IF formula:

=if(b10<MinTarget,b10-MinTarget,0)

14. Drag the fill handle from B22 to C22:E22

15. Change the page orientation to landscape, change the top margin to 1.5 inches, and center

the worksheet horizontally

16. Create a header that will print your first and last names at the left margin and the current

date and time separated by one space at the right margin

17. Create a footer that will print the file name at the right margin

18. Save as “Excel HW1 – your Last name.xlsx”

Submit all worksheets on the blackboard via this assignment link.