Conditional Functions (Excel 2003 SP2)

Topic

New Reply

I’ve used the conditional functions for dates that Hans wrote about in post 503094 . Here’s the formula: =AVERAGE(IF((A2:A16>=F1)*(A2:A16=$F1)*($A$2:$A$16=$F1)*($A$2:$A$16<=$F2),$C$2:$C$16)). The second data series is in C.

I'd also like to do some conditional LINEST estimates, but that doesn't seem to work. I'm guessing there's a problem with the double array functions, but would appreciated any help or suggestions.

Reply | Quote

Replies

You can omit the IF for one of the two arguments – Excel will automatically exclude the values corresponding to the excluded values in the other argument. So for example (as an array formuila):

=SLOPE(IF(($A$2:$A$16>=$F1)*($A$2:$A$16=$F1)*($A$2:$A$16<=$F2),$C$2:$C$16))

Reply | Quote

As you have found, LINEST doesn’t work this way. As a replacement, you can use the INTERCEPT, CORREL and RSQ functions in addition to SLOPE. They do work with IF the same way as SLOPE does.

Reply | Quote

What I really want that I can’t find a single function for is the standard error for the intercept. LINEST calculates it, but not STEYX. I’ll have to go back to the definition. The problem seems to be that there isn’t a direct function for the residual sum of squares. I need to compare a lot of different regressions based on some conditions. I was hoping to find an shortcut to calculating some of the statistics.

Thanks for your help. If you know of a slick way to compute conditional SSE or MSE, that would be a big help!

Reply | Quote

I think you’ll have to go back to the formulas behind linear regression.

Reply | Quote