# Thread: Trend Question (Excel 2002)

1. ## 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. ## Re: Trend Question (Excel 2002)

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

Steve

3. ## 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. ## 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. ## 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. ## 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
•