Formulas and Functions in Spreadsheets
Today we will be working on using formulas and functions to analyze data in a spreadsheet. We have a sample spreadsheet of the K-8 student count at Calvary to work with for this exercise.
Today we will be working on using formulas and functions to analyze data in a spreadsheet. We have a sample spreadsheet of the K-8 student count at Calvary to work with for this exercise.
- Open Google Classroom. Find the assignment "Formulas and Functions" on the classroom stream. Open the file named "Students at Calvary."
- Click on Row Header 1. This will select the entire Row 1. Click on the Insert menu. Click on Row Above. This will insert an extra row at the top of the spreadsheet.
- Click and drag your mouse from A1 - D1. This will select cells A1 to D1. Click the Merge Icon to merge the cells. Change the Horizontal Alignment to Center.
- In your newly merged row typed Students At Calvary. Change the Font Size to 36.
- Click on cell D3. Type =B3+C3. Press Enter.
- Use the Autofill Handle to copy the formula in D3 to cells D4 through D11.
- Click on cell B12. Click the Function Icon. Click SUM. Type B3:B11 between the parenthesis. Press Enter.
- Use the Autofill Handle to copy the function in B12 to cells C12 through D12.
- Click on cell B13. Click the Function Icon. Click AVERAGE. Type B3:B11 between the parenthesis. Press Enter.
- Use the Autofill Handle to copy the function in B13 to cells C13 through D13.
- Click on cell B14. Type =MEDIAN(B3:B11) in the cell. Press Enter.
- Use the Autofill Handle to copy the function in B14 to cells C14 through D14.
- Click on cell B15. Type =Mode(B3:B11) in the cell. Press Enter.
- Use the Autofill Handle to copy the function in B15 to cells C15 through D15.
- Click on cell B16 Type =Max(B3:B11) in the cell. Press Enter.
- Use the Autofill Handle to copy the function in B16 to cells C16 through D16.
- Click on cell B17. Click the Function Icon. Click MIN. Type B3:B11 between the parenthesis. Press Enter.
- Use the Autofill Handle to copy the function in B17 to cells C17 through D17.
Using Absolute Cell Reference In Google Sheets
This lesson is designed to demonstrate the difference in relative cell references and absolute cell references in spreadsheets.
This lesson is designed to demonstrate the difference in relative cell references and absolute cell references in spreadsheets.
- Open the Multiplication Table file in Google Classroom
- Select cells B1 and C1. Click and drag the Autofill Handle to cell F1. 101 - 105 will now display along the top row of your spreadsheet.
- Select cells A2 and A3. Click and drag the Autofill Handle to cell A6. 51 - 55 will now display along the top row of your spreadsheet.
- Select cell B2. Type =$A$2*B1 in the Formula Bar.
- Click and drag the Autofill Handle to cell F2 to copy the formula into the rest of the row. Notice the first cell reference in the problem does not change, but the second cell reference does.
- Select cell B3. Type =$A$3*B1 in the Formula Bar.
- Click and drag the Autofill Handle to cell F3 to copy the formula into the rest of the row. Notice the first cell reference in the problem does not change, but the second cell reference does.
- Select cell B4. Type =$A$4*B1 in the Formula Bar.
- Click and drag the Autofill Handle to cell F4 to copy the formula into the rest of the row. Notice the first cell reference in the problem does not change, but the second cell reference does.
- Select cell B5. Type =$A$5*B1 in the Formula Bar.
- Click and drag the Autofill Handle to cell F5 to copy the formula into the rest of the row. Notice the first cell reference in the problem does not change, but the second cell reference does.
- Select cell B6. Type =$A$6*B1 in the Formula Bar.
- Click and drag the Autofill Handle to cell F6 to copy the formula into the rest of the row. Notice the first cell reference in the problem does not change, but the second cell reference does.
Making A Form In Google
In this lesson we will practice creating a survey using a Google Form and analyzing the responses.
In this lesson we will practice creating a survey using a Google Form and analyzing the responses.
- Open the Survey file in Google Classroom
- Change the Form Title to a name that matches the topic of your survey.
- Type your first question. Use a multiple choice question.
- Give 3 - 4 options for the answer. Also include an "Other" option.
- Type your second question. Use a multiple choice question.
- Give 3 - 4 options for the answer. Also include an "Other" option.
- Type your third question. Use a multiple choice question.
- Give 3 - 4 options for the answer. Also include an "Other" option.
- Under setting, change "Who can respond?' to "Anyone in Calvary Lutheran School."
- Check "Automatically collect respondent's Calvary Lutheran School username" and "Can submit only 1 response (requires login)."
- Send your survey to all your classmates. Use the list of email address attached to the assignment.
- Everyone should answer all the surveys sent to them.
- Once you have receive all your responses, print screen to show each of the charts analyzing the responses.
- Attach your screen prints to you assignment before you turn it in.