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%

---

 

[AliGW]

I've just rebooted my laptop and it is working fine here now. Must have been a gremlin.

 

[Peter_SSs]

I've just rebooted my laptop and it is working fine here now. Must have been a gremlin.

 

[bigfoot]

using the formulas displayed, if the current week is negative, it returns the count of all the weeks, not just the consecutive negative weeks. I am using Excel 2007, which I believe does not have the AGGREGATE function. could that be affecting the answer? again thanks.

 

[AliGW]

using the formulas displayed, if the current week is negative, it returns the count of all the weeks, not just the consecutive negative weeks. I am using Excel 2007, which I believe does not have the AGGREGATE function. could that be affecting the answer? again thanks.

Probably. You ought to have mentioned your version sooner!

 

[bigfoot]

truly sorry

 

[bigfoot]

can the AGGREGATE function, be converted to an EXCEL 2007 function?

 

[AliGW]

The AGGREGATE function first appeared in Excel 2010. You are going to have to hope that Pete will know how to make the formula work in 2007. It looks like he is offline now - maybe tomorrow.

 

[bigfoot]

thanks

 

[Peter_SSs]

using the formulas displayed, if the current week is negative, it returns the count of all the weeks, not just the consecutive negative weeks. I am using Excel 2007, which I believe does not have the AGGREGATE function.

I had wondered if you were using an earlier version so I tested in Excel 2007 and whilst (as expected) the formula did not work, I did not get the symptoms you described so I assumed the version was not the issue.

Anyway, now we have that sorted, for the same layout again, try this in K2.
This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be 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

 

[hicksi]

Spreadsheet Formulas

Cell Formula K2 {=IF(INDEX(B2:I2,COLUMNS(B2:I2)-1)>0,"",COLUMNS(B2:I2)-1-IFERROR(LARGE(IF(B2:I2>=0,COLUMN(B2:I2)-COLUMN(B2)+1),1+(I2>=0)),0))} <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Peter... Can I leave this thread with COMPLETE ADMIRATION for how you use the 'features' of Excel to achieve a result.

In that formula, you have used:
- Fuzzy maths (I2>=0) returns true/false, but that can be interpreted as 1/0
- In-line arrays {.....}
- Error handling -IFERROR(...) which is actually a further use of Excel's fuzzy maths

WOW

In other places I've seen you use true/false as a multiplier to sum only positive values. Brilliant use of how Micky-Soft has tried to make Excel easy for users.