Absolute Value Trendline

[Guest]

Hi,

I'm working on a Mechanical Engineering lab with a plot of data in the form
of an absolute value function. Is there any solution to allow Excel to plot
a line of best fit as an absolute value function?

 

[Jon Peltier]

In a range of your worksheet, convert the values into absolute values using
the ABS() worksheet function. For example, if the values are in column B,
starting in B2, in C2 enter

=ABS(B2)

and fill this formula down as far as you need. Plot this data and add a
trendline to this series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

 

[Tushar Mehta]

I interpreted the question very differently than Jon.

You can "roll your own" trendline with the help of Solver.

Suppose your data set is in A2:B101, with x values in col. A.

Then, designate C1 and D1 as cells that will hold the slope and intercept
respectively.

In C2 enter the formula =$C$1*A2+$D$1. Note the use of both absolute and
relative addressing.

In D2 enter =ABS(B2-C2).

Copy C22 as far down as you have data (row 101 in this example).

In E2 enter the formula =SUM(D2101) where 101 is the last row of the data
set.

Now, use Solver (Data | Solver...) to minimize E2 by changing C22.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions