Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to calculate sales tax from total sales | Excel Worksheet Functions | |||
how do I create a formula to calculate sales tax using Yes & No | Excel Worksheet Functions | |||
looking for a formula | Excel Worksheet Functions | |||
X IN A CELL TO CALCULATE A FORMULA | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |