software

Can anyone help? I can't think of a way to do this in a single cell
expression.

I have a column of week ending dates next to weekly sales next to end
of week stock holding and I need to add a column that shows how mnay
weeks the stock will last for providing I buy no more stock. Rough
example below.

Week Sales Stock Cover
1 150 500 3.2
2 150 500 2.8
3 150 700 3.6
4 150 800 5.0
5 250 800 5.?
6 200 700 ?
7 150 600 ?
8 100 500 ?
9 100 500 ?
10 100 500 ?

I hope that helps. It need to take forward sales off until it get to a
part week and then work out the fractions like. 500-(150-150-150)/250
= 3.2 weeks cover. I hope this is clear... I just need a while
statement but alas Excel does have that and I can't figure a complex
way to use If conditions...Assumptions:

A11 contains your labels, Week, Sales, Stock, and Cover

A2:C11 contains your data

Formula(s):

If you absolutely must have a single cell formula...

D2, copied down:

=(MATCH(TRUE,SUBTOTAL(9,OFFSET(B3:B$11,,,ROW(B3:B$ 11)-ROW(B3) 1))gt;C2,0)-1
) (C2-SUM(B3:INDEX(B3:B$11,MATCH(TRUE,SUBTOTAL(9,OFFSET( B3:B$11,,,ROW(B3:
B$11)-ROW(B3) 1))gt;C2,0)-1)))/INDEX(B3:B$11,MATCH(TRUE,SUBTOTAL(9,OFFSET(B
3:B$11,,,ROW(B3:B$11)-ROW(B3) 1))gt;C2,0)-1 1)

....confirmed with CONTROL SHIFT ENTER, not just ENTER.

However, the following would be better...

D2, copied down:

=MATCH(TRUE,SUBTOTAL(9,OFFSET(B3:B$11,,,ROW(B3:B$1 1)-ROW(B3) 1))gt;C2,0)-1

....confirmed with CONTROL SHIFT ENTER. This will give you the number of
complete weeks.

E2, copied down:

=(C2-SUM(B3:INDEX(B3:B$11,D2)))/INDEX(B3:B$11,D2 1)

....which will give you the fraction.

F2, copied down:

=SUM(D2:E2)

....which will give you the total.

*Adjust the range B3:B$11 accordingly.

Hope this helps!

In article .comgt;,wrote:

gt; Can anyone help? I can't think of a way to do this in a single cell
gt; expression.
gt;
gt; I have a column of week ending dates next to weekly sales next to end
gt; of week stock holding and I need to add a column that shows how mnay
gt; weeks the stock will last for providing I buy no more stock. Rough
gt; example below.
gt;
gt; Week Sales Stock Cover
gt; 1 150 500 3.2
gt; 2 150 500 2.8
gt; 3 150 700 3.6
gt; 4 150 800 5.0
gt; 5 250 800 5.?
gt; 6 200 700 ?
gt; 7 150 600 ?
gt; 8 100 500 ?
gt; 9 100 500 ?
gt; 10 100 500 ?
gt;
gt; I hope that helps. It need to take forward sales off until it get to a
gt; part week and then work out the fractions like. 500-(150-150-150)/250
gt; = 3.2 weeks cover. I hope this is clear... I just need a while
gt; statement but alas Excel does have that and I can't figure a complex
gt; way to use If conditions...

Oh my, thank you so much! The first one works just perfectly and it is
suitable to fit into the actual document I use; which is rather large.
I can't thank you enough. You have been most kind.