Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare ranges - compute agreement % | Excel Discussion (Misc queries) | |||
formulas will not compute | Excel Worksheet Functions | |||
Calculations don't compute... | Excel Discussion (Misc queries) | |||
How to compute overtime pay | New Users to Excel | |||
How do I compute ratios? | Excel Discussion (Misc queries) |