Here is the code for my days on hand function. As I said earlier, i
works 90% of the time, but will randomly error out with #Name and the
later come through correctly although none of the data has changed. T
my knowledge, this is not possible with built in Excel functions. Th
closest I came was with the NPER function, however this function ca
only accept a constant sales stream (was really meant for loa
repayments). If someone has a built-in solution I'm open to it.
think that the #Name error is a result of a large file size that slow
the calculation and somehow causes the error.

For testing the inputs a Inventory - a cell reference with th
number of units in inventory; Sales - a string of cells, typicall
horizontal, that represent monthly sales (must be greater tha
inventory in total or it errors out); Days - an array the same size a
the sales that calculates the days corresponding to the number o
months. Thanks for any help.


Function DOH(Inventory, Sales, Days)

If Inventory < 0 Then
DOH = "Neg Inv"
Exit Function
End If

'Initialize variables
cumulative_sales = 0
cumulative_days = 0
month_count = 0
final_month_count = 0

Set TempSalesRange = Intersect(Sales.Parent.UsedRange, Sales)
Set TempDaysRange = Intersect(Days.Parent.UsedRange, Days)

'Cycle through SalesRange until running total exceeds inventory
For Each cell In TempSalesRange
If cumulative_sales + cell.Value < Inventory Then
cumulative_sales = cumulative_sales + cell.Value
month_count = month_count + 1
Else
next_month_sales = cell.Value
GoTo NextStep
End If
Next cell

'Determine percentage of next month shipments to consumer remainin
inventory
NextStep:
remainder = (Inventory - cumulative_sales) / next_month_sales

'Total up the number of days based on the number of months from above
For Each cell In TempDaysRange
If final_month_count < month_count Then
cumulative_days = cumulative_days + cell.Value
final_month_count = final_month_count + 1
Else
final_month_sales = cell.Value
GoTo FinalStep
End If
Next cell

'Find the total DOH by adding days in full months times ratio of day
in last month
FinalStep:
DOH = cumulative_days + final_month_sales * remainder

End Functio

--
cvolker
-----------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...fo&userid=2438
View this thread: http://www.excelforum.com/showthread.php?threadid=47213