I have created a function that tells me how much of project's cost is
incurred in a particular year. So, all I need to do is enter the cost
of the project (Value), StartDate, EndDate and the Year, and, I get
the cost in that year as my output. If I want to see how the cost is
spread over several years, I create a row (e.g., 2004, 2005....2020)
and use the function to show me how the cost is spread over multiple
years.

The problem I have is in some of my models, the row of years
(2004-2020) is a named ranged called "Year". When I use the named
range in this formula, I get a #Value error. I need to have the
flexibility to use one number (e.g., 2005) or a named range. How can
I get rid of this #Value error. The actual function is shown below:


Function Spread(Value As Double, StartDate As Double, EndDate As
Double, Year)
'
'Takes a value (expense or income) and returns the amount incurred
'in a particular Year given the StartDate and EndDate (entered in
fractional
'terms e.g., 2010.50).

Select Case Year
'If Year is less than Start Year then enter 0
Case Is < Application.WorksheetFunction.Floor(StartDate, 1): Spread =
0
'If Year is greater than End Year then enter 0
Case Is Application.WorksheetFunction.Floor(EndDate, 1): Spread = 0
'If Year equals StartDate; and, StartDate and EndDate are both in the
same year then allocate Value to that year
Case Is = Application.WorksheetFunction.Floor(StartDate, 1) And
Application.WorksheetFunction.Floor(StartDate, 1) =
Application.WorksheetFunction.Floor(EndDate, 1):
Spread = Value
'If this is anything other than the first year, then take the minimum
of one (one full year) or EndDate - Year (fraction at the end) and
multiply it time the incremental Value
Case Is = Application.WorksheetFunction.Ceiling(StartDate, 1): Spread
= (Value / (EndDate - StartDate)) *
Application.WorksheetFunction.Min((EndDate - Year), 1)
'If this is the first year, then calculate the fraction of the year
times the incremental Value
Case Is = Application.WorksheetFunction.Floor(StartDate, 1): Spread =
(Application.WorksheetFunction.Ceiling(StartDate, 1) - StartDate) *
(Value / (EndDate - StartDate))
End Select
End Function

Thank you in advance