counting number of times value is greater than previous week

Need to be able to count the number of times the value is greater than the
previous week value in a row of numbers:
15, 13, 15, 14, 16, 17, 18
This would show a 4 as in for 4 weeks we were on an upward trend.

Then have to do the reverse for downward trends - on the same row of numbers.
15, 13, 15, 14, 16, 17, 18, 15, 14, 13
say 3 weeks later this would show downward trend for 3 weeks.

Could be 2 separate formulas. Any help would be appreciated - using a
massive if statment right now that only allows me to go back about 6 weeks -
so if the trend last longer than 6 weeks, I keep showing a 6 for the
following weeks. Trying to stay away from macros if possible.

If your data is in A1 thru G1, then in B2 enter:

=IF(B1>A1,1,0) and copy across. The sum of row 2 is your answer.

to get the other case use =IF(B1<A1,1,0)
--
Gary''s Student


"tereasajw" wrote:

> Need to be able to count the number of times the value is greater than the
> previous week value in a row of numbers:
> 15, 13, 15, 14, 16, 17, 18
> This would show a 4 as in for 4 weeks we were on an upward trend.
>
> Then have to do the reverse for downward trends - on the same row of numbers.
> 15, 13, 15, 14, 16, 17, 18, 15, 14, 13
> say 3 weeks later this would show downward trend for 3 weeks.
>
> Could be 2 separate formulas. Any help would be appreciated - using a
> massive if statment right now that only allows me to go back about 6 weeks -
> so if the trend last longer than 6 weeks, I keep showing a 6 for the
> following weeks. Trying to stay away from macros if possible.

I could do this but I need to rate over 100 rows which have over 150 columns
(this method would double my rows in a 25M file) - so I really need to have
one formula in one cell to reduce the complications. Any other ideas?

"Gary''s Student" wrote:

> If your data is in A1 thru G1, then in B2 enter:
>
> =IF(B1>A1,1,0) and copy across. The sum of row 2 is your answer.
>
> to get the other case use =IF(B1<A1,1,0)
> --
> Gary''s Student
>
>
> "tereasajw" wrote:
>
> > Need to be able to count the number of times the value is greater than the
> > previous week value in a row of numbers:
> > 15, 13, 15, 14, 16, 17, 18
> > This would show a 4 as in for 4 weeks we were on an upward trend.
> >
> > Then have to do the reverse for downward trends - on the same row of numbers.
> > 15, 13, 15, 14, 16, 17, 18, 15, 14, 13
> > say 3 weeks later this would show downward trend for 3 weeks.
> >
> > Could be 2 separate formulas. Any help would be appreciated - using a
> > massive if statment right now that only allows me to go back about 6 weeks -
> > so if the trend last longer than 6 weeks, I keep showing a 6 for the
> > following weeks. Trying to stay away from macros if possible.

I am not so sure about your layout, are your weeks are extending
horizontally or vertically?
Anyway, I tried the following formula in a single column of numbers, in
cells A1:A17. Next to them I supplied formulas in B2:B17. This formula
computes, next to any cell of the source data, the number of weeks with
downward trend, including the current week.

This is an array formula, thus it must be entered with
Shift+Ctrl+Enter. To be entered in B2.
=ROW()-MAX(ROW($B$2:B2)*($A$2:A2>$A$1:A1))

You will have to change > to < for upward trend. You might need to
add/subtract a constant, if your data do not start in row 1. You might
want to change all instances of ROW() to COLUMN() if time extends along
the horizontal. A more robust formula can be probably built, but at the
time this is the best I can come up with.

HTH
Kostis Vezerides

Assuming that A1:AZ1 contains your data, try...

Upward:

=SUMPRODUCT(--(B1:AZ1>A1:AY1))

Downward:

=SUMPRODUCT(--(B1:AZ1<>""),--(B1:AZ1<A1:AY1))

Hope this helps!

In article <[email protected]>,
tereasajw <[email protected]> wrote:

> Need to be able to count the number of times the value is greater than the
> previous week value in a row of numbers:
> 15, 13, 15, 14, 16, 17, 18
> This would show a 4 as in for 4 weeks we were on an upward trend.
>
> Then have to do the reverse for downward trends - on the same row of numbers.
> 15, 13, 15, 14, 16, 17, 18, 15, 14, 13
> say 3 weeks later this would show downward trend for 3 weeks.
>
> Could be 2 separate formulas. Any help would be appreciated - using a
> massive if statment right now that only allows me to go back about 6 weeks -
> so if the trend last longer than 6 weeks, I keep showing a 6 for the
> following weeks. Trying to stay away from macros if possible.