Does anyone have a formula for Days or Weeks Supply?

[Mike]

I have a formula that uses a lot of IF statements and am looking for an
improved one. I hope someone can help.

 

[JE McGimpsey]

You'd get better responses if you posted more information (such as your
current formula).

 

[xlm]

what is your IF formula? Without seeing your formula we are unable
to provide you a correct solution.

HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,

 

[Gord Dibben]

Hard to improve something we can't see.

Post an example.


Gord Dibben MS Excel MVP

 

[John]

Pick your choice, they all relate to date or time.

EDATE,EOMONTH,NETWORKDAYS,WORKDAY,YEARFRAC
DATE,DATEDIF,DATEVALUE, DAY, DAYS360, HOUR
MINUTE, MONTH, NOW, SECOND ,TIME, TIMEVALUE
TODAY. WEEKDAY, YEAR

 

[Mike]

Well, well, well! I apologize for not providing more info. However, I thought
it was trivial in Days Supply calculation. In any case, here is what I have:

Column C2 D2 E2 F2 G2
...........................
5-Jan 12-Jan 19-Jan 26-Jan 2-Feb .........................
Sales 2,500,000 2,900,000 2,600,000 3,200,000 2,800,000 ...................
Inventory 8,000,000 8,700,000 9,100,000 9,400,000 10,300,000
...................
Production 3,200,000 3,300,000 2,900,000 4,100,000 4,000,000
.....................
Inv+Prod 11,200,000 12,000,000 12,000,000 13,500,000 14,300,000 .............

My formula for calculating Days Supply is:

=IF(C6>SUM(C3:H3),(6+(C6-SUM(C3:H3))/I3),IF(C6>SUM(C3:G3),5+(C6-SUM(C3:G3))/H3,IF(C6>SUM(C3:F3),4+(C6-SUM(C3:F3))/G3,IF(C6>SUM(C3:E3),3+(C6-SUM(C33))/F3,IF(C6>SUM(C33),2+(C6-SUM(C3:E3))/E3,0)))))

I hope this helps.