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

 

[jmthompson]

What is the definition of stopped selling, the week before the first week there were no sales (first zero after the first week) or the last week that there were sales in the entire row (last cell with non-zero data?)?

 

[chrysti]

Zero before are when the color was not available, and the zeros after are when the colors were sold out or stopped selling. I hope that this clears that up.

 

[jmthompson]

And where do you want the summary of this data for each color? Or do you just want the first week one color and the last week another color?

 

[sous2817]

Couldn't you use:

=COUNTIF(B1:L1,"<> 0")

In M1 and copy down? (adjust ranges as necessary). That shuold get you the # of weeks it sold.

 

[jmthompson]

Most likely and then a macro to find the first week of sales

 

[chrysti]

I can get the count part, that is the least of my worries, it is how to find the first week that I am having the most trouble with Any ideas on how to find that out in a range that could be as large as 52 weeks???

 

[sous2817]

This seemed to work for me. Adjust ranges as needed

=OFFSET(INDEX(A2:G2,MATCH(1,A2:G2,0)),-1,0)

 

[sous2817]

Here is the whole shabang:

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	1	1	1	1	1		wk3	5
4	Color3	1	1	1	1	1	1	1		wk1	7
5	Color4	0	0	0	1	1	1	1		wk4	4
Sheet1

Hmm, seems to only work if 1 (or whatever you want it to be) is sold. Maybe someone who is less of a hack will come by and save the day

 

[jmthompson]

Very cool. I haven't invested the time to fully understand Offset yet. SOmeday I will have to read through the posts that I've bookmarked on the subject