HELP! Single cell formula to calculate weeks cover of stock on forward sales.
 

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...

HELP! Single cell formula to calculate weeks cover of stock on forward sales.
 

Assumptions:

A1:D1 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))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))C2,0)-1)))/INDEX(B3:B$11,MATCH(TRUE,SUBTOTAL(9,OFFSET(B
3:B$11,,,ROW(B3:B$11)-ROW(B3)+1))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))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 .com,
wrote:

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...

HELP! Single cell formula to calculate weeks cover of stock on forward sales.
 

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.