weighted trendline

Can anyone tell me if it possible to weight a linear trendline in excel?  
Like a calibration line where you can weight by 1/x where x is the variance 
at each point on the line.
0
HELP (162)
6/20/2009 9:13:01 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
1242 Views

Similar Articles

[PageSpeed] 46

Hi,

I don't believe that can be done with anything built into the trendline 
chart feature, but you can use the trendline formula in the spreadsheet and 
then apply your weighting to it.  Then plot that rather than the built-in 
trendline.  You can get the formula of the trendline from the chart by 
choosing to display it or in the spreadsheet by using the LINEST function or 
the related SLOPE and INTERCEPT functions.

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Always need excel help" wrote:

> Can anyone tell me if it possible to weight a linear trendline in excel?  
> Like a calibration line where you can weight by 1/x where x is the variance 
> at each point on the line.
0
6/20/2009 9:47:01 PM
Hi,

Many thanks for your quick reply but want to make sure I am going to do this 
correcly.
I know how to get the trendline formula. ok there.
The weighting factor will be different for each point on the line and I 
guess I can calculate them x(trendline)=y-c/m from the trendline and my 
weight for each point will be w = 1/ (x(trendline) - x(measured)) ie. inverse 
of the variance at each point.   But how do I apply it back to the trendline 
to plot this new weighted line?  Just multiply my measured x values by w in a 
new column and plot these?

I am basically trying to remove heteroscedasticity (increasing variance) 
across my regression line.
Thanks for any further help you can give.

"Shane Devenshire" wrote:

> Hi,
> 
> I don't believe that can be done with anything built into the trendline 
> chart feature, but you can use the trendline formula in the spreadsheet and 
> then apply your weighting to it.  Then plot that rather than the built-in 
> trendline.  You can get the formula of the trendline from the chart by 
> choosing to display it or in the spreadsheet by using the LINEST function or 
> the related SLOPE and INTERCEPT functions.
> 
> -- 
> If this helps, please click the Yes button.
> 
> Cheers,
> Shane Devenshire
> 
> 
> "Always need excel help" wrote:
> 
> > Can anyone tell me if it possible to weight a linear trendline in excel?  
> > Like a calibration line where you can weight by 1/x where x is the variance 
> > at each point on the line.
0
6/20/2009 11:56:01 PM
Can you scan a page or two of a textbook that explains this weighting 
(hopefully with example) and send files to my private email (remove 
TRUENORTH. or visit website to get my real email address)
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Always need excel help" <Always need excel help@discussions.microsoft.com> 
wrote in message news:6E744DE1-3089-4FB9-965C-09FC11C58EB2@microsoft.com...
> Can anyone tell me if it possible to weight a linear trendline in excel?
> Like a calibration line where you can weight by 1/x where x is the 
> variance
> at each point on the line. 


0
bliengme (657)
6/21/2009 9:55:33 AM
Somebody else's plume from my notepad that follows Bernard's advice, still it 
should serve:

Function WLR(YRange As Range, XRange As Range, WeightRange As Range)
'calculates the weighted linear regression - returns an array {a,b} 
{slope,intercept}
'by Adam Slim

Dim SigmaW As Double, SigmaWX As Double, SigmaWX2 As Double
Dim SigmaWY As Double, SigmaWXY As Double
Dim i As Long, outWLR(1 To 2) As Double

'validate ranges
If XRange.Count <> YRange.Count Or XRange.Count <> WeightRange.Count Then
  'fails - the ranges must be the same size
  WLR = CVErr(xlErrRef)
  Exit Function
End If

'calculate the sigmas
For i = 1 To XRange.Count
  SigmaW = SigmaW + WeightRange.Cells(i).Value
  SigmaWX = SigmaWX + WeightRange.Cells(i).Value * XRange.Cells(i).Value
  SigmaWX2 = SigmaWX2 + WeightRange.Cells(i).Value * XRange.Cells(i).Value ^ 2
  SigmaWY = SigmaWY + WeightRange.Cells(i).Value * YRange.Cells(i).Value
  SigmaWXY = SigmaWXY + WeightRange.Cells(i).Value * XRange.Cells(i).Value * 
YRange.Cells(i).Value
Next i

'calculate the outputs
outWLR(1) = (SigmaWX2 * SigmaWY - SigmaWX * SigmaWXY) / (SigmaW * SigmaWX2 - 
SigmaWX ^ 2)
outWLR(2) = (SigmaW * SigmaWXY - SigmaWX * SigmaWY) / (SigmaW * SigmaWX2 - 
SigmaWX ^ 2)
WLR = outWLR
End Function
-- 
Petr Bezucha


"Always need excel help" wrote:

> Hi,
> 
> Many thanks for your quick reply but want to make sure I am going to do this 
> correcly.
> I know how to get the trendline formula. ok there.
> The weighting factor will be different for each point on the line and I 
> guess I can calculate them x(trendline)=y-c/m from the trendline and my 
> weight for each point will be w = 1/ (x(trendline) - x(measured)) ie. inverse 
> of the variance at each point.   But how do I apply it back to the trendline 
> to plot this new weighted line?  Just multiply my measured x values by w in a 
> new column and plot these?
> 
> I am basically trying to remove heteroscedasticity (increasing variance) 
> across my regression line.
> Thanks for any further help you can give.
> 
> "Shane Devenshire" wrote:
> 
> > Hi,
> > 
> > I don't believe that can be done with anything built into the trendline 
> > chart feature, but you can use the trendline formula in the spreadsheet and 
> > then apply your weighting to it.  Then plot that rather than the built-in 
> > trendline.  You can get the formula of the trendline from the chart by 
> > choosing to display it or in the spreadsheet by using the LINEST function or 
> > the related SLOPE and INTERCEPT functions.
> > 
> > -- 
> > If this helps, please click the Yes button.
> > 
> > Cheers,
> > Shane Devenshire
> > 
> > 
> > "Always need excel help" wrote:
> > 
> > > Can anyone tell me if it possible to weight a linear trendline in excel?  
> > > Like a calibration line where you can weight by 1/x where x is the variance 
> > > at each point on the line.
0
PBezucha (46)
6/22/2009 6:19:01 AM
Reply:

Similar Artilces:

Weight Weight Weight
So if I want to give a weighted score/percentage.....how would I? For example, PEOPLE + REQUIRED OBSERVATIONS + WEIGHTED SCORE BASED ON TYPE OF SCORE = OVERALL SCORE/RANKING So.... If I have 3 types of observations.... If Type A = Hardest Type Type B = Average Type Type C = Easiest Type How do I weigh the QUALITY of their production? So...... 10 people x 6 observations each + Type B observations = ......... Hi! Only you can know how you want to weight them so you will have to b able to define in a suitably mathematical way how scores will b amended to recognise this weighting and by ...

Trendlines #3
I am using Access-to-Excel automation to bring in 2 Columns of data...Week Ending Dates, & a Value. These are the Source Data for a Vertical-Bar Chart. I have a 3rd column that uses this formula... =IF(LEN(A8)=0,"",VLOOKUP(A8,TheLook,2)) to get Values for a Trendline...in "Column C". I am NOT strong in Excel Charts at all, as I do mostly Access Programming. How can I take this "3rd-column" and assign that to a Trendline? TIA - Bob Bob - Excel can calculate a trendline from a charted series, but I sense this isn't the trendline you mean. To add t...

How to change axes settings after adding trendline?
Before adding the trendline to my linear scatter plot, I set my Y axis to range from the desired -7 to -5 scale. After adding the trendline, the axis is changed to range from -7 to 0 which produces almost a flat line. How do I display the trendline on the previous -7 to -5 scale? Double-click on the axis, the Format Axis dialog box should appear. Then under the "Scale" tab of the Format Axis dialog box, you should be able to set the maximum and minimum (to -5 and -7). If that doesn't work, can you tell me what the equation for the trendline is? (Double-click on the trendli...

How to apply a weighting
I'm trying to apply a weighting to my teams to allow for experience and size I have 3 teams which are different sizes and are different skill levels. Level 5 is the lowest skill level and level 1 being the highest skill level. Team size/level5/level4/level3/level2/level1 6/3/2/0/1/0 10/2/3/4/0/1 7/0/2/3/1/1 How do I apply a weighting to these numbers to allow for team size and experience. Can I do this using sumproduct? I tried looking at this but cant get my fragile little mind around it Thanks in advance. -- Homer J ---------------------------------------------------------------...

weighting a value?
Let's say I have 10 hours to accomplish 10 tasks. Having previousl accomplished each of these tasks I know some will take longer tha others. Let's say I want to weight them into three categories, easiest receives a weight of 1, harder 1.5 and harder yet 2.5. Wha Excel trick, or someones know how, would be used to spread my 10 hour appropriately over the 10 tasks -- jvan10 ----------------------------------------------------------------------- jvan100's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2922 View this thread: http://www.excelforum.com/showt...

Weighted items
Does anyone know of a way to weigh an item on a stand alone scale, print out the price and weight on the barcode, and then scan it at the register without having to weigh the item again. Can RMS read the barcode and understand the item, price, weight and in turn calculate a total? Thanks! Use Random Weight UPC. Search this newsgroup for more info. Regards Michael "zoostation" <zoostation@discussions.microsoft.com> wrote in message news:0D7EF298-BCA1-474F-B3FA-D96E53E26F91@microsoft.com... > Does anyone know of a way to weigh an item on a stand alone scale, print ou...

Trendlines in Pivot Charts
How do I add a trendline to a chart based on a pivot table? Is it possible Thanks Leroy - Did you try this? Right click on a series, and select Add Trendline from the pop up menu. This works in Excel 2000 and XP (2002), and presumably for 2003 as well; Excel 97 didn't support pivot charts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Leroy wrote: > How do I add a trendline to a chart based on a pivot table? Is it possible? > > Thanks Jon - I am having a similar problem - I hav...

Add trendline to PivotChart
Hello, can anyone tell me how to add a trendline to a pivot chart? No matter what I try I cannot add a trendline. I've tried clicking on a data series, right click then select add trend line but this option is greyed out I have also tried using the chart menu. This is realy getting on my nerves well only because I don't know how to do it but I can do it when the chart is not attached to a pivot table...Why? Adrian - Pivot charts do not have all the flexibility of a regular chart. While it's inconvenient, it's also necessary to ensure the integrity of the links to the...

How do I show slope of a trendline on a plot ????
I have a simple plot with two points only, and added a trendline which fits with the plotted line. I really need to show the slope of the trend line on the plot. But I dont want to show the equation or anything else. I am using a plot which should calculated and plot by itself with the change of inputs in the table(somewhere in the page), and need to show that slope value on the plot too. I can calculate slope in a cell somewhere on the sheet. Is there any way to show the value (calculated somwhere on the sheet) on the plot beside my plotted line. Either way would work. But I prefer...

Random Weight
Hi, I have this problem regarding the random weight in RMS. When scanning an item, my client wants to capture the weight information of the barcode. I got a solution from the knowledge base which will use EAN/UPC code and price embedded however, the total digits for the price is up to 999.99 only. Does anyone have an alternative? -- Derrick Castro Project Manager ...

Weights
I am trying to find a formula for vlookup and sumif. I am trying to build a spreadsheet for our drafting dept. The weights are being calculated by hand. I think it would be easier if we had a spreadsheet. For example, I have a channel that is C 8 x 11.5 the length is 7ft 6inch. The weight of the channel is .852 pounds per foot. I am able to calculate that in excel and come up with 86 pounds rounded. The material will change every time and the weight of the material will change every time. The length of the material will also change. I can input the length every time. What I want is to cre...

Weights
I am trying to find a formula for vlookup and sumif. I am trying to build a spreadsheet for our drafting dept. The weights are being calculated by hand. I think it would be easier if we had a spreadsheet. For example, I have a channel that is C 8 x 11.5 the length is 7ft 6inch. The weight of the channel is .852 pounds per foot. I am able to calculate that in excel and come up with 86 pounds rounded. The material will change every time and the weight of the material will change every time. The length of the material will also change. I can input the length every time. What I want is to cre...

add trendlines unavaiable
Having a problem in excel where the Chart, Add Trendlines is unavailable. This happens whether using a new or existing document. Have done a detect and repair back to defaults and have searched the knowledge base but can't find anything on why this would be unavailable. Isn't this part of the basic excel install or is there something missing in excel? Have looked at several other workstations and am able to add a trendline but its not working on this one workstation. In article <87DF3990-10E5-4028-A4ED-160A8B985FC5@microsoft.com>, Ellen@discussions.microsoft.com says.....

Trendlines print incorrectly with 600dpi
Just like in the Microsoft problem report kbbug KB277593, we have trendlines inserted that graph beyond the data in order to get them to look right, and by default we print to 600dpi, so our screen display is right, but the printed output and print preview is right. Now, I'm not saying it wouldn't be possible to educate 50 people in the office to make one of the 4 workarounds Microsoft recommends, but I am thinking that none of them is a satisfactory permanent fix that is 100% safe because they all rely on users to remember to do the workaround. Anybody got a suggestion for something I...

77014 Mining the Web :Searches with Kriging, Inverse Distance Weighting, eigenVectors and Cross-Pollination 77014
Site and Features: http://www.eigensearch.com Search engine, eigenMethod, eigenvector, mathematical, manifolds, science, technical, search tools, eigenmath, Jacobian, quantum, mechanics, manifolds, science, physics, chemistry, law, legal, government, home, office, business, domain lookup, medical, travel, food, university students, searching, searchers, surfing, advanced search, search tools Chemistry, mathematics, physical sciences, engineering, aerospace, astronomy, photography, news, computers, software, investment, venture capital, stakeholder, Biology, Chemistry, Geosciences, Biotechno...

Weighted Average
On the first tab of my spreadsheet, I'd like to calculate the weighted average of data on my 2nd tab. The 2nd tab is setup using "Row()" formulas, and has auto-populated based on predetermined criteria. Currently, the 2nd tab has the following info in columns A, B & C: Bank Principal Yield Bk A $500 1.25% Bk B $300 1.00% Bk A $250 1.25% I've tried using the following formula, but keep getting a #VALUE response, and and not sure why? =Sumproduct(--(A2:A5="Bk A"...

Weight Scales
Has anyone used an external scale for weighted items? If so what is the best hardware? Thanks Fairbanks ULTEGRA Bench Scale uo to 150lbs. Serial version. Works with ShipRush, UPS ShipManager and Fedex Software. USB version not supported by all software so beware. You can get a UPS discount if you buy it direct. $249 http://www.fairbanks.com/ups/ Mickie no relation to Fairbanks....user only. "JereHurn@zeroedin" wrote: > Has anyone used an external scale for weighted items? If so what is the best > hardware? > > Thanks ...

trendlines for two phases
Hello. I am working on a line chart that shows 12 weeks of data for homework completion. The X axis is weeks and the Y axis is rate of completion. Weeks 1-6 were baseline and data was collected for completion. Weeks 7-12 were experimental where an intervention was used to influence homework completion. I would like to be able to insert a trend line for weeks 1-6 and a separate trend line for weeks 7-12 on the same graph to compare student performance. Right now, when I right-click on a data point in my grapn to create a trendline, I get a trendline for all 12 weeks of data. Can a...

Weight loss line chart to monitor weight loss progress
Does anyone know how I can set up a database to log my weight loss and then create a line chart based on that information so I can see how well i'm doing?? 12 and a half pound so far in 6 weeks....i'm so pleased and would like to see it in the form of a graph... Please help You could look at it in terms of % loss or BMI. or actual loss. It's up to you. What do need to know? "S Fox" <S Fox@discussions.microsoft.com> wrote in message news:98DCE8CD-2FF0-4C15-8376-5D34E278524A@microsoft.com... > Does anyone know how I can set up a database to log my weigh...

Weight per line item
Hi! My client needs to trackthe weight of each line item (which varies depending on UOM) on the Sales Trx Entry window. Does anyone know how this can be accomplished? Any third party apps? Freight Matrix shows a running total of the order weight but I need to see the weight for each line item also. Thanks -- Marisol Mortera Marisol, It should be fairly easy to display the weight per line using VBA, both in the form and the report, assuming that we can take the unit weight * uom conversion factor. When you say weight varies by UOM, is that different from base unit weight * uom conve...

Some trendlines cannot be calculated from data containing negative or zero values
Can this alert be turned off? In Excel 2000, XP, and 2003, This message occurs when you modify data that is charted with a Logarithmic, Power, or Exponential trendline, in such a way that refitting the trendline would essentially require taking logarithms of negative numbers. Since Excel's functions (other than a few in the Analysis ToolPak, which is a 3rd party Add-In that only ships with Excel) are all restricted to real values, what do you think Excel should do instead of warning you that it cannot do what you have asked it to do? Jerry Reo Grande wrote: > Can this aler...

Weighted Scale
How do I generate an Excel bar chart that shows that the closer a value gets to 100 the more value or weight should be applied, essentially skewing the bars as the values increase? I don't understand what you want. an example please "Whirly" wrote: > How do I generate an Excel bar chart that shows that the closer a value gets > to 100 the more value or weight should be applied, essentially skewing the > bars as the values increase? In <D3095367-F746-44A5-8CB5-83CF47506DCD@microsoft.com>, Whirly told us an interesting story. My reply to this story is at the ...

excel 2007 line weight in legend does not match the chart
In excel 2007 the line weight in the legend does not appear to reflect the line weight in the chart. That is when I adjust the weight of a line in a line chart this is not shown as a change in the weight of the line in the legend. This is a problem because I tend to use charts with 'thick lines' and 'thin lines' and the legend does not differentiate on the basis of the line weight - only on the use of markers and whether or not the line is dashed. This is exacerbated by the fact that my charts are in black and white and have multiple plotting points that limit the utili...

Weighted Ratios
I need urgently to calculate a weighted ratio for following type of data: If column "B" is a single entry then column "D" = 100% If column "B" has multiple common entries column "D" = "C" / (all common B) A B C 7MW7 36399F927 78,977,646.86 7MW6 919CDR902 150,371,345.83 7MW7 36199Y9Z5 75,458,990.97 7MY1 36199Y9Z5 6,425.72 7MX9 36199Y9Z5 2,313.22 7MW6 36199Y9Z5 2,026,681,443.93 7MRB 36199Y9Z5 79,876,546.14 7MW7 36199Y9Z5 852,776,501.17 "Jeff" wrote: > I need urgently to...

Weighted Costs
We are finding our costs are varying widely around our chain of 15 stores. I manually logged into each stores' operations manager last night and changed the Weighted Cost option to None. (What we really want is to use the HQ Cost, or the Item.Cost field from HQ.) One store received a PO from a vendor today and that store's cost now reflects the cost on the PO. (The item was from a secondary supplier, so the cost is higher than our normal cost for that item.) How do I stop that? What does the option "None" mean on the Inventory tab under Configuration? If I want...