Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is there such worksheet functions...
Hello all,
There are two things I'm trying to do. First, is there a function that will return (to the cell the function is in) the address of the maximum value in a range? I have a chart based on months over several years. I have the last several rows of the chart data left blank (for future input), but my chart includes these blank months in it. I'm hoping to dynamically set the range of the chart's source data based on the address that appears in a cell, so the chart will only show the months that have data in them. Second, is there a function that will extract unique values from a list? Like Auto Filter does: When you turn Auto Filter on and click the drop down in one of the columns, there are only unique values in the drop down list. If there is a way to create worksheet functions in VBA and then use them in the cells of the worksheets, please post sample code. Thanks for any help anyone can provide, Conan Kelly |
#2
|
|||
|
|||
For the first part, enter the following UDF:
Function whereis(r As Range, v As Variant) As String Dim rr As Range For Each rr In r If rr.Value = v Then whereis = rr.Address End If Next End Function and use it like =whereis(A:A,MAX(A:A)) where A:A can be replaced by your range. -- Gary's Student "Conan Kelly" wrote: Hello all, There are two things I'm trying to do. First, is there a function that will return (to the cell the function is in) the address of the maximum value in a range? I have a chart based on months over several years. I have the last several rows of the chart data left blank (for future input), but my chart includes these blank months in it. I'm hoping to dynamically set the range of the chart's source data based on the address that appears in a cell, so the chart will only show the months that have data in them. Second, is there a function that will extract unique values from a list? Like Auto Filter does: When you turn Auto Filter on and click the drop down in one of the columns, there are only unique values in the drop down list. If there is a way to create worksheet functions in VBA and then use them in the cells of the worksheets, please post sample code. Thanks for any help anyone can provide, Conan Kelly |
#3
|
|||
|
|||
On Thu, 13 Oct 2005 10:43:43 -0700, "Conan Kelly" <CTBarbarin at msn dot com
wrote: Hello all, There are two things I'm trying to do. First, is there a function that will return (to the cell the function is in) the address of the maximum value in a range? I have a chart based on months over several years. I have the last several rows of the chart data left blank (for future input), but my chart includes these blank months in it. I'm hoping to dynamically set the range of the chart's source data based on the address that appears in a cell, so the chart will only show the months that have data in them. Second, is there a function that will extract unique values from a list? Like Auto Filter does: When you turn Auto Filter on and click the drop down in one of the columns, there are only unique values in the drop down list. If there is a way to create worksheet functions in VBA and then use them in the cells of the worksheets, please post sample code. Thanks for any help anyone can provide, Conan Kelly You can probably use some variation of the MATCH, MAX and ADDRESS or INDEX functions depending on how your data is set up. Here are some examples: Find address of maximum number in row 2: =ADDRESS(2,MATCH(MAX(2:2),2:2,0)) Find address of maximum number in Column B =ADDRESS(MATCH(MAX(B:B),B:B,0),2) With data in B2:B65535 and Dates in A2:A65535, return the date in column A that corresponds with the last entry in column B: If there are no blanks in the data: =INDEX(A:A,MATCH(TRUE,ISBLANK(B2:B65535),0)) entered as an array formula (hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula). If there may be blanks in the data, then try: =INDEX(A:A,MAX(ISNUMBER(B2:B65535)*ROW(INDIRECT("2 :65535")))) also entered as an array formula. --ron |
#4
|
|||
|
|||
On Thu, 13 Oct 2005 10:43:43 -0700, "Conan Kelly" <CTBarbarin at msn dot com
wrote: Hello all, There are two things I'm trying to do. First, is there a function that will return (to the cell the function is in) the address of the maximum value in a range? I have a chart based on months over several years. I have the last several rows of the chart data left blank (for future input), but my chart includes these blank months in it. I'm hoping to dynamically set the range of the chart's source data based on the address that appears in a cell, so the chart will only show the months that have data in them. Second, is there a function that will extract unique values from a list? Like Auto Filter does: When you turn Auto Filter on and click the drop down in one of the columns, there are only unique values in the drop down list. If there is a way to create worksheet functions in VBA and then use them in the cells of the worksheets, please post sample code. Thanks for any help anyone can provide, Conan Kelly I forgot about your second problem. For that I would download the Laurent Longre's free morefunc.xll from http://xcell05.free.fr/ and use the UNIQUEVALUES function. It's explained in HELP once you download and install the add-in. --ron |
#5
|
|||
|
|||
Gary''s Student wrote...
For the first part, enter the following UDF: Function whereis(r As Range, v As Variant) As String Dim rr As Range For Each rr In r If rr.Value = v Then whereis = rr.Address End If Next End Function and use it like =whereis(A:A,MAX(A:A)) where A:A can be replaced by your range. Using a different syntatic form than existing functions, e.g., FIND and MATCH, in which the value sought comes first followed by the range or array in which to search, is at best questionable. Also, your udf returns the last matching value in the range rather than the first when there are multiple entries. Maybe that's OK, maybe not, but you should have mentioned that. But using a udf for something that could be done by a few built-in functions is a bad idea. In order to use udfs, users may need to change macro security settings, and udfs are SLOW. The address of the topmost MAX value is given by =CELL("Address",INDEX(A:A,MATCH(MAX(A:A),A:A,0)) 4 built-in functions are guaranteed to recalc faster than a udf and a built-in function call. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get the complete list Excel Worksheet Functions | Excel Worksheet Functions | |||
Worksheet functions | New Users to Excel | |||
Worksheet Memory hog | Excel Discussion (Misc queries) | |||
Worksheet functions - Ajit11021225 | Excel Discussion (Misc queries) | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions |