Count Negatives from Current Week Only

[bigfoot]

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%

---

 

[Peter_SSs]

Peter_SS...
Do you think this might be best handled by writing a public function and calling THAT in the cell?

No

 

[hicksi]

If that thing of yours works, Peter_SS, then you probably only need to put a $ on all the B references, AliGW
Then, if you insert Wk8 into column I (moving the total to J and the Expect to L), then it should refresh the equations.

 

[hicksi]

No

 

[hicksi]

Why are you asking me? I think you mean the OP. This is not my query.

Some organisations DO NOT allow Excel spreadsheets containing macros (it's a security issue to thwart viruses). That was why my question to you.
But Peter has affirmed that it can be achieved using cell formulae

 

[AliGW]

If that thing of yours works, Peter_SS, then you probably only need to put a $ on all the B references, AliGW
Then, if you insert Wk8 into column I (moving the total to J and the Expect to L), then it should refresh the equations.

It makes no difference. That final range stays stuck at B2:H2 (or $B2:$H2).

 

[AliGW]

Some organisations DO NOT allow Excel spreadsheets containing macros (it's a security issue to thwart viruses). That was why my question to you.
But Peter has affirmed that it can be achieved using cell formulae

I made no mention of macros! Where did you get that from?

 

[bigfoot]

spreadsheet can be saved with macros, and yes each week a column is added, which would be the starting point for the calculation. thanks.

 

[hicksi]

It makes no difference. That final range stays stuck at B2:H2 (or $B2:$H2).

WOAH. I'm missing something. Are you saying that you aren't expecting data next week?
Or do you change the content by moving everything left and inserting the data for Wk8 in Column H?
So you're only reporting Wk2, Wk3, Wk4, Wk5, Wk6, Wk7 and Wk8 in columns B:H.

Be very careful how you move the data left. If you CUT and paste, all references to $B$2 might become REF!

 

[hicksi]

Outa here. It's sleep-time in Australia.

 

[AliGW]

Outa here. It's sleep-time in Australia.

Have a good sleep - I think you need it!!!