Thread: Conditional Functions (Excel 2003 SP2)

1. Conditional Functions (Excel 2003 SP2)

I've used the conditional functions for dates that Hans wrote about in <post#=503094>post 503094</post#> . Here's the formula: =AVERAGE(IF((A2:A16>=F1)*(A2:A16<=F2),B2:B16)) from that post with dates in column A and conditions in F1 and F2, data's in column B.

I have two questions.

I've used this technique with a function that has two arguments - Slope. I have two if statements, one for each argument in slope. Is that the most efficient way? Here's what I have now:
=SLOPE((IF((\$A\$2:\$A\$16>=\$F1)*(\$A\$2:\$A\$16<=\$F2),\$B\$ 2:\$B\$16)),IF((\$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.

2. Re: Conditional Functions (Excel 2003 SP2)

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<=\$F2),\$B\$2 :\$B\$16),\$C\$2:\$C\$16)

or

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

3. Re: Conditional Functions (Excel 2003 SP2)

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.

4. Re: Conditional Functions (Excel 2003 SP2)

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!

5. Re: Conditional Functions (Excel 2003 SP2)

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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•