linear trend lines
can anyone help?
I have to put 95% confidence intervals around a linear trend line. How
do I work out the values for the trend line? I am using a scatter
graph with 2 sets of data.
Please help!
---
~~ Message posted from http://www.ExcelForum.com/
The confidence bound for the estimated line at x is
=FORECAST(x,known_y's,known_x's) +/- TINV((1-conf)*2,COUNT(known_y's)-2)
*STEYX(known_y's,known_x's)
*SQRT(1/COUNT(known_y's)+(x-AVERAGE(known_x's))^2/DEVSQ(known_x's))
the confidence bound for an individual observation at x is
=FORECAST(x,known_y's,kn...
how do I graph a linear equation?I want to graph the linear equation y = 2x + 4 in microsoft excel. How do I
do this? On my TI-83 Plus I could easily do this. I also know I could create
a data table and use the table of values to chart the line in excel. But I
want just enter the equation in excel and have excel graph it. Thanks.
On Sun, 9 Jan 2005, in microsoft.public.excel.charting,
Throstle <Throstle@discussions.microsoft.com> said:
>I want to graph the linear equation y = 2x + 4 in microsoft excel. How do I
>do this? On my TI-83 Plus I could easily do this. I also know I could create
>a data table and ...
linear trend lines do not appear gaps in dataLinear trend lines will not appear when specified. I have a few gaps in my
data. I suspect that is why they will not work. Can anyone verify this
limitation and/or tell me how to get around it.
Hi,
Gaps in your data should not stop you from being able to add a trendline.
Where version of excel are you using?
What chart type do you have. Note that none of the 3d effects charts allow
for trend lines.
Cheers
Andy
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"wirepusher" <wirepusher@discussions.microsoft.com> wrote in message
news:5039B903-5E68-4196-AC08-...
Linear FittingHello
I have a 512*256 data file. What I want to be able to do is create a new matrix by doing the following
First, I want to calculate the linear fit equation for each row (the equation is different for each row). Then, I want to replace every value in that row with the value stored there minus the linear fit equation, where x is just the index number for the row. I want to be able to do this for all 512 rows, ultimately creating a whole new data set
Let me just provide you with an example so it's clear what I am asking. So in my data file there are 256 numbers in the first row. T...
linear charting with zerosHave a line chart covering many cells. Data is added to each cell daily.
The data cells use a formula. Problem is, the formula evaluates to zero
without a value in the formula's source cell. Thus, the line chart, for
future values, goes to zero.
Is there a way to have the line on the chart simply "end" without showing
zero out to the end of the data range?
Thx.
--
Bill
Hi,
You need to adjust your formula so it evaluates to #N/A rather than zero.
=IF( <test> , <value> , NA() )
Line charts will not display markers for #N/A and the line is interpolated ...
Linear Optimizationhow can I optimize with integer variables?
Assuming you are using Solver, add a constraint that specifies the
variables to be 'Int' (w/o the quotes).
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article <E214DF54-5098-41E1-9DD9-74DD53017D30@microsoft.com>,
FaranakNegahdar@discussions.microsoft.com says...
> how can I optimize with integer variables?
>
...
Using Solver with Linear Constraint(s)Excel 2002 SP3
Win XP Pro SP2
*Follow-up to: microsoft.public.excel*
Hi,
Sorry but I have "writer's block" right now as regards to a spreadsheet
solution I need.
It's a simple problem but I am just drawing a blank at the moment....
Let me try to describe it:
I have a cell chosen as the target for solver e.g. M25 = 1% (total
market share)
And a range as the cells to change by solver e.g. J4:J23 (market share
as % at each period e.g. month or quarter, etc.)
Each cell needs to progressively grow until reaching 1%. And the rate
of growth of the cells in J4:23 needs to...
Using Excel's Solver for Linear Programming ProblemI would like to solve a Linear Programming problem using Excel's Solver
as follows:
Min C = 100*X1 + 150*X2 + 120*X3
Subject to constraints:
X1 + X2 + X3 = 6
X1 + 2*X2 + X3 >= 8
X1 + X2 + 2*X3 <= 9
How do I set it up to arrive at the solutions?
fcharn -
Browse to www.solver.com, click "Solver Tutorial," and click "Can you show
me step by step?"
Or, search for and look at the SolvSamp.xls workbook, which is installed on
your hard drive when Solver is installed.
- Mike
www.mikemiddleton.com
<fcharn@aol.com> wrote in message
news:1139...
How do I anchor one end of a linear trend line?I want to display a linear trend line to project an end point at a future
time. I have a fixed starting point and want a linear extrapolation from
that point. The standard linear trend line moves the start point to give a
straight line fit to the data, but that's not what I am trying to do.
In article <3CB2B42C-9FDB-4308-885D-052125CA25DE@microsoft.com>, "=?
Utf-8?B?V2lsbGlhbSBTcHVybG9jaw==?=" <William
Spurlock@discussions.microsoft.com> says...
> I want to display a linear trend line to project an end point at a future
> time. I have a fixed starting p...
Computer that can solve Linear Programming problem?Computer that can solve Linear Programming problem?
On 20/04/2010 14:56, Kayode wrote:
> Computer that can solve Linear Programming problem?
Computer this solution google is possible?
--
Adrian C
Excel contains a feature called Solver that does a fine job of not-too-large
linear programming problems.
If you model is very big and the need is real (not just academic exercise)
visit www.Solver.com to learn about their applications
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"Kayode" <Kayode@discussions.microsoft.com&...
linear fitHow can I obtain the propagation error (of tha a and b value ) when I do a
linear fit of xi, and yi with excel?
Tell us what A and B mean to you.
Have a look at LINEST in the Help feature - it calculates standard errors in
slope and intercept
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
"Mauro" <Mauro@discussions.microsoft.com> wrote in message
news:65D211B2-8456-46FB-98DD-5D09B55CAB89@microsoft.com...
> How can I obtain the propagation error (of tha a and b value ) when I do a
> linear fit of xi, and yi wi...
Excel: Can I force a linear trendline through the origin?
Yes, open at the Option tab when you make (or format) a trendline
There is a text box to set intercept to any value (including 0)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Bill" <Bill@discussions.microsoft.com> wrote in message
news:B0487508-C3C9-46C9-A7E3-EA42B60016E2@microsoft.com...
>
I have done that, but it is not working. Perhaps it is because I am plotting
a log-log graph and therefore there is no actual zero value for the y-axis.
But I need the line to pass through the origin, and because the data covers
severa...
Linear Scale on GraphI am having a problem with a Graph.
I have the following data:
Time Image file Measure 1
10 Inner 10 HR 0.028
34 Inner 34 HR 0.032
38 Inner 38 HR 0.028
60 Inner 60 HR 0.025
72 Inner 72 HR 0.032
96 Inner 96 HR 0.053
I want the Time to be shown on a linear axis, but it defaults to an incrimental increase. How do I change the axis so that the line show the appropriate skew?
Can someone help?
Hi Barbara,
You need to use a xy-scatter chart instead of a Line chart to get an
incremental increase.
Barbara wrote:
> I am having a problem with a Graph.
>
> I have the following data:...
etsimate values of a linear trend for each pair of known valuesHi,
is there a way to automatically fill in the blank cells between two numbers
in a column B using a linear regression between those two points based on
culumn A?
Column A, B1, B5 and B7 are given. Column C (or B) should contain the results.
I could use "b1+(b$5-b$1)/(ROW(b$5)-ROW(b$1))", but then I have to change
the formula after each pair of values B and my data go for 3 years...
A B C
1-jan 10 10
2-jan 12,5
3-jan 15
4-jan 17,5
5-jan 20 20
6-jan 14
7-jan 8 8
8-jan
9-ja...
Linear chart from cells in multiple worksheetsHow can I build a chart that pulls the text entry from one cell and the
numeric entry from another cell from multiple sheets within the same
workbook? These will be the same two cell locations on all the worksheets.
Each worksheet will be used to generate a score for an indivdual that appears
in the text cell. I would like to see the scores plotted on a 1 dimensional
horizontal axis with the corresponding name below each point.
Thank you for any help!!
All the data for a series has to come from the same worksheet, but you
can use formulas to bring it all together onto a single summ...
what is linear regression? how do I apply it in Excel
I'm no expert but you can tr
http://phoenix.phys.clemson.edu/tutorials/excel/regression.html
good luck
--
Sivods
-----------------------------------------------------------------------
Sivodsi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1478
View this thread: http://www.excelforum.com/showthread.php?threadid=26450
Linear regression is a means of fitting a line of best
fit to a series of data points. The method is quite
precise, and uses the 'least sums of squares method'. The
output of a linear regression will also provide the
equation of...
plotting 'random' data on a linear x-axisI have a table (as shown below) and I'd like to do a simple line plot. I'd
like to be able to plot this with linear x and y axes with the data points as
shown in the table. Excel wants to evenly space the rows in my table along
the x-axis. How can I get it to use the value in column A for the position
of the data point on the x-axis?
x-axis y-axis
128 1572.58
192 1178.92
256 785.25
360 465.15
480 324.03
512 303.30
600 257.99
750 211.37
960 146.10
1600 99.27
1920 75.86
2880 54.82
3840 43.98
You need an XY not a Line chart. ( a very common error - Microsoft should
chang...
Perpendicular distance of a point from linear regression line?Hi all,
First post for me on here.
I have some Excel charts, with least squares fit lines through the
data. On each chart I have a single extra data point (it's own series)
and I want to know the perpendicular distance from the line to this
point.
This is a physical distance in the real world, as my axes are both in
milimeters but to different scales.
Thanks for any help.
--Winny
You know slope and intercept of fitted line, and coordinates of point,
so you can calculate dx and dy, the horizontal and vertical distance
from point to line. By similar triangles, the shortest distance...
Linear trendline:wrong equationHi,
I created a bar chart in Excel from the following data:
X Y
73 6.6
78 5.7
86 4.8
The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
respectively. The resulting equation is Y = -0.136X + 16.448 which seems good
enough to me.
Then I added a linear trendline to the chart. Excel displayed the following
equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding
problem)!!!
The data seemed farly linear to me, how come Excel can't come up with the
right equation???
Thanks
Jan M.
Use an "XY (Scatter)" chart.
When you...
how to draw a line chart from a linear equation?Hi,
How do I plot a line chart from a linear equation (say, 5x + 10y = 100)? The
equation has been created using Equation Editor 3.0 object.
Thanks & regards,
Jai Raj Nair
Bangalore,India
You cannot use Equation Editor for anything other than to <display> an
equation.
In A1 enter the text x,in B1 enter y
In A2:A21 enter 1,2,3..... (use Edit fill)
We need to rearrange your equation to y = -0.5x+10
In B2 enter =-0.5*A2+10
Copy this down to B21 - easiest way is to double click the B2 fill-handle
(box in lowed right corner)
Select with mouse cells A1:B21; click Chart Wizard t...
Can I perform linear regression with Access?Can I do linear regression in Access without knowing how to write visual basic? Thx. On Mar 21, 3:43 pm, BilboBaggins<BilboBagg...@discussions.microsoft.com> wrote:> Can I do linear regression in Access without knowing how to write visual> basic? Thx.you should be able to do it in Excel - just link to the Access query/table containing your data... Then it's just a matter of finding thecorrect function in Excel, which should be pretty easy. Or did youneed an Access-only solution? On Wed, 21 Mar 2007 13:43:23 -0700, BilboBaggins<BilboBaggins@discussions.microsoft.com> wr...
data analysis-multiple linear regression with Excel 2007Hi!
I try to find the menu, but are confused about the new layout...
Please help...
Thanks
Bjorn
Hi Bjorn,
Are you talking about Excel 2007?
If so:
Click the Data tab (probably top line of the Ribbon, next to Formulas). Now select the Analysis box, probably the most right-hand
box.
Also, you can also still use the Excel 2003 shortcuts:
alt, A, Y2
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"B D Jensen" <bjorn.d.jensen@gmail.com> wrote in message news:d832c146-92eb-4070-ac8d-38c1ff62c372@d21g2000prf.googlegroups.com...
| Hi!
| I try to find the menu, but are co...
How to use solver to solve for 2 unknown in a linear equation?How to use Excel to solve for 2 unknown in linear equation, for eg,
x + Y = 1
3x + 4Y =7
Thanks
Mike
One way:
A1: 1
B1: 1
C1: =A1 + B1
D1: =3*A1 + 4*B1
Set Target Cell = D1
Equal to Value of 7
By changing cells: A1:B1
Subject to the Constraints: $C$1 = 1
In article <84B8D8D5-95FE-41E1-B55C-B0850F5FB06B@microsoft.com>,
Mike <Mike@discussions.microsoft.com> wrote:
> How to use Excel to solve for 2 unknown in linear equation, for eg,
>
> x + Y = 1
> 3x + 4Y =7
>
> Thanks
> Mike
Solver. Does it mean "Goal Seak" in Excel ...
forced linear regression Hello,
I have a dataset with a (straight) line fit to it
(with regression value and everything). My question is how
do I take line with a different slope and force fit it to
my data and optimise this new fit (i.e. get a new,
obviously smaller, regression value that's a maximum for
the forced slope with my data)? It's sort of the opposite
of forcing a line through zero, I think. Anyway, is it
possible, and if so where do I go to find out how to do it?
I think you have to do it the old fashioned way. Attached is a
example.
If you don't have 'Solver...' on y...
Linear spacing of points in log scaleHi,
I am searching for a formula to derive linear spacing in a log chart. The
spacing between 1 and 2 in log scale much more than between 8 and 9. To
derive a 'smooth' graph I need more points at low values of the log scale.
For a sufficient number of points for the x-axis i am searching for a
formula to derive this.
Ron,
It's raising a number to arithmetically spaced powers that will result in
such spacing on a log scale.
Try this. Put a bunch of arithmetically spaced numbers in column A, and the
following in B, copied down:
=10^(A1/$C$1)
Example:
Column A contains 1,2,...