Forecast closing stock formula - more than 8 'IF' statements problem

[gsullivan]

Hi,
Have asked around, but without luck and am getting desperate here. I am looking for a single cell formula to calculate the required closing stock value based upon forecasted sales and target stock cover in days. The below may help to explain: (is also attached as an excel file)

Department 1

Wk / Sales fc / Targetcovr days / Closingstock val reguired
1 / na / 30 / 11,143 (formula for this column please!?)
2 / 3000 / 35 / 14000
3 / 1500 / 49 / 16000
4 / 2000 / 55 / FALSE
5 / 3500 / 55 / FALSE
6 / 4000 / 55 / FALSE
7 / 3000 / 55 / FALSE

I can imbed seven IF statements and drag down, which will do the trick if the target days cover is less than 49 days (below formula calculates the 11,143 for week 1 closing stock) :

=IF(C2<=7,C2*B3/7,IF(C2<=14,(B3+(C2-7)*B4/7),IF(C2<=21,(B3+B4+(C2-14)*B5/7),IF(C2<=28,(B3+B4+B5+(C2-21)*B6/7),IF(C2<=35,(B3+B4+B5+B6+(C2-28)*B7/7),IF(C2<=42,(B3+B4+B5+B6+B7+(C2-35)*B8/7),IF(C2<=49,(B3+B4+B5+B6+B7+B8+(C2-42)*B9/7))))))))

[where C2= Target stock cover days & B3, B4 etc = sales forecasts by week]

but if it is over 49 days as displayed, it falls over (shows FALSE).

Can anyone help with a single cell formula which will give me my closing stock value? I will be very grateful for any help.

Regards
Gerry

[DonkeyOte]

Not entirely sure I agree with your results in your sample file for <=42 onwards given you jump in the divisor from C11 to C13 (should be C12 I believe)

Perhaps then based on above correction the below will work:

Please Login or Register  to view this content.

[zbor]

Where do I make mistake...

Closing_stock_calculation(1).xls

[zbor]

Actually, SUMIF, rather than SUMPRODUCT

=SUMIF($B$6:$B$52;"<"&D6/7;$C$6:$C$52)+VLOOKUP(ROUNDUP(D6/7;0);$B$6:$C$52;2;FALSE)*(7-MOD(D6;7))/7

[gsullivan]

Guys, thank you so much for incredibly quick responses. Have had a go at yours Donkeyote and it works which is fantastic. Will also check yours zbor regardless. Once again thanks very much.
GS