Results 1 to 6 of 6
Thread: Trend Question (Excel 2002)

20050128, 21:10 #1
 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

20050128, 21:28 #2
 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

20050131, 12:59 #3
 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

20050131, 23:32 #4
 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

20050201, 13:09 #5
 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

20050201, 13:34 #6
 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 ctrlshiftenter):
<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