Weighted/Conditional LINEST VBA (Coefficient/Y-Intercept)

Hey everyone,

I've got two VBA codes that work really well for what I'm doing, mostly:

1. Conditional LINEST function that only finds the x coefficient if the range matches the additional criteria:
   
   

   Please Login or Register  to view this content.

   Formula example:
   PHP Code: 

   =LinestCond($U$2:$U$56,$P$2:$P$56,$N$2:$N$56,"D") 

   Where Column U are the y's, Column P are the x's, and Column N is where the cells are denoted with "D"s or "R"s, so it's pulling only the "D"s.
   
   
2. Weighted LINEST function that gives the x coefficient more weight based on how high the numbers are in the range:
   
   

   Please Login or Register  to view this content.

   Formula example:
   PHP Code: 

   =LinestWeighted($P$2:$P$56,$U$2:$U$56,$K$2:$K$56,TRUE,TRUE) 

   Where Column P are the x's, Column U are the y's, and Column K is where the cells are weighted from 1-38 so the function knows how much weight to give to each point.
   

Again, these both work great -- but I was wondering if somebody more skilled in VBA than I am could help me do two things:

1. Come up with a VBA code that combines the two (a weighted *and* conditional) LINEST function; and
2. If somebody can help me use these functions to get the y-intercept and not just the x coefficient.

I would be forever grateful! Thanks a lot.

-Ryan

If somebody can help me use these functions to get the y-intercept and not just the x coefficient.

This statement

Please Login or Register  to view this content.

Should return both values into LinestCond -- LinestCond should be a variant containing an array with 2 elements. (Set a breakpoint on the end function statement. While in breakmode, set a watch for LinestCond and you should see both values in the watch window). If you select two cells when entering your function call and enter it as an array function (ctrl-shift-enter), both values should be returned to the spreadsheet. Same should apply to LinestWeighted()

When I've done a conditional LINEST() like this, I have treated just like your weighted LINEST() only allowed the weight to be 0. When weight is 0, you don't include that point in NewSeries at all. That's how I'd combine the two. Code LinestWeighted() to handle a weighting factor of 0. Then, the conditional question simply needs to put 0 in for the weighting factor for that point.

Do I have to add more code to the VBA LinestCond/LinestWeighted in order to get the b?

Have you looked at Help for LINEST?

Do I have to add more code to the VBA LinestCond/LinestWeighted in order to get the b?

The point I was trying to make is that you shouldn't need more code to get b -- it should already be there. Or perhaps I am not understanding what you are doing with LinesCond and LinestWeighted.

I think the fact that I have far more than two cells I need in order to get the correct y-intercept is what's confusing me.

When regression a straight line fit y=mx+b, it should not matter how many data points are going into the regression. You should still only get two values (m and b) out of the regression. Unless there is something I'm misunderstanding, anyway.

I was hoping that I could embed it within another formula without using the array

Not sure I understand at all what you are trying to do. You want to return both m and b, but you don't want to return them or store them together?? You could always nest the LinestCond/LinestWeighted inside of the INDEX() function (=INDEX(linestcond(arglist),2) to return just the slope if that's what you really want to do.

Thanks! I'm all good with the y-intercept now -- I just grabbed it from the array. I think I'm still just a bit confused on how to combine the two parts of code (if necessary) to get a LINESTWeightedCond() type-function. The ideal formula would probably look something like:

PHP Code: 

=LinestCondWeight($U$2:$U$56,$P$2:$P$56,$N$2:$N$56,"D",$K$2:$K$56) 


Where LinestCondWeight(x range, y range, conditional range, conditional value, weighted values) -- or whatever version of that is easiest.

But I'm obviously open to whatever else would take both conditional and weighted LINEST function into account.

Thanks again!

As I noted above, I think I would combine the two functions using the weighting factors. What would happen in LinestWeighted() if wt(i) is 0? According the help file for the REPT() function, it will return an empty string. I don't know what the Split function will do when it tries to separate out the individual entries. One way or another, I would adapt this section of the code to handle a weighting factor of 0. Perhaps a simple If ... Else ... end if construct where it tests for wt=0 and, if wt=0, then skip over adding this point to new series.

Then, prior to this point in the code, you would simply need a routine that will check the condition and, where the condition is false, set a weighting factor of 0 for that point. Personally, I would perform this routine in the spreadsheet using an additional =IF() function beyond how you are currently populating the weight array [=IF(cond,wt(i),0)]. It shouldn't be hard to include code in the function to perform this same action. I suppose you could even include it as part of the If test if you use my suggestion above for nesting the weighting part inside of an If block.

Please Login or Register  to view this content.