[gsullivan]

Hi,
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:

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