UDF to compute using ranges
 

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.


--
nougain
------------------------------------------------------------------------
nougain's Profile: http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=517826

UDF to compute using ranges
 

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)

"nougain" wrote in
message ...

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.


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=517826

UDF to compute using ranges
 

=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





"nougain" wrote in
message ...

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.


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=517826

UDF to compute using ranges
 

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" wrote in message
...
=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





"nougain" wrote in
message ...

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.


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread:
http://www.excelforum.com/showthread...hreadid=517826

UDF to compute using ranges
 

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

UDF to compute using ranges
 

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)

"nougain" wrote in
message ...

Thanks for the suggestions. Based on clues I received, I am building
following UDF that I indend to call in my sheet. It doesn't compile. I
am pretty new to VBA and don't know what is wrong. Please treat this
code as Pseudo code to know what I indend to achieve. There could be
easlier way that I don't know. Additionally, I am not sure if it will
be efficient from performance perspective as I will be calling this
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 2
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 Function


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=517826

UDF to compute using ranges
 

I am getting compilation error at "dateRange = CSR!$O$20:$O$351" line
It gives error for $ use in the statement.

Thanks



Bob Phillips Wrote:
You don't say what doesn't work and then give us pseudo-code, but no
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

--
nougai
-----------------------------------------------------------------------
nougain's Profile: http://www.excelforum.com/member.php...fo&userid=3203
View this thread: http://www.excelforum.com/showthread.php?threadid=51782

UDF to compute using ranges
 

In real code you use

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nougain" wrote in
message ...

I am getting compilation error at "dateRange = CSR!$O$20:$O$351" line.
It gives error for $ use in the statement.

Thanks



Bob Phillips Wrote:
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)


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=517826

UDF to compute using ranges
 

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


--
nougain
------------------------------------------------------------------------
nougain's Profile: http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=517826

UDF to compute using ranges
 

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.

--
Regards,
Tom Ogilvy

"nougain" wrote in
message ...

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


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=517826

UDF to compute using ranges
 

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("The ARange") '
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" wrote:

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.

--
Regards,
Tom Ogilvy

"nougain" wrote in
message ...

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


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=517826