7th Grade Homework (XP)

Topic

New Reply

My daughter had a homework question that I was unable to help her with. It involves making a scatter chart with a trend line. The data is given but I’m just not certain that I’m showing her the correct way…

Anyone care to offer a hand? I’d hate to give her bad information. Might lose my “good daddy” license. I’ve attached my attempt.

____________________________________________________________________________________

Assignment:

In a presidential election, a state’s electoral votes go to the candidate who gets the most votes. The candidate who gets the greatest electoral vote becomes President. The number of electoral votes for each state, which is adjusted at each census, depends on its population.

a.) Make a scatter plot. Then draw a trend line.
b.) South Dakota is one of the states not listed in the table. It’s population is about 700,000 people. Use the trend line to predict how many electoral votes South Dakota has.

Electoral Vote Data – 10 State Sample

State	              AL    AR    CO    ID    IA    KS    MN    MO    NV    OK
Population (millions)  4     2     3     1     3     3     4     5     1     3
Electoral Votes	       9     6     8     4     7     6    10    11     4     8

Thanks

Reply | Quote

Replies

Try this.

For the scatter chart, you want to set the series of state populations as your X values and the corresponding electoral votes as the Y values.

“Trend” lines require regression analysis, which in my day was beyond the scope of 7th graders. However, with Excel, all you need are the SLOPE and INTERCEPT functions which compute the slope and y-intercept of the regression line. I added another series to the scatter chart which shows the “predicted” number of electoral votes based on the regression analysis results. I then right-clicked on this new series in the chart and chose “Format Data Series”. Under the “Patterns” tab, I set the Line to Automatic and the Marker to None.

Hope that helps.

Reply | Quote

Thanks, I appreciate your assistance. [indent]

---

which in my day was beyond the scope of 7th graders.

---

[/indent]
I sure was glad to hear that. I am well beyond the 7th grade and was thinking I must have been absent ‘that day’. In any event, my daughter would not have the benefit of using Excel in class and should not be using it for homework. I believe the text book suggests that the students should be able to plot the stats on a chart and then ‘freehand’ the trendline.

I was already at the PC when she asked for help, so I was going to let Excel make quick work of the project, print it out, and say here!…it should look like this. After a short while, I had that ‘dazed and confused’ look in my eye and I think she noticed. She closed her book and said something like ‘Don’t worry daddy’.

Reply | Quote

One approach is to add the South Dakota population to the data.
Sort the data left to right.
Create a chart.
Add a trendline to the electoral vote data with a one unit backward extension.
Eyeball the result.

See attached workbook.

Regards,
Jim Cone
San Francisco, CA

Reply | Quote

Thanks – “free-handing” the trend line becomes a bit more simple once the data is sorted. Didn’t think of that…

Reply | Quote

Chipshot’s answer is correct. You can also add the equation for the trendline on the chart, so you don’t need to use the separate regression equations. After you set up the XY chart, go to Chart/Add Trendline. Select Linear, Then click on the Options tab and select Display Equation on the Chart.

Jim Cone’s answer isn’t an XY chart. Presumably you want to use the population to predict Votes. So Population should be on the X axis and Votes on the Y.

Reply | Quote

Chipshot’s answer is correct. You can also add the equation for the trendline on the chart, so you don’t need to use the separate regression equations. After you set up the XY chart, go to Chart/Add Trendline. Select Linear, Then click on the Options tab and select Display Equation on the Chart.

Jim Cone’s answer isn’t an XY chart. Presumably you want to use the population to predict Votes. So Population should be on the X axis and Votes on the Y.

Reply | Quote

Thanks – “free-handing” the trend line becomes a bit more simple once the data is sorted. Didn’t think of that…

Reply | Quote

One approach is to add the South Dakota population to the data.
Sort the data left to right.
Create a chart.
Add a trendline to the electoral vote data with a one unit backward extension.
Eyeball the result.

See attached workbook.

Regards,
Jim Cone
San Francisco, CA

Reply | Quote

Thanks, I appreciate your assistance. [indent]

---

which in my day was beyond the scope of 7th graders.

---

[/indent]
I sure was glad to hear that. I am well beyond the 7th grade and was thinking I must have been absent ‘that day’. In any event, my daughter would not have the benefit of using Excel in class and should not be using it for homework. I believe the text book suggests that the students should be able to plot the stats on a chart and then ‘freehand’ the trendline.

I was already at the PC when she asked for help, so I was going to let Excel make quick work of the project, print it out, and say here!…it should look like this. After a short while, I had that ‘dazed and confused’ look in my eye and I think she noticed. She closed her book and said something like ‘Don’t worry daddy’.

Reply | Quote

Try this.

For the scatter chart, you want to set the series of state populations as your X values and the corresponding electoral votes as the Y values.

“Trend” lines require regression analysis, which in my day was beyond the scope of 7th graders. However, with Excel, all you need are the SLOPE and INTERCEPT functions which compute the slope and y-intercept of the regression line. I added another series to the scatter chart which shows the “predicted” number of electoral votes based on the regression analysis results. I then right-clicked on this new series in the chart and chose “Format Data Series”. Under the “Patterns” tab, I set the Line to Automatic and the Marker to None.

Hope that helps.

Reply | Quote

I hope they mention in the class that it is “dangerous” to extrapolate (go outside the limits) of the data. The LOWEST value is a population of 1,000,000 and you are asked to “predict” what it would be like 700,000.

It is generally “acceptable” to interpolate (get data within the data), either thru the trend line or just using the points that it is between, but to go outside the “scope of the model” may yield very poor results.

Steve

Reply | Quote

I hope they mention in the class that it is “dangerous” to extrapolate (go outside the limits) of the data. The LOWEST value is a population of 1,000,000 and you are asked to “predict” what it would be like 700,000.

It is generally “acceptable” to interpolate (get data within the data), either thru the trend line or just using the points that it is between, but to go outside the “scope of the model” may yield very poor results.

Steve

Reply | Quote