- Instructions For The Purpose Of Grading The Project You Are Required To Perform The Following Tasks Step 1 Instruction 1 (80.81 KiB) Viewed 39 times
- Instructions For The Purpose Of Grading The Project You Are Required To Perform The Following Tasks Step 1 Instruction 2 (76.1 KiB) Viewed 39 times
- Instructions For The Purpose Of Grading The Project You Are Required To Perform The Following Tasks Step 1 Instruction 3 (82.03 KiB) Viewed 39 times
- Instructions For The Purpose Of Grading The Project You Are Required To Perform The Following Tasks Step 1 Instruction 4 (93.49 KiB) Viewed 39 times
- Instructions For The Purpose Of Grading The Project You Are Required To Perform The Following Tasks Step 1 Instruction 5 (81.01 KiB) Viewed 39 times
- Instructions For The Purpose Of Grading The Project You Are Required To Perform The Following Tasks Step 1 Instruction 6 (29.84 KiB) Viewed 39 times
i) When we review, you will notice that we received partial credit in cell F7; this is because we got the wrong final value, but used the correct formula (3) We will now sum the rows and assign ranks [4 points] a) In cell F4, type=SUM(B4:E4), and then click Enter b) Click on cell F4; click and hold the fill handle and drag down to cell F7 c) In cell G4, type -RANK.EQ(F4,$F$4:SFS7,0) . The RANK.EQ function will assign the approproiate rank to the totals in column F start with 1 as the greatest total • The $ is used to create an absolute cell reference, or one that continues to reference the exact same cell no matter the relative position; we use this so we can drag and fill the RANK EQ column without the cell references changing d) Click on cell G4; click and hold the fill handle and drag down to cell (4) Last, we will determine during which year the scholarship committee paid out the most money [2 points] 6 a) In cell F10 type=LOOKUP(1,G4:G7,A4:A7) The LOOKUP function finds the cell with a rank of 1 in cells G4:G7, and then grabs the value in the corresponding row in cells A4:A7 5 SAVE & SUBMIT: After entering your answer in the second tab (ENTER SOLUTION HERE), save your spreadsheet by clicking FILE > SAVE AS. Make sure you DO NOT included spaces in your file name, and remember to save the file somewhere you will be able to find it, such as the Desktop. 8 Follow instructions in spreadsheet to complete ATTEMPT #2, to earn 100%. Updated: 09/16/2016 1 Current Instruction.docx 8.000 2.000 0.000 0.000 Total Points 20.000
Data ENTER SOLUTION HERE (INSTRUCTIONS: The instructions set up the scenario, and often give information you will need to figure out the solution.) INSTRUCTIONS: This year, Sam, Jo, Andi and Loren won 1st, 2nd, 3rd and 4th place, respectively, in the annual scholarship competition. The first place winner will receive the scholarship over 4 years, the second place winner will receive the prize over 3 years, the third place winner will receive the scholarship over 2 years, and the fourth place winner will receive the scholarship as a single lump sum. (DATA: You will often see data included in this first tab; you will need to use this data to complete the problem. As part of this overview, you will learn how to use "cell references") Total Prize 1st place 2nd place 3rd place 4th place $30,000 $20,000 $10,000 $2,500 (REQUIREMENTS: This is where you will see what you need to do to complete the problem. For the purposes of this demo, the original requirement is included in purple. The other requirements will complete this problem, including providing instruction on certain Excel skills.) REQUIRMENT: 1. Enter the information in the Yearly Prize Payout table to determine the total amount of money the Scholarship Competition will pay out each year. (Make sure to use cell references, formulas and functions where appropriate.) [14 points] Make sure to use the RANK.EQ function to fill out Ranks column. 2. During which year is the Scholarship Competition paying out the most money? [2 points) Enter your answer in cell F10. Make sure to use the LOOKUP function. 1) We are going to split the screen so you can see both the Data tab and the Start Template tab at the same time. [0 POINTS -- this is an excel tip: completing this will never be graded] a) To start, click View in the top tool bar. b) Click New Window (first option in fourth section of tool bar). c) Click VIEW> Arrange All (second option in fourth section of tool bar), then select Vertical and click OK. d) On the right hand screen, click on the ENTER SOLUTION HERE tab, so that you can see the Data tab on the left, and the SOLUTION tab on the right 2) All answers will be entered on the ENTER SOLUTION HERE tab; we will start by filling in the table (10 points) a) In cell B4, type then on the Data tab, click on cell B7 to create a cell reference, next type /4, as Sam will receive the scholarship divided over 4 years, and then click enter b) In cell C4, type-then on the Data tab, click on cell B8; next type /3, as Jo will receive the scholarship divided over 3 years, and then click enter c) In cell D4, type-then on the Data tab, click on cell B9; next type /2, as Andi will receive the scholarship over 2 years, and then click enter d) In cell E4, type then on the Data tab, click on cell B10, and then click enter, Loren receives the scholarship as a lump sum, so no need to divide the amount e) In cell BS, type-then click on cell B4 on same tab (ENTER SOLUTION HERE), and then click enter, since the scholarship is paid out in equal amounts, the amount paid out in th f) Click on cell B5; click and hold on the small green square in the bottom right corner of the cell (called the fill handle), and then drag across to cell DS Your cell reference is copied across cells; since it is a relative cell reference, the reference changes relative to the position of the new cell You only drag to cell DS because Loren's scholarship was completely paid out in the first year g) In cell B6, type-then click on cell B5 on the same tab (ENTER SOLUTION HERE), and then click enter b) Click on cell B6, click and hold the fill handle and drag across to cell Co You only drag to cell C6 because Loren's scholarship was completely paid out in the first year and Andi's was completely paid out after the second year 1) In cell B7, type-then click on cell B6 on the same tab (ENTER SOLUTION HERE), and then click enter
Data ENTER SOLUTION TIERE REQUIREMENT 1. Enter the information in the Yearly Prize Payout table to determine the total amount of money the Scholarship Competition will pay out each year. (Make sure to use cell references, formulas and functions where appropriate.) [14 points) Make sure to use the RANK.EQ function to fill out Ranks column. 2. During which year is the Scholarship Competition paying out the most money? [2 points) Enter your answer in cell F10. Make sure to use the LOOKUP function. (1) We are going to split the screen so you can see both the Data tab and the Start Template tab at the same time. [0 POINTS- this is an excel tip: completing this will never be graded] a) To start, click View in the top tool bar. b) Click New Window (first option in fourth section of tool bar). c) Click VIEW> Arrange All (second option in fourth section of tool bar), then select Vertical and click OK. d) On the right hand screen, click on the ENTER SOLUTION HERE tab, so that you can see the Data tab on the left, and the SOLUTION tab on the right (2) All answers will be entered on the ENTER SOLUTION HERE tab; we will start by filling in the table [10 points) a) In cell B4, type then on the Data tab, click on cell B7 to create a cell reference; next type /4, as Sam will receive the scholarship divided over 4 years, and then click en b) In cell C4, type-then on the Data tab, click on cell B8; next type /3, as lo will receive the scholarship divided over 3 years, and then click enter then on the Data tab, click on cell B9; next type /2, as Andi will receive the scholarship over 2 years, and then click enter c) In cell D4, type d) In cell E4, type then on the Data tab, click on cell B10, and then click enter, Loren receives the scholarship as a lump sum, so no need to divide the amount e) In cell B5, type then click on cell B4 on same tab (ENTER SOLUTION HERE), and then click enter, since the scholarship is paid out in equal amounts, the amount pa f) Click on cell B5; click and hold on the small green square in the bottom right corner of the cell (called the fill handle), and then drag across to cell DS Your cell reference is copied across cells; since it is a relative cell reference, the reference changes relative to the position of the new cell You only drag to cell DS because Loren's scholarship was completely paid out in the first year g) In cell B6, type - then click on cell B5 on the same tab (ENTER SOLUTION HERE), and then click enter h) Click on cell B6; click and hold the fill handle and drag across to cell C6 You only drag to cell C6 because Loren's scholarship was completely paid out in the first year and Andi's was completely paid out after the second year i) In cell B7, type-then click on cell B6 on the same tab (ENTER SOLUTION HERE), and then click enter 5) INTRODUCING ERRORS: In cell DS, type 5000; this is an error because we are not using a cell reference, we will get partial credit b) INTRODUCING ERRORS: In cell B7, type 8500, this is an error because we are not using a cell reference AND because we entered the wrong value; we will get no cred i) When we review, you will notice that we received partial credit in cell F7; this is because we got the wrong final value, but used the correct formula (3) We will now sum the rows and assign ranks [4 points] a) In cell F4, type-SUM(B4:E4), and then click Enter b) Click on cell F4; click and hold the fill handle and drag down to cell F7 c) In cell G4, type -RANK EQ(F4,SFS4:SFS7,0) The RANK EQ function will assign the approproiate rank to the totals in columnn F with I as the greatest total The S is used to create an absolute cell reference, or one that continues to reference the exact same cell no matter the relative position; we use this so we can drag and fill the RANK.EQ column without the cell references changing d) Click on cell G4; click and hold the fill handle and drag down to cell (4) Now, we will determine during which year the scholarship committee paid out the most money (2 points] a) In cell F10 type -LOOKUP(1,04:G7,A4:A7) The LOOKUP function finds the cell with a rank of 1 in cells G4:G7, and then grabs the value in the corresponding row in cells A4:A7 (SAVE & SUBMIT: After completing a project, you need to save your solution spreadsheet, so that you can upload it to be auto-graded. Make sure you do not included spaces in the file name, as
Dala ENTER SOLUT IN TIENG e) In cell B5, type then click on cell B4 on same tab (ENTER SOLUTION HERE), and then click enter; since the scholarship is f) Click on cell B5; click and hold on the small green square in the bottom right corner of the cell (called the fill handle), and then drag across to cell DS paid out in equal amounts, the an Your cell reference is copied across cells; since it is a relative cell reference, the reference changes relative to the position of the new cell You only drag to cell D5 because Loren's scholarship was completely paid out in the first year g) In cell B6, type-then click on cell B5 on the same tab (ENTER SOLUTION HERE), and then click enter h) Click on cell B6; click and hold the fill handle and drag across to cell C6 You only drag to i) In cell B7, type j) INTRODUCING ERRORS: In cell D5, type 5000; this is an error because we are not using a cell reference; we will get partial credit b) INTRODUCING ERRORS: In cell B7, type 8500; this is an error because we are not using a cell reference AND because we entered the wrong value; we will a i) When we review, you will notice that we received partial credit in cell F7; this is because we got the wrong final value, but used the correct formula cell C6 because Loren's scholarship was completely paid out in the first year and Andi's was completely paid out after the second year then click on cell B6 on the same tab (ENTER SOLUTION HERE), and then click enter (3) We will now sum the rows and assign ranks (4 points] a) In cell F4, type -SUM(B4:E4), and then click Enter b) Click on cell F4; click and hold the fill handle and drag down to cell F7 c) In cell G4, type -RANK EQ(F4,SFS4 SFS7,0) The RANK EQ function will assign the appropriate rank to the totals in column F with I as the greatest total The S is used to create an absolute cell reference, or one that continues to reference the exact same cell no matter the relative position, we use this so we can drag and fill the RANK.EQ column without the cell references changing d) Click on cell G4; click and hold the fill handle and drag down to cell (4) Now, we will determine during which year the scholarship committee paid out the most money (2 points] a) In cell F10 type -LOOKUP(1,G4:G7,A4:47) The LOOKUP function finds the cell with a rank of 1 in cells G4:07, and then grabs the value in the corresponding row in cells A4:A7 (SAVE & SUBMIT: After completing a project, you need to save your solution spreadsheet, so that you can upload it to be auto-graded. Make sure you do not included spaces in the f successfully.) SAVE & SUBMIT: (1) After entering your answer in the second tab (ENTER SOLUTION HERE), save your spreadsheet by clicking FILE > SAVE AS. Make sure you DO NOT included spaces in your name, and remember to save the file somewhere you will be able to find it, such as the Desktop. (2) Return to the Project Window where you initially downloaded your spreadsheet to submit your solution for grading. d) You are now on Step 3: click Choose File e) Select your updated spreadsheet, and click Ok f) Click Upload g) Click Submit for Grading REVIEW RESULTS: To see how you did, click on Results in My AccountingLab; then click on the assignment, and the question you just submitted.
First Year Second Year Third Year Fourth Year Sam Jo Scholarship Yearly Prize Payout Andi During which year is the Scholarship Competition paying out the most money? Loren Total Rank