weeks stock cover

[rascough]

Hi,

I currently have a planning spreadsheet, which shows a current stock on hand, and then usage spread weekly out for the next 6 months.

What would be the best option to show stock cover at the start of each week,

I am currently using
=IF(ISERROR(J7*4/SUM(K7,N7,Q7,T7)),"6+",(J7*4/SUM(K7,N7,Q7,T7)))
which shows for the following 4 weeks usaga (k7 to t7) and will return a result or show 6+ if over six weeks.

Im sure this isnt working properly and wouls like to expand to look at 8 weeks worth of stock

Richard

 

[Andrew Fergus]

Hi Richard

I suspect your question hasn't been answered because it is hard to follow what you are trying to do. Can you download the html maker and post a sample of what you are doing? Or if you are having issues with that, can you explain what is in cell J7 - is this the current stock level? And the values in cells K7, N7, Q7 and T7 - are these the forecast usage quantities for the next 4 weeks? Also, why do you have the iserror - are there potential non-numercial values in the cells you are wanting to add? If not then you don't need that.

My gut feel is this could be simplified using a formula like this (assuming I'm understanding this correctly):
=MIN(6, J7*4/SUM(K7,N7,Q7,T7))

and if you wanted to extend the formula to look at the next 8 weeks, it might be something like this :
=MIN(6, J7*8/SUM(K7, N7, Q7, T7, W7, Z7, AC7, AF7))

This will give you the forward cover, but if that cover is more than 6 it will show the value 6 instead.

Andrew