UDF to compute using ranges

[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.

[Bob Phillips]

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

[Tom Ogilvy]

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

[Tom Ogilvy]

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

[nougain]

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

[Bob Phillips]

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

[nougain]

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

Thanks



[QUOTE=Bob Phillips]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)

[Bob Phillips]

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

[nougain]

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

[Tom Ogilvy]

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

[Mark Bower]

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