If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
weighted trendline
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. |
#3
|
|||
|
|||
weighted trendline
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. |
#4
|
|||
|
|||
weighted trendline
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 wrote in message ... 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. |
#5
|
|||
|
|||
weighted trendline
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. |
Thread Tools | |
Display Modes | |
|
|