weeks stock cover

rascough

New Member
Joined
Oct 22, 2003
Messages
13
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Forum statistics

Threads
1,137,330
Messages
5,680,861
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top