UDF to compute using ranges

  • Thread starter Thread starter nougain
  • Start date Start date
N

nougain

I have two sheets in my workbook. In sheet 1, Column A (say sellingDate)
has dates and column B (say apples) has some numbers. In sheet 2 I have
column A (say givenFridayDate) where I have dates and column B
(calAverage) where I need to calculate some valve using the UDF.

What I need to do is to have some function that takes givenFridayDate
value and return average of apples sold for sellingDate less than equal
to givenFridayDate and greater than the date that falls 5 days back from
givenFridayDate (i.e. Last sunday).

I don't know how to use Ranges and filter the data that falls within
given dates. Please suggest some solution. Thanks.
 
You can do this with a formula

=AVERAGE(IF((Sheet1!A1:A20<=Sheet2!A1)*(Sheet1!A1:A20>=Sheet2!A1-5),Sheet1!B
1:B20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
=sumif(Sheet1!A:A,"<=Feb 24, 2006",Sheet1!B:B)-sumif(Sheet1!A:A,"<Feb 19,
2006",Sheet1!B:B)
gives the sum

for an average
=(sumif(Sheet1!A:A,"<=Feb 24, 2006",Sheet1!B:B)-sumif(Sheet1!A:A,"<Feb 19,
2006",Sheet1!B:B))/5

adjust the dates to suit
 
to use the value in A1 for a date

=sumif(Sheet1!A:A,"<="&A1,Sheet1!B:B)-sumif(Sheet1!A:A,"<"&A1,Sheet1!B:B)
gives the sum

for an average
=(sumif(Sheet1!A:A,"<"&A1,Sheet1!B:B)-sumif(Sheet1!A:A,"<"&A1,Sheet1!B:B))/5

I would recommend this over an array formula or sumproduct (which is also an
array formula) if you will be using many such formulas as it will be much
faster to recalculate.

--
Regards,
Tom Ogilvy




Tom Ogilvy said:
=sumif(Sheet1!A:A,"<=Feb 24, 2006",Sheet1!B:B)-sumif(Sheet1!A:A,"<Feb 19,
2006",Sheet1!B:B)
gives the sum

for an average
=(sumif(Sheet1!A:A,"<=Feb 24, 2006",Sheet1!B:B)-sumif(Sheet1!A:A,"<Feb 19,
2006",Sheet1!B:B))/5

adjust the dates to suit

--
Regards,
Tom Ogilvy
Click to expand...
 
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
 
You don't say what doesn't work and then give us pseudo-code, but not that
SUMOF is a worksheet function, to use in VBA try

Application.SUMIF(dateRange, "<=" & (endDate + 2), dataRange)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
I am getting compilation error at "dateRange = CSR!$O$20:$O$351" line
It gives error for $ use in the statement.

Thanks
 
In real code you use

Set dateRange = Range("CSR!$O$20:$O$351")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Compilation error disappear, but when I use ss_weekly function it shows
#NAME? in my sheet's cell. I don't know where I should focus to correct
it. Here is the updated VBA code:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ss: Schedule Slippage
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function ss_weekly(endDate As Date) As Double
' Data range
Dim dateRange As range
Dim dataRange As range

'CSR is sheet name
Set dateRange = Sheets("CSR").range("O20:O351")
Set dataRange = Sheets("CSR").range("AA20:AA351")

' 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 2
days to get the Sunday and
' subtracting 4 to get the last Monday
sumDataForAllDatesLessThanGivenDate = Application.SUMIF(dateRange,
"<=" & (endDate + 2), dataRange) 'On Sunday
sumDataForAllDatesLessThanOneWeekBack =
Application.SUMIF(dateRange, "<" & (endDate - 4), dataRange) 'On
just passed Monday
countDataForAllDatesLessThanGivenDate =
Application.CountIf(dateRange, "<=" & (endDate + 2))
countDataForAllDatesLessThanOneWeekBack =
Application.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 Function
 
Make sure the code is in a general module, not a sheet module, the
thisworkbook module or a userform module. in the VBE, Insert=>Module. Put
it in that.
 
I am having similar problems as described in the original question. I am
trying to create a UDF that runs on one worksheet and internal to the UDF it
collects values from another worksheet. I can make it work when everything
is on one worksheet. But, as soon as I introduce a reference to another
worksheet, the UDF has no result and returns #Value.

For example: I have:

Public Function MyFunction(iAIndex as Integer, iBIndex as Integer) as Variant

Dim a
Dim b

Set a = ThisWorkbook.Worksheets("MyFirstSheet").Range("TheARange") '
TheARange is a 1xN range
Set b = ThisWorkbook.Worksheets("MyReferenceSheet").Range("TheBRange") '
TheBRange is a 1xM range

MyFunction = TheARange(1, iAIndex) * TheBRange(1,iBIndex)

End Function

Basically, what I have been able to figure out is that as long as the
function is calling a value from the worksheet where it is used, this works.
But, if it tries to touch another worksheet it is broke. Is this a
limitation on the UDF method or am I missing something?

Your help is greatly appreciated.

Best regards,
Mark Bower

Tom Ogilvy said:
Make sure the code is in a general module, not a sheet module, the
thisworkbook module or a userform module. in the VBE, Insert=>Module. Put
it in that.
Click to expand...
 
Back
Top