Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 12th 06, 08:20 PM 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  
Old January 13th 06, 12:12 AM 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:

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$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  
Old January 13th 06, 10:24 AM posted to microsoft.public.excel.worksheet.functions
[email protected]
 
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 07:37 AM
X IN A CELL TO CALCULATE A FORMULA JUSTIN Excel Worksheet Functions 1 January 3rd 05 11:08 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM


All times are GMT +1. The time now is 06:42 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017