Trend Line weighted by Population

  • Thread starter Thread starter Gee...
  • Start date Start date
G

Gee...

I'm working a trendline for a graph. The data looks like
this:
POPULATION AVERAGE COUNT OVERERT OCCUR.
27 4,720 3,533
40 16,248 9,282
21 24,247 9,376
17 34,147 8,940
3 45,984 18,192
1 58,422 13,398
1 63,196 8,673
1 70,217 31,491
1 80,697 22,527
I'm charting Occurance by Count. When I just use those
two, I get an R-squared of .699, when I stretch it out
(the first set 27 times, the 2nd 40 times, the third 21
times like:
AVERAGE COUNT OVERERT OCCUR.
4720 3533
4720 3533
4720 3533
4720 3533
4720 3533
4720 3533 ETC.)
I get an R-squared of .8465 which is a much more accurate
number and what my boss prefers this. How can I do this
easier?
Thanks,
Gee...
 
Add a plot of Count*Population and Occurance*Population. Put this new
series on the secondary axis (both x and y), display the trendline,
then hide the secondary axis and the points. Of course, the trendline
will not really look like it represents the visible data.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top