Thanks for the suggestions. Based on clues I received, I am buildin
following UDF that I indend to call in my sheet. It doesn't compile.
am pretty new to VBA and don't know what is wrong. Please treat thi
code as Pseudo code to know what I indend to achieve. There could b
easlier way that I don't know. Additionally, I am not sure if it wil
be efficient from performance perspective as I will be calling thi
function for many dates in a column.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
' ss: Schedule Slippage
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
Function ss_weekly(endDate As Date)
' Data range
Dim dateRange As range
Dim dataRange As range
dateRange = CSR!$O$20:$O$351 'CSR is sheet name
dataRange = CSR!$AA$20:$AA$351
' Local variables to compute the average
Dim sumDataForAllDatesLessThanGivenDate As Double
Dim sumDataForAllDatesLessThanOneWeekBack As Double
Dim countDataForAllDatesLessThanGivenDate As Integer
Dim countDataForAllDatesLessThanOneWeekBack As Integer
Dim sumData As Double
Dim countData As Integer
' Input endDate is expected to be a Friday date. Therefor adding
days to get the Sunday and
' subtracting 4 to get the last Monday
sumDataForAllDatesLessThanGivenDate = SUMIF(dateRange, "<="
(endDate + 2), dataRange)
sumDataForAllDatesLessThanOneWeekBack = SUMIF(dateRange, "<"
(endDate - 4), dataRange)
countDataForAllDatesLessThanGivenDate = CountIf(dateRange, "<="
(endDate + 2))
countDataForAllDatesLessThanOneWeekBack = CountIf(dateRange, "<"
(endDate - 4))
' Slippage
sumData = sumDataForAllDatesLessThanGivenDate
sumDataForAllDatesLessThanOneWeekBack
countData = countDataForAllDatesLessThanGivenDate
countDataForAllDatesLessThanOneWeekBack
If countData <= 0 Then
ss_weekly = 0
Else
ss_weekly = sumData / countData
End If
End Functio
--
nougai
-----------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...fo&userid=3203
View this thread:
http://www.excelforum.com/showthread.php?threadid=51782