How to Create a Trendline for Data Points of Different Weights?

[dmmx3]

I have a scatter plot of X and Y data points. Each point is made up of some number of items. How can I weight the points so that the trendline doesn't count a point that is made up of 10 items the same as it counts a point that is made up of 100 items?

An example of my data:

X .... Y .... (Number of items making up the data point)
1 .... .459 .... (45)
2 .... .509 .... (118)
3 .... .551 .... (231)
4 .... .589 .... (218)
5 .... .609 .... (124)
6 .... .698 .... (54)

Thanks in advance for any advice.

(Also, I should note I played with doing this manually by simply copying each data point the number of times in the parentheses, and then using the much larger data set. I think this works, but I have far too much data to do this with everything. I assume there is an easier way?)

 

[dmmx3]

Thank you for the suggestion. I still get "Solver encountered an error value in the Objective Cell or a Constraint cell."

One of the cells in the worksheet became an error value when Solver tried certain values for the Variable Cells.

I tried this with just items and still get this.

Is this because the EXP(x) is returning a number that is too large?

 

[shg]

What constraint did you set?

 

[dmmx3]

fixed the #NUM issue. I wasn't doing the constraint correctly. I added a column to do the constraint calculation, and used that in the solver and set that to less than or equal to 699.

Problem now is the "solution" i am getting now, depending on the data, is something like


BetaA: 6.54964754
BetaB: -4.31525E-08
BetaC: 1.22303E-08

Basically, the B and C betas are 0, and the Fit column is almost all 1s ?

Also, it seems I do a max of 100 items at a time, right?

Thanks so much for your help on this.

 

[shg]

Post aworkbook on box.net or similar?