P
patrickbirch
I have a problem at work which is causing me lots of problems. W
currently do our planning with excel spreadsheets and has part of th
plan we have a Weeks stock cover. To find this Cover calculation w
take current Inventory and subtract the weekly forcast.
At present this is done via an if statement see below:
=IF(ISERROR(IF(H333-I331<1,H333/I331*I$4,IF(H333-I331-J331<2,I$4+(H333-I331)/J331*J$4,IF(H333-I331-J331-K331<3,I$4+J$4+(H333-I331-J331)/K331*K$4,IF(H333-I331-J331-K331-L331<4,I$4+J$4+K$4+(H333-I331-J331-K331)/L331*L$4,IF(H333-I331-J331-K331-L331-M331<5,I$4+J$4+K$4+L$4+(H333-I331-J331-K331-L331)/M331*M$4,I$4+J$4+K$4+L$4+(H333-I331-J331-K331-L331)/M331*M$4)))))),0,IF(H333-I331<1,H333/I331*I$4,IF(H333-I331-J331<2,I$4+(H333-I331)/J331*J$4,IF(H333-I331-J331-K331<3,I$4+J$4+(H333-I331-J331)/K331*K$4,IF(H333-I331-J331-K331-L331<4,I$4+J$4+K$4+(H333-I331-J331-K331)/L331*L$4,IF(H333-I331-J331-K331-L331-M331<5,I$4+J$4+K$4+L$4+(H333-I331-J331-K331-L331)/M331*M$4,I$4+J$4+K$4+L$4+(H333-I331-J331-K331-L331)/M331*M$4))))))
Has you can see this is very long winded & because it is an I
statement will only look seven cells out.
H333 is the inventory cell
I331 is the forecast for one period I.E a week
I4 is the length of time this period relates to (Week or Month).
The formula subtracts the forecast from the inventory and checks if i
would relate to less than one weeks cover, If not it then subtracts th
next forecast until it finds the number of weeks forecasts it require
until it is less than one weeks cover. It then devides the inventory b
the forecast and adds the number of weeks to the result to find one th
weeks cover.
Sorry if this makes no sense!
Where this falls down is if the forecast changes drastically furthe
out in the plan the formula can not pick it up as the if statement wil
only look out approx seven cells.
I'm sure there must be an easier and better way than this - Any idea
would be gratefully received
currently do our planning with excel spreadsheets and has part of th
plan we have a Weeks stock cover. To find this Cover calculation w
take current Inventory and subtract the weekly forcast.
At present this is done via an if statement see below:
=IF(ISERROR(IF(H333-I331<1,H333/I331*I$4,IF(H333-I331-J331<2,I$4+(H333-I331)/J331*J$4,IF(H333-I331-J331-K331<3,I$4+J$4+(H333-I331-J331)/K331*K$4,IF(H333-I331-J331-K331-L331<4,I$4+J$4+K$4+(H333-I331-J331-K331)/L331*L$4,IF(H333-I331-J331-K331-L331-M331<5,I$4+J$4+K$4+L$4+(H333-I331-J331-K331-L331)/M331*M$4,I$4+J$4+K$4+L$4+(H333-I331-J331-K331-L331)/M331*M$4)))))),0,IF(H333-I331<1,H333/I331*I$4,IF(H333-I331-J331<2,I$4+(H333-I331)/J331*J$4,IF(H333-I331-J331-K331<3,I$4+J$4+(H333-I331-J331)/K331*K$4,IF(H333-I331-J331-K331-L331<4,I$4+J$4+K$4+(H333-I331-J331-K331)/L331*L$4,IF(H333-I331-J331-K331-L331-M331<5,I$4+J$4+K$4+L$4+(H333-I331-J331-K331-L331)/M331*M$4,I$4+J$4+K$4+L$4+(H333-I331-J331-K331-L331)/M331*M$4))))))
Has you can see this is very long winded & because it is an I
statement will only look seven cells out.
H333 is the inventory cell
I331 is the forecast for one period I.E a week
I4 is the length of time this period relates to (Week or Month).
The formula subtracts the forecast from the inventory and checks if i
would relate to less than one weeks cover, If not it then subtracts th
next forecast until it finds the number of weeks forecasts it require
until it is less than one weeks cover. It then devides the inventory b
the forecast and adds the number of weeks to the result to find one th
weeks cover.
Sorry if this makes no sense!
Where this falls down is if the forecast changes drastically furthe
out in the plan the formula can not pick it up as the if statement wil
only look out approx seven cells.
I'm sure there must be an easier and better way than this - Any idea
would be gratefully received