hi, what I tried to copy in, below, is a grid consisting of 16 styles down, 7 weeks across, and a total. the last column, is the results I am looking for.
each week the data is updated with another week added. the latest week will always be left of the 'total' column. the formula I am looking for, would look at the most current week. if that week is positive, drop down to next style. if current week is negative, return the count of the number of consecutive negative weeks, starting with current week.
below, style 1 is a pass
style two is 2 negatives
style 3 is a pass
style 4 is 1
style 5 is
and so on. thanks for all your help
wk1 wk2 wk3 wk4 wk5 wk6 wk7 total expect
sty1 -3.9% -12.8% 27.5% 7.0% 18.0% -26.6% 1.6% -3.1%
sty2 -16.5% -26.0% -22.6% -27.4% 20.5% -4.1% -14.0% -1.7% 2
sty3 -2.1% -24.7% -0.2% 17.5% -1.1% -16.9% 52.7% 0.3%
sty4 -6.3% -35.8% -22.8% -21.5% 4.8% 5.3% -1.7% 12.5% 1
sty5 -5.7% 0.6% -8.8% -31.1% -5.4% -33.3% -32.0% -2.2% 5
sty6 -9.4% -29.5% 9.9% 6.4% 37.9% -7.8% 58.6% 9.1%
sty7 -2.2% -34.8% -0.3% -27.8% -10.3% -37.4% -10.4% 12.7% 7
sty8 0.5% -7.1% 9.8% -14.8% -60.3% -8.8% 71.1% 5.7%
sty9 -14.8% -35.7% 38.9% -12.4% -3.2% -35.5% 14.5% -6.3%
sty10 6.6% -33.6% -16.8% -14.1% -23.1% -37.3% 55.8% -8.6%
sty11 0.0% -73.1% -2.1% 9.8% -16.3% -34.2% -4.8% -5.7% 3
sty12 -1.8% -21.3% 3.8% 6.4% -34.2% -34.6% -9.5% -1.9% 3
sty13 -26.7% -44.8% 14.7% -13.8% 8.1% -37.0% 11.5% -19.0%
sty14 -32.3% -30.4% 20.5% 0.2% 1.3% -32.9% 16.2% 3.6%
sty15 -17.5% -24.5% 7.5% -1.0% 21.4% 6.5% -7.3% -0.1% 1
sty16 -21.7% -4.1% 14.9% 2.8% 4.3% -8.0% 92.6% 6.4%
each week the data is updated with another week added. the latest week will always be left of the 'total' column. the formula I am looking for, would look at the most current week. if that week is positive, drop down to next style. if current week is negative, return the count of the number of consecutive negative weeks, starting with current week.
below, style 1 is a pass
style two is 2 negatives
style 3 is a pass
style 4 is 1
style 5 is
and so on. thanks for all your help
wk1 wk2 wk3 wk4 wk5 wk6 wk7 total expect
sty1 -3.9% -12.8% 27.5% 7.0% 18.0% -26.6% 1.6% -3.1%
sty2 -16.5% -26.0% -22.6% -27.4% 20.5% -4.1% -14.0% -1.7% 2
sty3 -2.1% -24.7% -0.2% 17.5% -1.1% -16.9% 52.7% 0.3%
sty4 -6.3% -35.8% -22.8% -21.5% 4.8% 5.3% -1.7% 12.5% 1
sty5 -5.7% 0.6% -8.8% -31.1% -5.4% -33.3% -32.0% -2.2% 5
sty6 -9.4% -29.5% 9.9% 6.4% 37.9% -7.8% 58.6% 9.1%
sty7 -2.2% -34.8% -0.3% -27.8% -10.3% -37.4% -10.4% 12.7% 7
sty8 0.5% -7.1% 9.8% -14.8% -60.3% -8.8% 71.1% 5.7%
sty9 -14.8% -35.7% 38.9% -12.4% -3.2% -35.5% 14.5% -6.3%
sty10 6.6% -33.6% -16.8% -14.1% -23.1% -37.3% 55.8% -8.6%
sty11 0.0% -73.1% -2.1% 9.8% -16.3% -34.2% -4.8% -5.7% 3
sty12 -1.8% -21.3% 3.8% 6.4% -34.2% -34.6% -9.5% -1.9% 3
sty13 -26.7% -44.8% 14.7% -13.8% 8.1% -37.0% 11.5% -19.0%
sty14 -32.3% -30.4% 20.5% 0.2% 1.3% -32.9% 16.2% 3.6%
sty15 -17.5% -24.5% 7.5% -1.0% 21.4% 6.5% -7.3% -0.1% 1
sty16 -21.7% -4.1% 14.9% 2.8% 4.3% -8.0% 92.6% 6.4%
