# Thread: Trend Function (Excel 2003)

1. ## Trend Function (Excel 2003)

Is it possible to search a range for data you want to trend, similar to using SUMPRODUCT with asteriks?

I have column A with sequential dates, column B with days of the week and column C with totals. I just want to trend column C for the next date I enter.

I attached an example.

Thanks!

2. ## Re: Trend Function (Excel 2003)

I'm not sure I understand your question - what have SumProduct and Asterix (or Obelix) to do with this?

If you enter a date in (say) A12, the formula =FORECAST(B12,C2:C8,A2:A8) will calculate the corresponding value.

3. ## Re: Trend Function (Excel 2003)

Sorry if Im unclear. Lets say I add in 1/12/2006- a Thursday- to cell A9 and add in an item count of 25 to cell C9. I want to search through my column C and Forecast only for Thursday. I have done SUMPRODUCT in the past to totals based on specific criteria and want to know how to do it with Trend. I can only seem to either trend the whole range or else I have to break the days out to different sheets. I want to trend specific days on the fly.

4. ## Re: Trend Function (Excel 2003)

Sorry, I still don't understand. You want to enter a value for 1/12 AND forecast a value for 1/12?

5. ## Re: Trend Function (Excel 2003)

Yes. I want to know what the forecast is for the next available day, ie, 1/12.

TREND column C for values WHERE column B = Thursday.

Here is the TREND formula I was using:
=TREND(C2:C8,\$A2:\$A8,\$A12) and in A12 I was putting in the next date I wanted to Trend. Realy, Im trending for the next data, but I only want Mondays, then Tuesdays, Then Wednesdays. I can chnage cell A12 to trigger it. What I cant get is to have it just grab certain days to TREND.

6. ## Re: Trend Function (Excel 2003)

Perhaps someone else will understand what you want. I'm sorry.

7. ## Re: Trend Function (Excel 2003)

Wow. Its tought o communicate this scenario. Let me try one more. In the attached excel, can I replace C12 (=TREND(C2,C7,A2,A12)) with a formula where I didnt have to use the control key to select the C2 and A2 values? If that doesnt help, thanks anyway.

8. ## Re: Trend Function (Excel 2003)

I'm completely stumped. You have a different formula in your sample workbook than in your post, and neither makes any sense to me.

9. ## Re: Trend Function (Excel 2003)

Is this what you are after?

I added a chart that shows the trendline with all the points and that calculated using just the points from the same day. The formulas are complex and use the breakout of the Matrix Math:

Slope is: [(Number of Points) * Sum (X*Y) - Sum(X)*Sum(Y)] / (Number of Points * Sum (X^2) - [Sum(X)]^2)

Intercept is: [Sum(Y) - Slope*Sum(X)] / (Number of Points)

It uses the SUMPRODUCT to calculate the SUMs for the days when the weekdays match...

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
•