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?
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?
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
_______
"Lee" <[email protected]> wrote in message
news:[email protected]...
> 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?
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 C2:D2 as far down as you have data (row 101 in this example).
In E2 enter the formula =SUM(D2:D101) where 101 is the last row of the data
set.
Now, use Solver (Data | Solver...) to minimize E2 by changing C2:D2.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article <[email protected]>,
[email protected] says...
> 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?
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks