Insert a mean trendline or best fit line

[jones1234]

Hi,

I'm currently working on a project that compares infiltration rates on soils on grass and in forest. I have a dataset for both soils and I plotted them in a scatter (see attachment). I would now like to create some kind of mean line, a best fit line through the data points of grass and soil in order to make a better comparison. Can anyone help me? I tried the exponential and moving avarage, but they do not quite fit the data well enough; I would really like a smooth best fit line. If this can't be done in Excel, does anyone know a good program of software that can?

Thanks already!

[MarvinP]

Hi jones and welcome to the forum,

Have you tried a polynomial fit of your data? In my 2015 version of excel I an get it to the 5th degree polynomial. I'm not sure what else you can do for trend lines.

See http://best-excel-tutorial.com/56-charts/143-trendlines for all the current trend line options.

[Kaper]

Have you checked power trend line?

For your blue points you can apply it immediatelu and (in my opinion) it fits quite reasonably.

For red - it is not that easy because you have some points with zero value in column E. But if you do not use this values (I simply deletred them but you may show values and use additional series (with zeros removed) for trend line.

By the way - once zeros are removed - have a look on a copy of the plot with vertical axis on logarytmic scale - for me such graph seems easier to follow.

Last but not least - have you tried biblography seach? may be there are some (more or less generaly accepted) models for the fenomenon you are showing on a plot? Then of course the type of equation used there would be the best choice for your fitting.