Bubble Charts and fitted trendlines

  • Hi all. I hope one of you can help me with this.


    I've created a bubble chart to show some data that I have. I now want to show a fitted (straight) line through this. I can do this with the built-in facility in excel, but this just fits the line as if it were an ordinary scatter plot. I want it to take account of the size of the bubbles as well. (The technical term is to do weighted least squares rather than ordinary least squares).


    Excel doesn't have any functions built in that will do WLS. So I searched the web and found the addin at http://www.mrflip.com/resources/ExcelFunctions that will calculate the fitted line.


    But the problem now is that Excel won't let me add the line because this would need to be as a mixed chart and it says bubbles can't be mixed with other chart types. Agghh!


    Can anyone help? My last resort is to just draw a line on and try to get it in the right place, but that isn't very elegant.


    Thanks,
    Rob

  • Can you attach an example of you workbook withthe chart or at least a picture?


    I know that you can apply a trendline directly to a bubble plot as per attached and even extract a formula for line of best fit but I think this falls short of what your chasing.


    I'm assuming that this is an accounting function? I'm mainly engineering but will help if I can.


    AJW

  • See if this trick helps.


    Essentially I have overlaid a Scatter Plot onto the Bubble Plot with the same scaling and a transparent background.


    A bit of a fiddle but it may be of use.


    AJW

  • See example attached. I've plotted the data and added a trendline in Excel. You'll see if you change all the Z's to 1 that the trendline doesn't move - Excel doesn't take into account the size.


    I've then calculated the slope and intercept in G4:H4 using LINEST. You'll see that these are the same as excel has used for the trendline.


    I've then used the "MrFlip" addin to calculate the weighted least squares fitted line in J3:K3. (this uses 1/Z because by default it >Z to mean greater uncertainty). As you would intuitively expect, the slope is much shallower because of the effect of the large data point E. I've drawn this line on the chart approximately.


    My question is: is it possible to put this line on the chart as a proper data series, so it updates automatically when the data changes?


    Thanks,
    Rob

  • I just thought of one potential solution - but it's rather messy:


    I could plot it as a scatter and then use X and Y error bars to represent the size. Because this is a scatter plot it would then allow me to put another series in to represent the ends of the fitted line, and Excel will allow a line between the points.


    As far as I'm aware, there is no way to get excel to add lines to the bubble chart type - is this correct? (I could then add a second series of bubbles with very small size and have then joined with a line).


    Rob

  • Hi,


    I have attached an example of how I used to produce bubble charts before they became built in.


    It uses a shape to generate the bubble.
    This shape is then used as a custom data marker.


    The code uses the size value an a fixed bubble size to create the chart.


    Doing this on a standard xy scatter chart then allows you to add an additional reference line.


    Cheers
    Andy

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!