+ Reply to Thread
Results 1 to 3 of 3

Custom Function #Name Problem

  1. #1
    Registered User
    Join Date
    06-16-2005
    Posts
    68

    Custom Function #Name Problem

    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

  2. #2
    Bernie Deitrick
    Guest

    Re: Custom Function #Name Problem

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >




  3. #3
    Registered User
    Join Date
    06-16-2005
    Posts
    68

    Custom Function Code

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

    For testing the inputs are: Inventory - a cell reference with the number of units in inventory; Sales - a string of cells, typically horizontal, that represent monthly sales (must be greater than inventory in total or it errors out); Days - an array the same size as the sales that calculates the days corresponding to the number of 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 remaining 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 days in last month
    FinalStep:
    DOH = cumulative_days + final_month_sales * remainder

    End Function

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1