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

[matthew.webb]

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

 

[Domenic]

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))>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$11)-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!

 

[matthew.webb]

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.

 

[Aidan Arkinson]

How easy is it to change this formula to compare data that runs horizontally instead of vertically. I've chnaged the ranges as I'd expect but am just getting an N/A#