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]

If that thing of yours works, Peter_SS, then you probably only need to put a $ on all the B references,

There was no mention of deleting any data ..

each week the data is updated with another week added.

.. so I had assumed that wk8 data would be added by inserting a new column between wk7 and total. If that is the case then my formula requires no $ signs to be added.


@bigfoot
Did you try the formula suggested in post #6?

 

[AliGW]

Peter - did you see my comment? The final range in your formula does not expand when the extra column is added: the others do as expected.

 

[Peter_SSs]

Peter - did you see my comment? The final range in your formula does not expand when the extra column is added: the others do as expected.

Ah, I thought that was just directed at hicksi so didn't really focus on it. You are right, thanks for pointing that out.
Still shouldn't need any absolute references and I think this should do it.

New formula for K2 for the layout in post #6, changes highlighted.

IF(INDEX(B2:I2,COLUMNS(B2:I2)-1)>0,"",COLUMNS(B2:I2)-1-IFERROR(AGGREGATE(14,6,(COLUMN(B2:I2)-COLUMN(B2)+1)/(B2:I2>=0),1+(I2>=0)),0))

 

[bigfoot]

a new week is added every week. the consecutive count would start with the most current week, working to the left, thru to week one. thank you.

 

[AliGW]

Peter - yes, but then when I add week 9, the formula has to be manually changed again. Why is that range not growing like the others? Bigfoot needs to add a column each week.

 

[bigfoot]

Peter, I have a question. in your formula, you reference columns B to I. isn't column I, the total? I would like only the individual weeks to be considered for the calculation. thanks to everyone for the effort in my behalf.

 

[Peter_SSs]

Peter, I have a question. in your formula, you reference columns B to I. isn't column I, the total? I would like only the individual weeks to be considered for the calculation. thanks to everyone for the effort in my behalf.

Yes, I is the total but I have only used that so the formula automatically adjusts when you add a new column to the left of that. Rather than try to guess what the formula might do, I suggest that using the layout from post #6, you try the modified formula from post #23 in cell K2 and copy down. Then try inserting your new weekly columns and populating them with data.

Peter - yes, but then when I add week 9, the formula has to be manually changed again. Why is that range not growing like the others? Bigfoot needs to add a column each week.

My preface to the new formula was that it was for the layout in post #6 for cell K2. I'm guessing that you applied the new formula after you had already inserted a new column for week 8? If not can you explain which part of the formula is not adjusting so I can double-check?

 

[AliGW]

No, Peter - I am testing it properly, i.e. adjusting the formula BEFORE inserting the column. I have already said twice which bit is not adjusting: it is the final range in the formula. Your bits in red below do NOT adjust when another week column is inserted before the total column, whereas all the other ranges do.

IF(INDEX(B2:I2,COLUMNS(B2:I2)-1)>0,"",COLUMNS(B2:I2)-1-IFERROR(AGGREGATE(14,6,(COLUMN(B2:I2)-COLUMN(B2)+1)/(B2:I2>=0),1+(I2>=0)),0))

If I add another column, I get this:

IF(INDEX(B2:J2,COLUMNS(B2:J2)-1)>0,"",COLUMNS(B2:J2)-1-IFERROR(AGGREGATE(14,6,(COLUMN(B2:J2)-COLUMN(B2)+1)/(B2:I2>=0),1+(I2>=0)),0))

Notice the other ranges have changed.

 

[bigfoot]

Peter, I tried the formula in post #23, and applied it. if wk7 was positive, it returned a blank. if wk7 was negative, it returned a 7, regardless of how many consecutives there were. I then inserted a week, wk8, with the same formula in place, using wk8 as the standard, if positive, it returned a blank, if negative, it returned an 8, in every instance

 

[Peter_SSs]

No, Peter - I am testing it properly, i.e. adjusting the formula BEFORE inserting the column. I have already said twice which bit is not adjusting: it is the final range in the formula. Your bits in red below do NOT adjust when another week column is inserted before the total column, whereas all the other ranges do.

IF(INDEX(B2:I2,COLUMNS(B2:I2)-1)>0,"",COLUMNS(B2:I2)-1-IFERROR(AGGREGATE(14,6,(COLUMN(B2:I2)-COLUMN(B2)+1)/(B2:I2>=0),1+(I2>=0)),0))

If I add another column, I get this:

IF(INDEX(B2:J2,COLUMNS(B2:J2)-1)>0,"",COLUMNS(B2:J2)-1-IFERROR(AGGREGATE(14,6,(COLUMN(B2:J2)-COLUMN(B2)+1)/(B2:I2>=0),1+(I2>=0)),0))

Notice the other ranges have changed.

I cannot replicate that and I don't see how it is even possible that of the 6 "I2"s in the formula, none of which are $I2, that when a new column is inserted in the range that 4 of them would change to J2 and 2 would not. All 6 are changing for me.

Peter, I tried the formula in post #23, and applied it. if wk7 was positive, it returned a blank. if wk7 was negative, it returned a 7, regardless of how many consecutives there were. I then inserted a week, wk8, with the same formula in place, using wk8 as the standard, if positive, it returned a blank, if negative, it returned an 8, in every instance

I also have not been able to replicate that. Here is my actual sheet with the modified formula in it and you can see that I am not getting such results.

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

.. and now here it is with wk8 inserted &* some dummy data added for that week.
Are these the results that would be expecting for this data?

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