Some time ago, I created a custom function to calculated the number of
days sales that can be covered by the current inventory balance
(commonly known as Days on Hand). The function essentially figures out
how many months of demand the inventory can cover and then multiplies
this by an array of days in each month. During testing, the formula
worked fine and there were no errors - there also were no errors when
other groups started using it.

Now, one user is putting the function into a massive file with many
other calculations included. Occassionally, the DOH function returns a
#Name error. Unfortunately, it seems to occur haphazzardly - i.e. it
will error out with a calculation, we can try to calculate, resave, etc
to no avail, and then all of a sudden it will work for no reason. I was
wondering if anyone had run into anything like this. The function has
been a huge time saver, but obviously is of little use if it isn't
reliable. TIA - Chad


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=472132

Chad,

Post your code - if it is possible to do the same thing with worksheet functions, then they would be
more reliable.

HTH,
Bernie
MS Excel MVP


"cvolkert" wrote in message
...

Some time ago, I created a custom function to calculated the number of
days sales that can be covered by the current inventory balance
(commonly known as Days on Hand). The function essentially figures out
how many months of demand the inventory can cover and then multiplies
this by an array of days in each month. During testing, the formula
worked fine and there were no errors - there also were no errors when
other groups started using it.

Now, one user is putting the function into a massive file with many
other calculations included. Occassionally, the DOH function returns a
#Name error. Unfortunately, it seems to occur haphazzardly - i.e. it
will error out with a calculation, we can try to calculate, resave, etc
to no avail, and then all of a sudden it will work for no reason. I was
wondering if anyone had run into anything like this. The function has
been a huge time saver, but obviously is of little use if it isn't
reliable. TIA - Chad


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=472132

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