Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trend Question (Excel 2002)

    Hello Lounge,
    Can a If statement be used when using the Trend() function? I want to be able trend based on a day of the week.

    Thanks,
    Patrick

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Trend Question (Excel 2002)

    Could you elaborate on what you want to do? It is not clear to me.

    Steve

  3. #3
    New Lounger
    Join Date
    Oct 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trend Question (Excel 2002)

    Thanks for the reply. For example in the attached file cell B186 is trending the values in column B and column D. I would like to trend based on the day of the week in column A.

    Thanks,
    Patrick

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Trend Question (Excel 2002)

    You can get the predicted "High trend" value for "today" using the equation:
    =TREND(D2186,A2:A186,TODAY())

    If the date is in cell A1 (for example):
    =TREND(D2186,A2:A186,A1)

    Or you can hardcode a date (the example is for march 2, 2004):
    =TREND(D2186,A2:A186,date(2004,3,2))

    I am not sure how "Col B" enters in to it.

    If you want to literally trend on the day of the week instead of the date:

    =TREND(D2186,WEEKDAY(A2:A186),1)

    Will give you the predicted value for Sunday (=1). Change the "day of the week" desired. (though personally, I don't see a correlation with day of the week at all, though date is inportant).

    If I misunderstand what you are after, could you elaborate?

    Steve

  5. #5
    New Lounger
    Join Date
    Oct 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trend Question (Excel 2002)

    Steve,
    The high, low, and average columns represent temperature data for each day. "Col B" is energy usage. When forecasting energy usage the day of the week and temperature directly correlate with energy usage. When I filter out the days I do not want, trend() still looks at the filtered days. So, I was hoping there was a way to say if it is "Monday" then trend all Mondays. I hope this makes since.

    Patrick

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Trend Question (Excel 2002)

    You can create an intermediate column of "dates". In G2 enter:
    <pre>=IF(WEEKDAY(A2)=2,A2,"")</pre>

    Copy/Autofill G2 down the column
    This will put either a null or the date for all the mondays.

    TREND will not work with mixed text and numbers so you will have to use SLOPE and INTERCEPT functions
    Slope is geiven by:
    <pre>=SLOPE($D$2:$D$186,$G$2:$G$186)</pre>


    Intercept by:
    <pre>=INTERCEPT($D$2:$D$186,$G$2:$G$186)</pre>


    The "predicted Y" (which you would get from TREND)
    = slope * "given X" + intercept

    The predicted "X" is:
    =("Given Y" - Intercept)/slope

    You can also "bypass" the intermediate column by using an ARRAY formula (confirm with ctrl-shift-enter):
    <pre>=SLOPE($D$2:$D$186,IF(WEEKDAY($A$2:$A$186)=2, $A$2:$A$186,""))
    =INTERCEPT($D$2:$D$186,IF(WEEKDAY($A$2:$A$186)=2,$ A$2:$A$186,""))</pre>


    Again, TREND can not be used in this case either. Change the "=2" to =1 for Sunday, 3 for Tuesday, etc. If desired the day of the week could be put into a cell and have the formula reference the cell (eg A1)
    <pre>=SLOPE($D$2:$D$186,IF(WEEKDAY($A$2:$A$186)=$A $1,$A$2:$A$186,""))
    =INTERCEPT($D$2:$D$186,IF(WEEKDAY($A$2:$A$186)=$A$ 1,$A$2:$A$186,""))</pre>

    so if you changed A1, all would be calculated for this date.

    Steve

Posting Permissions

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