# Formula to calculate lead times using inbound, outbound and stock data

#### MattLudlam

##### New Member
Hello everyone,

I am getting in touch because I can't work out the correct formula I need to calculate lead times. Is it a nested IF, is it a COUNTIFS or is it something else entirely?

All I need is the number of weeks it will take to fulfil an order when using weekly data. It seemed a simple problem to begin with, but now appears really tricky.

Here's hoping that somebody already has something worked out for this. Below is a method I can see working, but it may not be possible in Excel?

Btw I am totally open to other ways of handling the calculation or formatting the data.

Many thanks if you're taking the time to look at this problem, I really do appreciate your help.

All the best, Matt.

Here is my effort at the calculation logic. I have given an example where an item goes out of stock and then takes 3 weeks to come back in. I am wanting to show lead times up until 12 weeks, so the calc needs to look ahead 12 weeks...

Calculate weekly volume - (stock+inbound)-sales
Count weeks until weekly volume is greater than zero
If Weekly volume > 0, Enter 0
If Weekly volume < 0, check future Inbound data (look up to 12 weeks ahead, but stop as soon as volume is > 0)
Week 1: If sum of balance + inbound > 0, then provide count (so 1 in this instance)
Week 1: If sum of balance + inbound < 0, then look at next week
Week 2: If sum of balance + inbound > 0, then provide count (so 2 in this instance)
Week 2: If sum of balance + inbound < 0, then look at next week
Week 3: If sum of balance + inbound > 0, then provide count (so 3 in this instance)
Week 3: If sum of balance + inbound < 0, then look at next week
Week 4: keep going until a positive balance is achieved (up to 12 weeks)

Here is a simple Excel example with inbound/sales/stock data plus examples of the lead time column where I'd like to replace the static figures with a dynamic formula - Lead Time Example Sheet

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Replies
1
Views
192
Replies
2
Views
281
Replies
3
Views
846
Replies
0
Views
99
Replies
2
Views
109

1,128,086
Messages
5,628,589
Members
416,326
Latest member
NinaChristal

### 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.

### Which adblocker are you using?

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

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