Plot multiple instances of the same point

**jabber25** · 03-14-2010, 06:26 PM

I am trying to "weight" some data I have and then plot it to get a regression line. In the attached file, I want to divide the distance traveled by 5, to give more weight to observations that had a further distance traveled. In other words, an observation over 100 distance would have a higher weight than one over 20, by a factor of 5. Is there a way to have an xy scatter plot that would take this into account and plot 20 observations for the 100 distance and only 4 for the 20 distance?

Thanks for any help you can offer!

**teylyn** · 03-14-2010, 06:40 PM

Hi jabber,

in order to plot a data point more than once, you will need the data in the table more than once. In a XY chart, that wouldn't make much difference, though, because the points would simply overlay each other and you would not see that there are several of them.

What you can do, though, is create another column in your data table where you calculate the weighting of each data point, and then produce a bubble chart. The size of the bubble comes from that new column. A bubble chart is essentially a XY chart, but with a size factor added to the data point.

cheers

**jabber25** · 03-14-2010, 06:43 PM

Thanks for your reply. I understand that it won't look any different, however it will change the regression line. This is what I'm looking for.

Is there a way to automate the plotting of multiple points? I know I can simply add all the instances of each data point, but since I have a large dataset I did not really want to do this manually.

Thanks again!

**shg** · 03-14-2010, 07:50 PM

There are simple ways to do a weighted regression, but your data doesn't make any apparent sense. Got some that does, or can you explain what's there?

**jabber25** · 03-14-2010, 07:57 PM

The data in the example is fuel consumption data. I have observations taken over various distances. I want to give more weight to observations taken over longer distances as they will be more accurate. I would like to use a distance of 5 to weight the data. Therefore, data taken over 100 km would have a weight of 20, and therefore 20 points on the graph. I will be plotting speed vs fuel consumption. Does this make sense?

**shg** · 03-14-2010, 08:13 PM

You don't need multiple points in order to do a weighted regression.

What you want makes sense, but the numbers don't, to me. You travel 100 miles (km, whatever) at a speed of 100 and consume 20 gallons, and drive 200 miles and consume 32??

**jabber25** · 03-14-2010, 08:15 PM

Sorry, it's not consuming 20 gallons, its a fuel consumption rate (ie 20 mpg).

How does one do a weight regression in Excel?

**shg** · 03-14-2010, 08:58 PM

See attached.

The larger you make A5, the closer F5 will approximate E5.

Note that speed is ignored in the regression.

**jabber25** · 03-16-2010, 10:54 PM

Thanks for your help with this. I don't understand why there is a negative and positive linest (nor do I understand what this is actually providing). If I now wanted to plot this, how would I make sure the fit line includes the weighting?

Thanks again