Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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. #5
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Re: Trend Function (Excel 2003)

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

  7. #7
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Thats a made up formula.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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