# Weeks of Supply

#### EdE

##### Board Regular
How would I go about figuring the weeks of supply of inventory.
I have PN's listed in a2:a10,inventory in b2:b10, weeks listed in c1:bb1, and the weekly demands of each pn are in c2:bb10. I need to know for each part what the weeks of supply are. My issue is that the week where sum of demands is great than inventory level varies based on inventory number.

any ideas?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### schielrn

##### Well-known Member
In this example I will use row 2. So are you saying you want to know what week the sum of row 2 is greater than the inventory number in b2?

#### EdE

##### Board Regular
Yes. I can insert a row above week with the week number if it makes it easier.

#### schielrn

##### Well-known Member
I would recommend using condtional formatting and try selecting c2:bb10 starting with c2 as the active cell and go to conditional formatting and put in formula is:

PHP:
``=\$B2<sum(\$C2:C2)``
<?xml:namespace prefix = sum(\$C2 /><sum(\$C2:C2)< p>
Then format to red or any other background color.

Hope that helps.
</sum(\$C2:C2)<>

#### EdE

##### Board Regular
Sorry, i didnt make my self clear. I need to know how many weeks of supply i have, whether it is 2 or 6.7. The sum range needs to change, the beginning date with todays date and end date (range) being where the sum from beginning to that particular date is greater than inventory number.

can this be done without Macro?

#### schielrn

##### Well-known Member
Ok this is just showing a conditional formatting approach. It would show that you do not have enough inventory to fill week 6 for PN1 and not enough to fill week 9 in PN2. Now if you want a hard coded number where will this formula or VBA go within the sheet?
Book1
ABCDEFGHIJKLMN
1WeekNumber123456789101112
2PN1288634569118931
3PN27513126612911212101013
Sheet1

Hope that helps.

#### schielrn

##### Well-known Member
This is the conditional formattign formula starting in C2:

=\$B2>=SUM(\$C2:C2)

#### EdE

##### Board Regular
Formula could go at the end of data range for the row or i could insert a column at C for W.O.S.

#### schielrn

##### Well-known Member
This UDF should work for you. Put in the formula at the end of the range like:

=RunOut(B2,C2:BB2)

Code:
``````Function RunOut(invAmt, weekTotals As Range)
Dim c As Range, total As Long, counter As Integer
For Each c In weekTotals
total = c.Value + total
If total = invAmt Then
RunOut = counter + 1
Exit Function
ElseIf total > invAmt Then
RunOut = counter + (invAmt - total + c.Value) / c.Value
Exit Function
End If
counter = counter + 1
Next c
End Function``````
Hope that helps.

Replies
2
Views
290
Replies
1
Views
273
Replies
3
Views
258
Replies
3
Views
387
Replies
10
Views
3K

1,191,354
Messages
5,986,173
Members
440,008
Latest member
Cmbuck

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