Excel Project Project 1 Revised CMPSC203 Penn State

Project 1 Revised CMPSC203 Penn State

Project Description: 
his project will be graded partially manually. The score right after uploading to MyITLab is not the true value. It will be modified after we grade your file. You must upload your own file from MyITLab to get credit.

Instructions: 
For the purpose of grading the project you are required to perform the following tasks: 
Step    Instructions    Points Possible
1    Change the file name into Project 1 CMPSC203 Last_First.    0.000
2    Enter your name in F2. Label F2 as “MyName”. Enter your birthday in F4, and label F4 as “MyBirthday”.    0.000
3    On the “Date Calc” worksheet, use the NOW function in F1 to show the current date and time.    4.000
4    Enter a formula to show the current hour in F3.    4.000
5    Enter in F5 an if-function to show either “am” or “pm” depending on the current hour in F3.     4.000
6    Enter in F7 an if-function to show the current hour with am/pm. For example, if the current time is 13:45, it must show “1pm” after entering the formula. Use “&” in the formula to concatenate “1” with “pm”. Label F7 as “CurrentHour”.    4.000
7    Enter in F9 a nested if-function to show “Morning”, “Afternoon” or “Evening” depending on the current hour in F3. It should show “Morning” before 12pm and “Afternoon” between 12pm and 6pm. See the hint in H9. Label F9 as “InGreeting”.     5.000
8    On the “Front Page” worksheet, modify a formula to show greeting with your name in B6. For example, if your name is “Barbra” and the current time is 2:30pm, it should show “Good Afternoon Brabra!”    4.000
9    In I6, enter the formula ="Now it's after " & CurrentHour & ", " & MONTH(TODAY()) & "/" & DAY(TODAY()) & "/" & YEAR(TODAY()) &"."    0.000
10    In F11 on the “Date Calc” worksheet, calculate the difference between MyBirthday and Today(). Change its format into number with no decimal places. Label it as “AgeInDays”.     4.000
11    Fix the formula in B8 on the front page to show your age in days correctly.     4.000
12    Fix the formula in I8 to show your age in minutes.     4.000
13    On the Date Calc worksheet, fill in F18 correctly to answer the question in B17.     0.000
14    Fill in F20 to answer the question in B20. See the hint in F21.     4.000
15    Fill in F23 to calculate which of the week your birthday was. Use the VLOOKUP-function with the table J17:K23. Name F23 as “DayOfMyBirthday”.     5.000
16    On the front page, fill in I10.    0.000
17    Enter the formula to find your birth year in F25 on the Date Calc worksheet. Label it as “BirthYear”.    4.000
18    In F27, use an if-function to show the answer (yes/no) to the question in B27. See H27 for hint. Name F27 as “LeapYear”.     4.000
19    Fill in I14 on the front page.    0.000
20    On the “Leap Years” worksheet, click A2. Use Home-->Editing-->Fill-->Series button to create a column series. The step value is 1, and the stop value 2016.     0.000
21    Keeping A2 clicked, create a table with the range selected by Excel.     0.000
22    In B2, enter the formula to calculate if A2 is a leap year or not. (It’s the same formula as F27 on the Date Calc worksheet.)     4.000
23    In C2, enter the formula to calculate if A2 is your birth year or after it. The answer is yes/no again.    4.000
24    Enter the formula in D2 to calculate if B2 and C2 is true. Use the AND function to check if B2=”yes” and C2=”yes”. The answer is given as TRUE or FALSE.     4.000
25    Enter the formula in E2 to show 1 if D2 is TRUE, and 0 if it is false.     0.000
26    Add the total row. Label E118 as “LeapYearAnswer”.    4.000
27    Fill in I14 on the front page.    0.000
28    Make sure the three worksheets are ordered in Front Page, Date Calc, and Leap Years. Upload to MyITLab.     0.000
    Total Points    70.000