Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •