# Office 365 >  >  Weeks of Stock Cover Calculation

## suzyanne37

Hello All,

Ive worked out how to work out how many weeks of stock cover I have using a fab formula I found online and then adjusted.

This is it: 

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))<=B8))+LOOKUP(0,SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))-B11:$P11-B8,(B8-(SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))-B11:$P11))/B11:$P11)

This looks at my opening stock in cell B8, My Sales forecast for the next 15 weeks in B11:P11 BUT I have a second set where the sales forecast is not input on one row. 

Im trying to adjust the formula to look at the sales forecast in cells: D9,G9,J9,M9,P9,S9,V9,Y9,AB9,AE9,AH9,AK9,AN9,AQ9 & AT9

Does anyone know how I can re-write this formula to do this? PLEASE do not suggest a VBA becase I don't get them and don't have the time to try and understand lol! 

Thanks in advance to anyon that can help

----------


## oeldere

Maybe I can't help you, but

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

----------


## suzyanne37

Hi - thanks for the reply.

Ive attached a very basic example showing both versions currently used. The first version (which includes what I have done to calculate the weeks of stock figure) and then the second version where I want to look at the sales forecast every couple of cells, and not in one long row. 

Both have the same information on them and the first version shows the expected results for how many weeks cover the opening stock would give. 

The original file for the second version includes over 500 SKU's and is a very large file. I would rather not change its current format to be like the first- I would rather try to adjust the formula from the first version, 

I hope Im making sense?

PLAN EXAMPLES.xlsx

----------


## oeldere

Not for me, maybe another forummember.

----------


## suzyanne37

How do I get other people to see and respond to this?

----------


## DiscoLee

Brilliant formula Suzyanne37.  Really helped when calculating stock cover. Been looking for something like this for a long time!! Thanks :Smilie: 





> Hello All,
> 
> Ive worked out how to work out how many weeks of stock cover I have using a fab formula I found online and then adjusted.
> 
> This is it: 
> 
> =SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))<=B8))+LOOKUP(0,SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))-B11:$P11-B8,(B8-(SUBTOTAL(9,OFFSET(B11:$P11,,,,COLUMN(B11:$P11)-COLUMN(B11)+1))-B11:$P11))/B11:$P11)
> 
> This looks at my opening stock in cell B8, My Sales forecast for the next 15 weeks in B11:P11 BUT I have a second set where the sales forecast is not input on one row. 
> ...

----------

