Formula to calculate supply based on forecast

[Jubjab]

I want to calculate supply in days depending on future expectations (each month having 30 days). Example:

Stock (A1): 32
Usage month 1 (A2): 16
Usage month 2 (B2): 48
Usage month 3 (C2): 28

In this case, the formula should evaluate to 40 days: first month uses 16 units, leaving 16 in stock. The next month uses 48, and the 16 remaining will cover 10 days => 30 + 10 = 40 days.

I can get it to work using a huge formula of nested IF:s, but would be interested to hear if there are more elegant solutions? The nested IF approach also is limited to a maximum of 6 months forward, but I would of course like to have a formula which could use up to one year of predicted use. I also don't want a VBA solution.

Thanks a lot in advance for all ideas!

 

[mallard1976]

for 12 months: (A2:A13)

B1: =MAX($B$2:$B$13)
B3: =IF(AND(IF(SUM($A$2:A3)>$A$1,"STOP","")="STOP",IF(SUM($A$2:A2)>$A$1,"STOP","")<>"STOP"),COUNT($A$2:A3)*30-30+($A$1-SUM($A$2:A2))*30/A3,"")

Copy B3 to B4 - B13

You can have as many months as you please.

 

[Jubjab]

Thanks for the suggestion, but the thing is that I want to have all of that in just one single formula, i.e. all 12 months in just one cell. So I don't want to copy it down or to the side.

 

[Jubjab]

Here is a clearer example of what I want to accomplish. The result of the formulas are in cells B3 and B6, the other cells have the values.

Työkirja6
	A	B	C	D	E	F
1	Stock	Month1	Month2	Month3	Month4	Month5
2	60	20	30	40	10	20
3	Coverageindays	67,5
4
5	10	5	5	5	5	5
6	Coverageindays	60
Taul1

 

[mallard1976]

ok, try this. You can extend it to as many months as you please:

=IF(A2-SUM(B2:B2)>0,30,A2*30/B2)
+IF(A2-SUM(B2:C2)>0,30,IF(A2-SUM(B2:B2)>0,(A2-SUM(B2:B2))*30/C2))
+IF(A2-SUM(B2:D2)>0,30,IF(A2-SUM(B2:C2)>0,(A2-SUM(B2:C2))*30/D2))
+IF(A2-SUM(B2:E2)>0,30,IF(A2-SUM(B2:D2)>0,(A2-SUM(B2:D2))*30/E2))
+IF(A2-SUM(B2:F2)>0,30,IF(A2-SUM(B2:E2)>0,(A2-SUM(B2:E2))*30/F2))
+IF(A2-SUM(B2:G2)>0,30,IF(A2-SUM(B2:F2)>0,(A2-SUM(B2:F2))*30/G2))
+IF(A2-SUM(B2:H2)>0,30,IF(A2-SUM(B2:G2)>0,(A2-SUM(B2:G2))*30/H2))
+IF(A2-SUM(B2:I2)>0,30,IF(A2-SUM(B2:H2)>0,(A2-SUM(B2:H2))*30/I2))
+IF(A2-SUM(B2:J2)>0,30,IF(A2-SUM(B2:I2)>0,(A2-SUM(B2:I2))*30/J2))
+IF(A2-SUM(B2:K2)>0,30,IF(A2-SUM(B2:J2)>0,(A2-SUM(B2:J2))*30/K2))
+IF(A2-SUM(B2:L2)>0,30,IF(A2-SUM(B2:K2)>0,(A2-SUM(B2:K2))*30/L2))
+IF(A2-SUM(B2:M2)>0,30,IF(A2-SUM(B2:L2)>0,(A2-SUM(B2:L2))*30/M2))

 

[Jubjab]

ok, try this. You can extend it to as many months as you please:

=IF(A2-SUM(B2:B2)>0,30,A2*30/B2)
+IF(A2-SUM(B2:C2)>0,30,IF(A2-SUM(B2:B2)>0,(A2-SUM(B2:B2))*30/C2))
+IF(A2-SUM(B2:D2)>0,30,IF(A2-SUM(B2:C2)>0,(A2-SUM(B2:C2))*30/D2))
+IF(A2-SUM(B2:E2)>0,30,IF(A2-SUM(B2:D2)>0,(A2-SUM(B2:D2))*30/E2))
+IF(A2-SUM(B2:F2)>0,30,IF(A2-SUM(B2:E2)>0,(A2-SUM(B2:E2))*30/F2))
+IF(A2-SUM(B2:G2)>0,30,IF(A2-SUM(B2:F2)>0,(A2-SUM(B2:F2))*30/G2))
+IF(A2-SUM(B2:H2)>0,30,IF(A2-SUM(B2:G2)>0,(A2-SUM(B2:G2))*30/H2))
+IF(A2-SUM(B2:I2)>0,30,IF(A2-SUM(B2:H2)>0,(A2-SUM(B2:H2))*30/I2))
+IF(A2-SUM(B2:J2)>0,30,IF(A2-SUM(B2:I2)>0,(A2-SUM(B2:I2))*30/J2))
+IF(A2-SUM(B2:K2)>0,30,IF(A2-SUM(B2:J2)>0,(A2-SUM(B2:J2))*30/K2))
+IF(A2-SUM(B2:L2)>0,30,IF(A2-SUM(B2:K2)>0,(A2-SUM(B2:K2))*30/L2))
+IF(A2-SUM(B2:M2)>0,30,IF(A2-SUM(B2:L2)>0,(A2-SUM(B2:L2))*30/M2))

Thanks again, but this is pretty much what I started with. As I said in my OP:

I can get it to work using a huge formula of nested IF:s

At the moment I think I have to revert to VBA after all and make an UDF.

 

[mallard1976]

but this works for 12 or more months ! and you said you were limited to 6 months with nested IFs.
you only have 2 nested IFs here...
others are added using "+"