# calculating out of stock dates

1. ## calculating out of stock dates

Hello,

google could not help - so I hope one of you can.
I need to do an OOS calculation for a data set as in attached picture(somehow could not copy the table into this post or attach an excel)

available info:
current stock
Forecast per week (calendar week in top row)

formula needed:
once the running total of weekly forecast is higher than the current stock level show OOS week in same row column "X". I started typing a long long long IF formula (as this is where my excel skills end) but it must be easier than this.

Thank you
Lydia

Untitled.jpg

2. ## Re: calculating out of stock dates

You don't show the cell ranges that your data is contained in, so I've assumed that your sample is located in A1:K7

With that in mind, this formula in C3 returns the correct OOS week.

=INDEX(\$D\$2:\$K\$2,MATCH(TRUE,INDEX(SUBTOTAL(9,OFFSET(\$D3,,,1,COLUMN(\$D\$3:K3)-COLUMN(\$D3)+1))>\$B3,),0))

To attach a sample workbook,

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. ## Re: calculating out of stock dates

Hi Jason,

thank you very much! this works perfectly.

Lydia

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1