Does anyone have aformula for Weeks Supply?

M

Mike

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Complicated Formula 1
same formula different result 3
A More efficient way to have a function with multiple SUMIF functions. 2
Excel formula for inventory problem 2
Value of minutes not calculated 8
If 0 then blank 8
Help with combination formula 2
Limitation of Nesting of Function 3

Top