Remember Me?

#1
January 12th 06, 08:20 PM posted to microsoft.public.excel.worksheet.functions
 Posts: n/a
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
January 13th 06, 12:12 AM posted to microsoft.public.excel.worksheet.functions
 Domenic Posts: n/a
HELP! Single cell formula to calculate weeks cover of stock on forward sales.

Assumptions:

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

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.

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
January 13th 06, 10:24 AM posted to microsoft.public.excel.worksheet.functions
 [email protected] Posts: n/a
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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Deanna Excel Worksheet Functions 7 October 5th 05 08:57 PM Kel Excel Worksheet Functions 2 April 14th 05 12:23 AM Amanda Excel Worksheet Functions 5 January 5th 05 07:37 AM JUSTIN Excel Worksheet Functions 1 January 3rd 05 11:08 PM Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM

All times are GMT +1. The time now is 12:07 PM.