Need find a tren Closed

Question

Hello,
   Hey i am new to excel. i ma not expert in maths derivations - regression like...
    i need to find a trend. 
I have a list of materials and rate for the material in month wise.
  i need what trend the rate is. ie. increasing or decreasing or some fluctuations.

  Ex:
  Material   April   May   June  July  Trend
    A            20     30       69    80    ?           (INCREASING , DECREASING , OR .. ) 
   B              80     30      30   20    ?
   C             70    80    68        90    ?

  ANY ONE CAN HELP ME ....

Trowa · Answer

Hi Mukil,

Let's say the data 20,30,69,80 is in range B2:E2 then put the following formula in F2:
=TREND(B2:E2,{1,2,3,4},5)

Best regards,
Trowa

Trowa · Answer

Hi Mukil,

I have put the numbers in range A1:D3:
10	20	25	30
30	20	15	10
10	8	25	15

In column F I have put the trend formula:
=TREND(A1:D1,{1,2,3,4},5)
=TREND(A2:D2,{1,2,3,4},5)
=TREND(A3:D3,{1,2,3,4},5)

This results in:
37,5
2,5
22,5

In column G I have put an array formula (Array formula's need to be confirmed by hitting Ctrl+Shift+Enter):
=SUM(IF(A1:D1<F1,1,0))
=SUM(IF(A2:D2<F2,1,0))
=SUM(IF(A3:D3<F3,1,0))

This results in:
4
0
3

In column E i have put the following formula:
=IF(G1=4,"Increasing",ALS(G1=0,"Decreasing","Fluctuating"))
=IF(G2=4,"Increasing",ALS(G2=0,"Decreasing","Fluctuating"))
=IF(G3=4,"Increasing",ALS(G3=0,"Decreasing","Fluctuating"))

This results in:
Increasing
Decreasing
Fluctuating

Take a look at the formula in column E. It has the condition G1=4. The number 4 stands for the amount of numbers, which in this case is 4 (A1:D1), but I imagine that that number is different in your file.

Try to figure out what is happening and don't hesitate to reply if something is unclear or you need some more explanation/information.

Best regards,
Trowa

Trowa · Answer

Hi Mukil,

Why don't you fill the empty cells with 0's?, since if nothing is sold, the purchase rate is zero.
If you only sold something in the fourth month with a purchase rate of 8. Then the trend isn't 8 and constant, but a lot less and fluctuating.

If I understand your elaboration correctly this solves your query.
Otherwise let me know.

Best regards,
Trowa

Need find a tren

3 responses

Similar discussions

Subscribe To Our Newsletter!