Most of the straight line fitting around is based on least squares.
Does anyone have a routine to calculate a LAD (L1 norm) fit?
Printable View
Most of the straight line fitting around is based on least squares.
Does anyone have a routine to calculate a LAD (L1 norm) fit?
OK I have a solution.
See attached file (LAD.xls within LAD.zip)
I have broken the equation down to its component parts that Excel can cope with.
So you have a column relating to the x-axis (frequencies in the attached file)
And a column relating to the data to be fitted (S11, S12, S21, S22 etc)
These are selected in the UI and copied to the Output cell.
Values of 1 are guessed for m and c
A column containg y=mx+c is created
A column containing magnitude of y minus data is created
A cell summing that column is created
It then calls the solver function to solve for the smallest sum by changing m and c
Does this make sense?
Does anyone care? :p
I'm interested but you lost me, what exactly are you trying to do??
I am trying to fit a straight line (y=mx+c) to a series of points on a graph.
If you use the Linear trendline function in Excel it uses a least squares
method to generate the line of best fit.
Becasue some of the data is wild, I don't want it to affect the trendline,
hence using an L1 norm method to generate the line of best fit instead.
For anyone that doesnt know, L1 norm is the same equation as least squares
(L2 norm) but where the equation for L2 norm is squared (raised to the
power 2) L1 norm is raised to the power 1 instead
To see this in action take a point on the graph and multiply it by 100.
The Excel Trendline will shoot off not looking like a best fit, however,
the L1 norm will hardly change at all.
A good analogy is the mean and median averages.
e.g
1, 2, 3, 4, 5, 6, 7, 8, 666
Mean = 78
Median = 5