Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula to calculate sales tax from total sales Deanna Excel Worksheet Functions 7 October 5th 05 08:57 PM
how do I create a formula to calculate sales tax using Yes & No Kel Excel Worksheet Functions 2 April 14th 05 12:23 AM
looking for a formula Amanda Excel Worksheet Functions 5 January 5th 05 08:37 AM
X IN A CELL TO CALCULATE A FORMULA JUSTIN Excel Worksheet Functions 1 January 4th 05 12:08 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 02:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"