Hi All. New to the forum, so please be gentle.
I have a bubble chart that I have a weighted trendline on. I'm not happy with how the line of best fit gives a misleading representation of the predictability of the data, hence I would like to replace this single line with a band of possible trendlines instead. (Essentially an x% confidence interval range, where x is unlikely to be greater than 80).
I appreciate that there is no one perfect answer to this problem, so I am simply looking for the best possible solution (if it exists!)
First thing to master is the mathematics behind the problem. Then I will aim to code up something in VBA (of which, my experience is limited, so any help here would be appreciated too!).
My first approach to this involved splitting up the data points into thin vertical bands (about 30 of them) and calculating the weighted standard deviation for each band, and by assuming a normal distribution of the spread within each band calculate an upper band and lower band. Then I fit a trendline to the set of upper bands and also one to the set of lower bands. Where this falls over slightly is in the bands (in the extremes where data becomes sparse) that have less than two data points in, as the variance cannot be calculated. The result (possibly influenced by this data sparsity) was that the two curves were different shapes and almost crossed at the extreme right of the chart. I feel like the lack of data in the tails should increase the distance between the lines.
I also pondered to what extent each band's spread should be influenced by it's neighbouring band's spread, but I can't work out a way to incorporate this.
The chart that I have been testing on has around 500 data points. However, I hope to repeat this process for many different bubble charts, with varying numbers of data points.
Any thoughts?
Many thanks
I have a bubble chart that I have a weighted trendline on. I'm not happy with how the line of best fit gives a misleading representation of the predictability of the data, hence I would like to replace this single line with a band of possible trendlines instead. (Essentially an x% confidence interval range, where x is unlikely to be greater than 80).
I appreciate that there is no one perfect answer to this problem, so I am simply looking for the best possible solution (if it exists!)
First thing to master is the mathematics behind the problem. Then I will aim to code up something in VBA (of which, my experience is limited, so any help here would be appreciated too!).
My first approach to this involved splitting up the data points into thin vertical bands (about 30 of them) and calculating the weighted standard deviation for each band, and by assuming a normal distribution of the spread within each band calculate an upper band and lower band. Then I fit a trendline to the set of upper bands and also one to the set of lower bands. Where this falls over slightly is in the bands (in the extremes where data becomes sparse) that have less than two data points in, as the variance cannot be calculated. The result (possibly influenced by this data sparsity) was that the two curves were different shapes and almost crossed at the extreme right of the chart. I feel like the lack of data in the tails should increase the distance between the lines.
I also pondered to what extent each band's spread should be influenced by it's neighbouring band's spread, but I can't work out a way to incorporate this.
The chart that I have been testing on has around 500 data points. However, I hope to repeat this process for many different bubble charts, with varying numbers of data points.
Any thoughts?
Many thanks
