# Days supply in excel - formula

#### sasils

##### New Member
Hi,

Need advise from the experts in this forum. Data is below

 W1 W2 W3 W4 W5 W6 W7 W8 W9 W10 Forecast 10 20 20 10 10 20 10 20 10 10 Closing stock 40 20 40 20 20 20 10 40 10 10 Week supply 2 1 3 1.5 1 1.5 0.5

<colgroup><col><col span="10"></colgroup><tbody>
</tbody>

I would like to get a automatic calculation formulation for Week supply. For example. In Week 1 Week supply is 2 because of Week 1 closing stock can cover forecast for week 2 and week 3 (20+20) - 40 = 0, therefore it is 2.

However in Week 5 Week supply is 1.5 week because week 6 forecast is 10 and week 7 forecast is 20, closing stock in week 5 is 20, therefore week supply base on closing stock week 5 going forward is 1.5.

Hope this explains.

Cheers

Sasils

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### kvsrinivasamurthy

##### Well-known Member
Does UDF will do.

#### sasils

##### New Member
Hi

Thank you, what is UDF?

Cheers.

#### kvsrinivasamurthy

##### Well-known Member
UDF means user defined function. Function which we have to code. After coding it can be used like other function.

#### sasils

##### New Member
UDF means user defined function. Function which we have to code. After coding it can be used like other function.

Cheers.

#### kvsrinivasamurthy

##### Well-known Member
Data is in A1:K3

Formula in B4 then drag across
=GetWeeks(B3,C\$2:\$K\$2)

Code for UDF GetWeeks

Code:
Function GetWeeks(ClosingStock As Long, WeekRng As Range)

Dim T As Long

For T = 1 To WeekRng.Cells.Count

If ClosingStock > WeekRng.Cells(1, T) Then
GetWeeks = T
ClosingStock = ClosingStock - WeekRng.Cells(1, T)

ElseIf ClosingStock = WeekRng.Cells(1, T) Then
GetWeeks = T
Exit For

Else
GetWeeks = GetWeeks + Round(ClosingStock / WeekRng.Cells(1, T), 2)
Exit For
End If

Next T

End Function

Open tab Developer --> Visual Basic
IN VIsual Basic window

Insert --> Module

Now Paste the given code to module

Close the VB window

UDF is available in the function list like other functions.

Last edited:

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,159
Messages
5,857,712
Members
431,891
Latest member
shirazx3

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