Sales Trend Formulae

[singerbatfink]

Hi Folk's

I have sales values in 3 cells A1:A3 they are £20, £40, £60 and represent April May and June.
Id like a formulae to look at the 3 cells and Identify that there has been an increase each month. If there has been an continued increase ie. May higher than April (A2 higher than A1) and June higher than May(A3 higher than A2), Id like the display cell B1 to say Up.

Now if the figures for the same period are £60, £40, and £20 Id like the cell B1 to say Down.

If the sales figures are neither up nor down or a combination, Id like B1 to say No Trend.

Ive tried various nested IF's but to no avail..

Can anyone help.. Many Thanks

B

[shg]

You could use =CHOOSE(SIGN(LOGEST(A1:A3) - 1) + 2, "Down", "No trend", "Up")

[singerbatfink]

You could use =CHOOSE(SIGN(LOGEST(A1:A3) - 1) + 2, "Down", "No trend", "Up")

Hi, Thanks for this.. It kinda does the job but still returns a value of "down" when the cells are A1=30 A2=40 A3=10, when it should say "No Trend"

I would like the formulae to return "Up" if there has been a progressive increase in sales, or "Down" if there has been a progressive decrease in sales for 3 successive months. If sales fluctuate between up and down then "No Trend"


Thanks for any help you can give.

B

[shg]

Plot the points 30, 40, 10 on a line graph, and then add a exponential trendline. Month-over-month sales are 58%, a frightening downward trend.

[singerbatfink]

Plot the points 30, 40, 10 on a line graph, and then add a exponential trendline. Month-over-month sales are 58%, a frightening downward trend.

Thanks, but I cant use a graph in this scenario..

Can anyone else help with the above query??

Cheers

B

[singerbatfink]

Hi

I need a formula that looks at the last 3 months figures.. If the figures are all up on the previous month Id like it return a value of “UP”.

If the figures for the last 3 months are all down on the previous month Id like it to return a value of “Down”.

If the figures fluctuate then Id like a value of “No Trend”

Lets say the figures are for April May & June and are 10, 15, 20 this would be UP, as the figures increase each month..

If the figures were 20, 15, 10 then the trend would be down as June is less than May and May is less than April..

The formula has to identify an increase or decrease in the same direction over a 3 month period.

If the figures are not either continuously Up or Down then “No Trend” would be returned..

This is what I have so far (thanks to SHG)

This works when the figures are up or down, but not when the figures show no trend I.e 10, 20, 15.

Can anyone help please..

B

[squiggler47]

Starting a new thread with the same question wont make you popular!

SHG's answer is the correct one, for what you are asking

your way try :-

=CHOOSE((SIGN(A1-A2)+SIGN(A2-A3)+2)/2+1,"Upward","No Trend","Downward")

[squiggler47]

hmmm early morning! Caffeine not hit home yet!

=CHOOSE((SIGN(B7-C7)+SIGN(C7-D7)+4)/2,"Upward","No Trend","Downward")

removes the extra+1


10 15 20 UP
20 15 10 Down
20 25 10 No Trend
10 25 20 No Trend
10 10 10 No Trend
25 25 10 Down
10 25 25 UP


I thought about the last 2 cases as an afterthough, if you want them then the formula is :-

=CHOOSE((SIGN(B9-C9)+SIGN(C9-D9))+3,"UP","UP","No Trend","Down","Down")

[teylyn]

singerbatfink,

I've reversed the infraction because you closed your first thread about the same topic. However, you should have continued the discussion there, instead of opening a new thread.

shg's suggestion to use a chart was only to demonstrate to you that the data you provided showed a downward trend, not an upward one, like you thought. It does not mean that you will need a chart to solve your problem.

[singerbatfink]

Starting a new thread with the same question wont make you popular!

SHG's answer is the correct one, for what you are asking

your way try :-

=CHOOSE((SIGN(A1-A2)+SIGN(A2-A3)+2)/2+1,"Upward","No Trend","Downward")

Point noted... Thanks for your help.

[singerbatfink]

Sorry for the confusion... I closed the thread because I though it was exhausted and a few days old.. Id never knowingly break the rules. I just needed a solution for this, Im sure you know what its feels like..lol

Thanks to SHG and Squiggler47 for your help and comments..

B

[singerbatfink]

hmmm early morning! Caffeine not hit home yet!

=CHOOSE((SIGN(B7-C7)+SIGN(C7-D7)+4)/2,"Upward","No Trend","Downward")

removes the extra+1


10 15 20 UP
20 15 10 Down
20 25 10 No Trend
10 25 20 No Trend
10 10 10 No Trend
25 25 10 Down
10 25 25 UP


I thought about the last 2 cases as an afterthough, if you want them then the formula is :-

=CHOOSE((SIGN(B9-C9)+SIGN(C9-D9))+3,"UP","UP","No Trend","Down","Down")

This is great, but If the scenario is 25, 25, 10 Id like a return of No Trend , and also if 10, 25, 25 Id like a return of No Trend so that only three months of data going in the same direction is either an upward or downward trend.

Sorry to be a pain....

B

[DP978]

Just add an iff statement around what they wrote..

If(OR(B9 = C9, C9 = D9), "No Trend", The Above Code Here)

[shg]

Threads merged.

[singerbatfink]

Thanks to all who contributed to helping me solve this one..

Ive uploaded the finished work to share if anyone would like it..

Regards

B

[squiggler47]

=IF(D2="","",IF(OR(B2=C2,C2=D2),"No Trend",CHOOSE((SIGN(B2-C2)+SIGN(C2-D2))+3,"UP","UP","No Trend","Down","Down")))

and

=IF(D2="","",CHOOSE((SIGN(B2-C2)+SIGN(C2-D2))+3,"UP","No Trend","No Trend","No Trend","Down"))

are the same, the extra UP/Down from row 4 in your sheet takes care of 2 values being the same

[squiggler47]

=MID("ñóóóò ",(SIGN(B2-C2)+SIGN(C2-D2))+3+(D2="")*5,1)

will give you the arrows note the characters are entered on the numeric keypad holding down the Alt Key and then typing 0241 then releasing the alt key(the same with 0243 0243 0243 0242)

I modified your formulas to make them shorter!