Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Does anyone have aformula for Weeks Supply?
I have a formula that uses a lot of nested IF statements and am looking for an
improved one. Currently, I calculate my weeks supply as follows: Row C1 D1 E1 F1 5-Jan 12-Jan 19-Jan 26-Jan Sales 2.5 2.9 2.6 3.2 Inventory 8.0 8.7 9.1 9.4 Production 3.2 3.3 2.9 4.1 Inv + Prod 11.2 12.0 12.0 13.5 My Weeks Supply formula for Jan 5 is: =IF(D6=SUM(D3:I3),(6+(D6-SUM(D3:I3))/J3),IF(D6=SUM(D3:H3),5+(D6-SUM(D3:H3))/I3,IF(D6=SUM(D3:G3),4+(D6-SUM(D3:G3))/H3,IF(D6=SUM(D3:F3),3+(D6-SUM(D3:E3))/G3,IF(D6=SUM(D3:E3),2+(D6-SUM(D3:F3))/F3,0))))) The formula works, however it has its limitations. If anyone has a different or improved one, I would appreciate sharing it with the group. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Does anyone have aformula for Weeks Supply?
Hi Mike
Can you describe in words, what you desire. Regards, Per On 24 Dec., 14:44, Mike wrote: I have a formula that uses a lot of nested IF statements and am looking for an improved one. Currently, I calculate my weeks supply as follows: Row * * * * *C1 * * * * * *D1 * * * * * * E1 * * * * * F1 * * * * 5-Jan * 12-Jan *19-Jan *26-Jan Sales * 2.5 * * 2.9 * * 2.6 * * 3.2 Inventory * * * 8.0 * * 8.7 * * 9.1 * * 9.4 Production * * *3.2 * * 3.3 * * 2.9 * * 4.1 Inv + Prod * * *11.2 * *12.0 * *12.0 * *13.5 My Weeks Supply formula for Jan 5 is: =IF(D6=SUM(D3:I3),(6+(D6-SUM(D3:I3))/J3),IF(D6=SUM(D3:H3),5+(D6-SUM(D3:H3*))/I3,IF(D6=SUM(D3:G3),4+(D6-SUM(D3:G3))/H3,IF(D6=SUM(D3:F3),3+(D6-SUM(D3*:E3))/G3,IF(D6=SUM(D3:E3),2+(D6-SUM(D3:F3))/F3,0))))) The formula works, however it has its limitations. If anyone has a different or improved one, I would appreciate sharing it with the group. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Does anyone have aformula for Weeks Supply?
What is in row 6 ? What's in col G H I J ? If you register you can post a sample of your sheet - Makes it easier to help -- Pecoflyer Cheers - *_Membership_is_free_* & allows file upload -faster and better answers ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=43997 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Does anyone have a formula for Days or Weeks Supply? | Excel Discussion (Misc queries) | |||
Need formula like ISBLANK that returns "no value" even if there is aformula in referenced cell | Excel Worksheet Functions | |||
Weeks of Supply Function | Excel Worksheet Functions | |||
Weeks of Supply Calculation | Excel Discussion (Misc queries) | |||
calculate weeks from a start date ( not yr weeks) | Excel Worksheet Functions |