Calculate Average Number of Days based on Increase or Decrease of Price

Hi Friends,

I have the below requirement. The table below has the prices of a certain item for 9 days. I have to calculate the average number of days the prices went up continuously, and the average number of days the prices went down continuously. For eg, the answer is 3 for the increase as it increased continuously for the first three days and the last three days.

Could anyone help me with the formula required to do this task? Thanks a ton for your help.

Day	Price
1	10
2	20
3	30
4	20
5	18
6	16
7	25
8	28
9	35

Assume your price is in the range(b2:b10)
formula to count the increase =SUMPRODUCT(--(B2:B9<=B3:B10))
in this case answer will be 5, if I am not wrong.

Hi Sadath31,

Thanks for your reply. However, the result should be 3 (average number of continuous days for which the price increased). Please let me know if I have worded my question ambiguously?

Please see attached sheet for fomulas.
But by my calculation the price only increases twice at the beginning of the week as you do not know the price before day 1.

Very similar solution to @BarryTSL, except you wanted the average and not the max, so another helper column was needed.

BTW, I also agree with his comment on counting increases. Depending on your view, the first set could be 2 days of increases since the base does not really count. Alternatively, the end could be four, since 16, 25, 28, 35 all increase. You seem to change your technique on how to count the increases. I would argue the data you provided is either 2 and then 3 days (for an averages of 2.5) or 3 and then 4 days.

I just did the increase. Decrease is similar.

Pauley

Sadath31, BarryTSL, Pauleyb,

Thank you for your time in helping me out. Much appreciated.

Regards, Navs.

You are spot on BarryTSL. I was wrong...