Help with FORMULA

[chrysti]

I hope that someone can help me!!! I am trying to figure out how to write a formula, and I don't even know where to begin!!!

I have a list of colors and the weeks that they started selling in
wk1 wk2 wk3 wk4 wk5 wk6 wk7 wk8 wk8 wk9 wk10
Clr 1 0 1 1 1 1 2 3 2 1 1 0
Clr 2 0 0 2 3 3 2 2 1 0 0 0
Clr 3 0 0 0 1 2 1 2 0 0 0 0
Clr 4 0 0 0 0 1 2 1 1 1 1 1
Clr 5 0 1 2 3 1 2 1 1 1 0 0

I want to know what week each color started to sell, and then how many weeks after that there were sales, I need it to tell me that Clr 1 started selling wk2 and lived for 9 weeks so that I know when it stopped selling...arghhhh!!!

Please help!!! There is also the possibility that we may look at more weeks, as many as the full year so 52 weeks!!!

Thanks in advance to anyone who can help me out!!!
Chrysti

 

[sous2817]

Thanks jmthompson, it's not quite what the OP was looking for (I edited my post to show where my flaw is), but it's close. I'd be nice if you could find the first "not zero", but <>0 doesn't work in the formula.

 

[chrysti]

Thanks the OFFSET works, but I have a question...is there someway that I can make it so it won't look for a 1 to start the count, but any number? Can I specify a greater than 0, or greater than 10??? It won't always be 1, I am just using this as an example...

 

[chrysti]

Thanks the offest works, but what about for when it is not a 1??? Can I put in something that says greater than 10??? or not zero??? I hope so, then it would be perfect

 

[sous2817]

Here you go. Now looks for just the first non-zero

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		wk1	wk2	wk3	wk4	wk5	wk6	wk7
2	Color1	0	1	1	1	0	0	0		wk2	3
3	Color2	0	0	2	1	1	1	1		wk3	5
4	Color3	0	1	1	1	1	1	1		wk2	6
5	Color4	0	0	0	1	1	1	1		wk4	4
Sheet1

The crazy formulas are array formulas. They have to be confirmed with Ctrl+Shift+Enter, not just Enter.

Hope that helps...

and for the record, I hacked the formula Krishnakumar gave here:
http://www.ozgrid.com/forum/showthread.php?t=65477

 

[infuse]

Try this Formula <Code>=INDEX($B$1:$J$1,1,MATCH(TRUE,B2:J2>0,0))\<Code>
This will find the first non zero cell and put the Week name into the cell. Its an Array formula, so Ctrl+Shift+Enter

 

[sous2817]

That's much cleaner than my hack job. Thanks for posting infuse!