Counting weeks the number of cells between the first and last nonblank cell

[afarcet]

I'm stuck. I can't find a way to crack this, should be simple.

I have a table with
Rows: customers
Columns: weeks

The data represents the number of orders each customer has made each week (0 and up).

I need to determine the average number of weeks of 'activity' (i.e. purchasing) per customer from first to last order. In other words the number of cells between the first non-zero and the last non-zero, knowing that there may be many weeks between one purchase and another, then more blanks, then more purchases, etc.

Thank you.

 

[xld]

Try

=SUMPRODUCT(--(Sheet1!2:2>0))

etc.

 

[afarcet]

Thanks , but that returns the total number of weeks in which there was an order. Useful. But I need the time elapsed in weeks between the first and last order (no matter if the orders have been continuous or broken up over time).

Is that clear?

 

[barry houdini]

You mention both zeroes and blanks, which of those do you have when there are no purchases?

 

[afarcet]

Good question, sorry. I have blanks in the pivot table I'm using.

 

[barry houdini]

Try this formula for the number of weeks between the first number and the last

=MATCH(9.9E+307,2:2)-MATCH(TRUE,INDEX(2:2<>"",0),0)

 

[afarcet]

That works great! Respect.

Many thanks.