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