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%

---

 

[bigfoot]

can I clarify anything? any questions? thanks

 

[AliGW]

Yes. Please explain why the sty1 value in week 7, which is negative, triggers a pass.

 

[bigfoot]

sorry, the -3.1 is the total for all weeks. week 7 is 1.6%, which is a positive.

 

[AliGW]

Are you asking for a formula for just that final column?

 

[Peter_SSs]

Try this, copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1		wk1	wk2	wk3	wk4	wk5	wk6	wk7	total		expect
2	sty1	-3.90%	-12.80%	27.50%	7.00%	18.00%	-26.60%	1.60%	-3.10%
3	sty2	-16.50%	-26.00%	-22.60%	-27.40%	20.50%	-4.10%	-14.00%	-1.70%		2
4	sty3	-2.10%	-24.70%	-0.20%	17.50%	-1.10%	-16.90%	52.70%	0.30%
5	sty4	-6.30%	-35.80%	-22.80%	-21.50%	4.80%	5.30%	-1.70%	12.50%		1
6	sty5	-5.70%	0.60%	-8.80%	-31.10%	-5.40%	-33.30%	-32.00%	-2.20%		5
7	sty6	-9.40%	-29.50%	9.90%	6.40%	37.90%	-7.80%	58.60%	9.10%
8	sty7	-2.20%	-34.80%	-0.30%	-27.80%	-10.30%	-37.40%	-10.40%	12.70%		7
9	sty8	0.50%	-7.10%	9.80%	-14.80%	-60.30%	-8.80%	71.10%	5.70%
10	sty9	-14.80%	-35.70%	38.90%	-12.40%	-3.20%	-35.50%	14.50%	-6.30%
11	sty10	6.60%	-33.60%	-16.80%	-14.10%	-23.10%	-37.30%	55.80%	-8.60%
12	sty11	0.00%	-73.10%	-2.10%	9.80%	-16.30%	-34.20%	-4.80%	-5.70%		3
13	sty12	-1.80%	-21.30%	3.80%	6.40%	-34.20%	-34.60%	-9.50%	-1.90%		3
14	sty13	-26.70%	-44.80%	14.70%	-13.80%	8.10%	-37.00%	11.50%	-19.00%
15	sty14	-32.30%	-30.40%	20.50%	0.20%	1.30%	-32.90%	16.20%	3.60%
16	sty15	-17.50%	-24.50%	7.50%	-1.00%	21.40%	6.50%	-7.30%	-0.10%		1
17	sty16	-21.70%	-4.10%	14.90%	2.80%	4.30%	-8.00%	92.60%	6.40%
Neg Weeks

 

[AliGW]

I am part way there. In order to ensure that the formula is checking the most recent week, and to allow for new columns to be inserted immediately before the totals column, you can use this:

=IF(INDEX($B3:$I3,,MATCH($I$1,$B$1:$I$1,0)-1)>0,"",1)

This formula checks if the latest week's entry is positive, and if it is, it returns a blank (""). If it's negative, it currently returns 1 - this last bit is what now needs addressing to count back how many consecutive weeks were negative. I am sill working on this bit!

 

[AliGW]

Peter has beaten me to it!

Peter - that seems to work, however the last range in the formula does not change if a new weekly column is inserted (the other ranges do).

 

[hicksi]

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

AliGW...
Are you allowed to save spreadsheets containing macros?

 

[AliGW]

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

AliGW...
Are you allowed to save spreadsheets containing macros?

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