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
I'm interested but you lost me, what exactly are you trying to do??
When your thread has been resolved please edit the original post in the thread ()
and amend "-[RESOLVED]-" to the end of the title and change the icon to , Thank you.
When posting Code use the [VBCode]Code Here[/VBCode] tags to be able to use the code highlighting.
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
Least Absolute Deviation *SOLVED*
Reply With Quote
)
, Thank you.
