Ok i have a table #1 with dates and the two cells under it is the day's products usage. I project out two weeks on the dates and the products usage. On another woorsheet i have that days inventory for this product. I want to take todays opening inventory and project out how many day's that product will last from my tabel #1 products usage. Example If I have 1000 items in my inventory and project to use x amount per day. What day will this inventory last till.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Ok i have a table #1 with dates and 1
- Thread starter HOMMER
- Start date
-
1
- #2
Not sure if this is what you want or if it will work with your problem but give it a go.
Insert this into a module on the workbook you are try to do your calculation.
Public Function WhichDay(MyAmount As Range, DateRange As Range, AmountRange As Range)
Dim Dates() As Date
Dim DateCount, Counter As Integer
Dim AmountLeft As Long
Counter = 0
AmountLeft = MyAmount.Value
For Each c In AmountRange
AmountLeft = AmountLeft - c.Value
If AmountLeft > 0 Then Counter = Counter + 1
Next
DateCount = 0
For Each c In DateRange
DateCount = DateCount + 1
ReDim Preserve Dates(DateCount)
Dates(DateCount) = c.Value
Next
If Counter + 1 > UBound(Dates()) Then
WhichDay = "Still " & AmountLeft & " Left"
Else
WhichDay = Dates(Counter + 1)
End If
End Function
This is a user defined function and it will appear in the Function list in Excel.
You need to pass it the cell with the Opening Inventory amount. The excel range of dates. Excel range with the daily usage.
It will then return the date that the Product will run out.
Example:
Column A Column B Column C
01/01/01 500 830
02/01/01 100
03/01/01 200
04/01/01 10
05/01/01 20
06/01/01 50
07/01/01 90
type in formula =WhichDay(C1,A1:A7,B1:B7)
this would then return 05/01/01 because that is the day that the stock runs out. (830-500-100-200-10-20=0)
Insert this into a module on the workbook you are try to do your calculation.
Public Function WhichDay(MyAmount As Range, DateRange As Range, AmountRange As Range)
Dim Dates() As Date
Dim DateCount, Counter As Integer
Dim AmountLeft As Long
Counter = 0
AmountLeft = MyAmount.Value
For Each c In AmountRange
AmountLeft = AmountLeft - c.Value
If AmountLeft > 0 Then Counter = Counter + 1
Next
DateCount = 0
For Each c In DateRange
DateCount = DateCount + 1
ReDim Preserve Dates(DateCount)
Dates(DateCount) = c.Value
Next
If Counter + 1 > UBound(Dates()) Then
WhichDay = "Still " & AmountLeft & " Left"
Else
WhichDay = Dates(Counter + 1)
End If
End Function
This is a user defined function and it will appear in the Function list in Excel.
You need to pass it the cell with the Opening Inventory amount. The excel range of dates. Excel range with the daily usage.
It will then return the date that the Product will run out.
Example:
Column A Column B Column C
01/01/01 500 830
02/01/01 100
03/01/01 200
04/01/01 10
05/01/01 20
06/01/01 50
07/01/01 90
type in formula =WhichDay(C1,A1:A7,B1:B7)
this would then return 05/01/01 because that is the day that the stock runs out. (830-500-100-200-10-20=0)
- Thread starter
- #3
Thanks almost what i need. I think i'll get it to work. The still left needs to show the date the product will run out.
Lets say I have 500 cases of a product. On the other sheet I project how many cases i'll uses per day. I must use my inventory within 4 days or it's to old. This sheet is a code date check from my projected sheet. Lets' say the products code date is 5/21/01. today is the 5/10/01 and i have 500 cases. If I project to use 50 per day I want to say the product will last till 5/20/01. Six days to long. I hope i explaned the correct.
Lets say I have 500 cases of a product. On the other sheet I project how many cases i'll uses per day. I must use my inventory within 4 days or it's to old. This sheet is a code date check from my projected sheet. Lets' say the products code date is 5/21/01. today is the 5/10/01 and i have 500 cases. If I project to use 50 per day I want to say the product will last till 5/20/01. Six days to long. I hope i explaned the correct.
- Status