Nongraphical Trend

  • Thread starter Thread starter Monte75
  • Start date Start date
M

Monte75

I am looking to denote a "trending up" or "trending down" on
spreadsheet that contains operating profit by month. I know how to d
this graphically, using the add a trendline function in a graph.
However, I would like to indicate this without using a graph. How do
do that?

The data is as follows (simple):
J F M A M J J A S O N D
OP% 8 7 8 6 4 9 3 5 11 8 7


Thanks
 
Monte,
You should be able to use the TREND function. You will need to use numbers to represent the months within the function. Also if December is 12 then the following January must be refered to by 13.

Good Luck,
Mark Graesser
(e-mail address removed)

----- Monte75 wrote: -----

I am looking to denote a "trending up" or "trending down" on a
spreadsheet that contains operating profit by month. I know how to do
this graphically, using the add a trendline function in a graph.
However, I would like to indicate this without using a graph. How do I
do that?

The data is as follows (simple):
J F M A M J J A S O N D
OP% 8 7 8 6 4 9 3 5 11 8 7


Thanks!
 
Mark,
Thanks for answering my question. I'm still a little lost, however.
I've enter the trend formula and referenced my Operating Percentages as
the known y's and my month numbers as the known x's. I receive a
number that doesn't give me an indication of up or down trend (I would
assume this would be shown in + for trend up and - for trend down or
that I could compare the trend # i receive with some other number to
indicate the trend up or down.) What am I missing here?
 
I am looking to denote a "trending up" or "trending down" on a
spreadsheet that contains operating profit by month. I know how to do
this graphically, using the add a trendline function in a graph.
However, I would like to indicate this without using a graph. How do I
do that?

The data is as follows (simple):
J F M A M J J A S O N D
OP% 8 7 8 6 4 9 3 5 11 8 7
Click to expand...

If your percentages were in B2:L2 (as there are only 11 shown), you could use

=IF(2+SIGN(INDEX(LINEST(B2:L2),1)),"trending down","stable","trending up")
 
Monte,
I think I misunderstood what you were looking for. If you input a number for the next month, then the formula I provided would return the expected value for that next month.

If you only want the trend direction and amplitude you could use:

=SLOPE(A2:K2,A3:K3)

This will return a positive or negative number depending on the trend. A2:K2 is your operating percentages and A3:K3 is the month numbers. Your month numbers should always start with 1 regardless of the actual begining month in your trend analysis.

If you just want an indicator as to the direction of the trend you can use:

=IF(SLOPE(A2:K2,A3:K3)>0,"Up",IF(SLOPE(A2:K2,A3:K3)>0,"Down","Flat"))

Good Luck,
Mark Graesser
(e-mail address removed)


----- Monte75 wrote: -----

Mark,
Thanks for answering my question. I'm still a little lost, however.
I've enter the trend formula and referenced my Operating Percentages as
the known y's and my month numbers as the known x's. I receive a
number that doesn't give me an indication of up or down trend (I would
assume this would be shown in + for trend up and - for trend down or
that I could compare the trend # i receive with some other number to
indicate the trend up or down.) What am I missing here?
 
I believe Harlan meant to use CHOOSE instead of IF, as in
CHOOSE(2+SIGN(INDEX(LINEST(B2:L2),1)), "trending down", "stable",
"trending up")

[Watch out for line break]

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top