i have a function ("function1") that takes a range as an input:
function1 (a As Range) As Double
i have another function ("function2") that internally creates an array "a" that I need to be the input for function1. I tried, inside function2,:
...
function1(a)
...
but of course (?) it does not work...
I need to rank the top 3 names for each location based on gender (see sample data table below).
My spreadhseet has been setup using dynamic named ranges ("Size", "Name", "Gender", "Location" and "Score") using the formula "Size"=MATCH("zzzzz",'Sheet1'!$A:$A) and then "Name" =OFFSET('Sheet1'!$A$2,0,0,Size), and so on for "Gender", "Location" and "Score".
Is there a way to design an array rank formula using the dynamic named ranges to return the name associated with the highest score for "M"s in location "N"? I want to be able to edit the formula so I can chose which rank to display (e.g. 3rd, 7th, etc).
I've been playing with the following but can't get it to work
{=RANK(IF(ISNUMBER(MATCH(Gener,{"M"},0)),IF(Location{"N"},0),Score)}
NameGenderLocationScoreTimMN89
RebeccaFN45DanaFN85TerryMN15
RachelFN78BruceMN31BryanMS15
DonnaFS80JakeMS67MelissaFS66AdamMS48KateFS90
can anyone modify below macro to sort this data:
before sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-6-5-99-2-1-99-4-3
3-99-7-8-99-5-6-99-8-9
4-99-8-7-99-6-5-99-9-8
etc
after sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-5-6-99-1-2-99-3-4
3-99-7-8-99-5-6-99-8-9
4-99-7-8-99-5-6-99-8-9
etc
Sub SortNoncontiguousRanges()
Dim rRange As Range
Dim lArea As Long
'10 rows in columns B and C
'10 rows in columns E and F
'10 rows in columns H and I
Set rRange = Range("B1:C10,E1:F10,H1:I10")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
.Sort Key1:=.Cells(1, 1), _
Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
End With
Next lArea
End With
End Sub
I am dumping data out of SAP, including the following fields:
* Vendor
* Material
* PO #
* Qty
* Amt
* Date
I would like to concatenate the material and vendor and pull in the PO # for the most recent date.
The VLOOKUP part is easy on the material/vendor, but I haven't had any luck in integrating a MAX, LARGE or RANK function to pull in the PO corresponding to the most recent date.
Once the PO # associated with the most current date can be identified for the material/vendor combination, I will need to utilize another vlookup to match the PO # up against a separate data file to pull in pricing. If anyone can think of a way to accomplish this in one step as opposed to multiple formulas.
I need to use the rank function to get the position of each item in a list based on its corresponding Revenue. However, the range that has the revenue numbers also contains a percentage related to that item. Basically, I need to use RANK based on a list that ignores any elements which are <= 1.
View 9 Replies View RelatedI am trying to rank number that do not have a specific adress. My problem is that I do not know how to define all parameters of this function
View 3 Replies View RelatedMy boss asked me to format a pivot table like this with irregular ranges:
I've had a crack (as seen below and have gotten everything right except the strange group irregular ranges. I'm stumped! I'm not sure if pivot tables even have a function for irregular group ranges.
My raw data looks like here is its layout:
I am trying to break ties in the Rank function.
Example worksheet attached.
I have an eight digit number (a digit can show up more than once) and determine the rank of the sum of each number. I had the ties broken by whatever digit showed up first.
In the following example, June 13th row showed 6 digits tied for second place and the tie was broken by the order in which the digit show up in the range.
On the June 14th row it showed digit 4 and 5 tied with 3 occurrence each (running total for the two days). But digit 4 came in twice that day, and reach a total 3 first before digit 5, but Rank2 shows that digit 5 was ranked first, which should of been digit 4.
I am using the RANK function to rank the data in several rows. There are some times ties in the result.
I want to break that tie by taking the greatest value in a different cell in that row
Greatest value wins the tie.
same situation if there are multiple ties (4 "RANK"'s of position 2) ...
I'm trying to rank the values in row 94 while ignoring only zero values and including positive and negative values. I'd like the function to rank the values in descending order, beginning with the most positive value and ending with the most negative value while ignoring all zeros. The function I've come up with so far ranks the positive values correctly and ignores the zeros, but the negative values are ranked as if they come after all of the zero values instead of being ranked immediately after the smallest positive value. Is there a way to fix the function below to make it do what I want?
=IF(D94=0,"",RANK(D94,$D$94:$AY$94,0))
In the RANK() function I can rank a value depending on one set of values (one condition), but when you SORT data you can choose a second (and a third) condition.
Is there a way to RANK with two sets of values (two conditions)?
I am trying to rank a list of numbers, such as:
1
3
5
3
4
1
I have no problem with the rank function in terms of the ties showing as duplicate values, however, when this occurs I would like a "T-" to appear before ranks that are tied, and show nothing if they are not tied. Essentially, I want the final result to look like this, without having to manually add the "T-" after the ranking is complete.
1 T-5 3 T-3 5 1 3 T-3 4 2 1 T-5
I have done more complex Excel formulas before, but for some reason this is stumping me.
why this returns a #Value! error?
{=RANK(MAX(IF(MOD(ROW(F3:F9),2)=0,F3:F9)),IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1),1)}
I just used the even row selector as an example.
MAX(IF(MOD(ROW(F3:F9),2)=0,F3:F9)) returns 203 and IF(MOD(ROW(F3:F9),2)=0,F3:F9,E1) returns {0;47;0;203;0;"A";0}
when you press F9.
If I change the range to exclude the non-numeric value I get the same error (it shouldn't matter according to the help file as non-numeric values are ignored).
I am a meteorologist and I keep track of my forecaster's monthly statistics. One area I track is percentage of warnings issued with desired lead time. I calculate and rank the individuals by percentages.
Example:
Forecaster A: 3 of 4 75% Rank 1
Forecaster B: 1 of 2 50% Rank 2
Forecaster C: 0 of 3 0% Rank 3
Or if ties:
Forecaster A: 6 of 6 100% Rank 1
Forecaster B: 3 of 3 100% Rank 1
Forecaster C: 1 of 2 50% Rank 3
Is there a way in Excel in case of ties, to award a higher rank (1) to the individual who went 100%, 6 for 6, compared to the individual who also earned 100%, but only 3 of 3.
More reward for more chances and opportunities.
I have 3 columns, A B C: Distribution Center, Sales, and Top Ranked. What I am trying to get is a Rank function in the Top Ranked column that looks for the highest value in the Sales column, but returns the Distribution Center associated with this Sales value instead of the actual value. I could get this done with some helper columns and a VLOOKUP, but the report is rather large and the VLOOKUP not only slows it down but also pushes the file size over the limit. Also, I will be using this for metrics other than Sales where I'll be looking for the highest and lowest figures, so if a Max or Min function works better, that would be fine. I just haven't been able to figure out how to have it return the associated data instead of the ranked data.
View 9 Replies View RelatedI am wanting to create a custom function that i can enter into a cell to run a macro (MyMacro). I do not know how to write a function, but so far i have:
Public Function Run(MacroName As String)
Application.Run MyMacro
End Function
Custom average function. can this be done with Worksheet functions:
View 4 Replies View RelatedThis just a shot in the dark, but does anyone have a custom function that calculates the check digit for a cusip? http://en.wikipedia.org/wiki/CUSIP
The algorithm is listed above.
Imagine i have 2 columns: Open and Close, both of these got numbers like
1 ---- 2
3 ---- 1
4 ---- 10
and so on. I had to make a function which checks if some number is in between any of those Open and Close numbers and count how many, for example: im searching for number 1.3, so according to previously drawn table i would get answer of 2, because 2 is in between 1----2 and 3-----1, i achieved this by a simple function:
=IF(OR(AND(Bendras!$J$1>=Table1[[#This Row],[Open]],(Table1[[#This Row],[Close]]>=Bendras!$J$1)),AND((Bendras!$J$1<=Table1[[#This Row],[Open]]),(Table1[[#This Row],[Close]]<=Bendras!$J$1))),TRUE,FALSE)
this generated an additional column with TRUE and FALSE values which i counted with:
=COUNTIF(Table1[T/F],TRUE)
and got the answer.
so now then preparations are ready i need to make a function which would for example if the number i was searching was in 10th and 45th rows find the MAX/MIN values of Close column between those rows(hope i made my self clear)
this is how i was hoping to do that : first of all make a new array of all cell addresses from "Close" column which were "TRUE" from the first function i wrote and when do w/e i like with those cell addresses in other functions.
My co-worker created a function that would calculate the standard deviation according to whatever range is selected by the user.
I was hoping to add an if statement to the formula but am getting a #value! error.
coworker's function:
Function volatility(r As Range, Optional scl As Double = 252) As Double
Dim lr() As Double
rws = r.Rows.Count
ReDim lr(1 To (rws - 1)) As Double
Dim variance As Double
[code]....
What I'd like to do with the function:
=volatility(IF((YEAR(A8:A1036)>=K3)*(YEAR(A8:A1036)
I have created a custom Function in excel. I would like to create a custom button so I can run this funtion with a Click of a button unstead of typing it in or clicking on the insert function button. I am not sure if this is possible to have VB call a custom function. Below is my VB for the Custom Function. I basically want the custom button to open the formula in excel.
Public Function CreateFlexstring(Company As String, Cost_Center As String, _
Division As String, Geography As String)
CreateFlexstring = Company & "-" & Cost_Center & "-" & Division & "-" & Geography
End Function
I have a column of data with 2 possible values, "H" or "A". The second column contains numbers (unsorted).
How do I write the VBA code to return the Highest number, where the first column is 'H' ?
Eg.
Location Attendance
H 25365
A 17436
A 47252
H 15494
A 37578
H 17549
H 28756
A 59756
This would need to return 28756.
I am very proficient at Excel/VBA and have a question about custom/user-defined functions that may be a little more advanced. I understand how to write custom functions and access them through the user-defined functions menu, but I would like to be able to include my function in an add-in that users could simply add, and then access the function via the 'Fx' box at the top of Excel, like they would any other built-in function, instead of having to go to the user-defined functions menu. I guess what I am looking for is how to add 'built-in' functions and not user-defined ones. I just want the user to start typing '=customFunction(' and have the parameters pop-up in tool-tip form, like any Excel built-in function would.
View 9 Replies View RelatedI have a calendar I have developed that highlights the current date and opens by default to the current month. Each worksheet is a seperate month. The current date highlighting is written with a public macro and the opening to current month is a private macro. The problem comes when this workbook is shared and the other users have to agree to accept my macro because of security. I would like to bypass that requirement and just have the workbook stand alone without the permission.
View 5 Replies View RelatedDoes anyone know why you can't specify an array, like an array of Doubles, as an argument or input to a custom function? For example:
View 14 Replies View RelatedI have a custom function that will tell me which quarter it is based on a date entered into a cell. It works except if the cell is empty is still returns the last Case but not my Case else. If the cell is blank I wanted the function to not return anything.
Function QuarterMonth(InputDate As Date)
Dim MonthNumber As Integer
MonthNumber = Month(InputDate)
Select Case MonthNumber
Case 1
QuarterMonth = "Q1 - 13"
[Code] ...........
Basically i want to use a UDF to count cells in a range of a certain colour.
But i want to make it easy for the user to be able to change the colour the function counts, so i thought i could ask them to colour the cell in which the function is written.
Is there a way to tell the function to pick up the Interior.ColorIndex of the cell it is written in?
Use Application.caller.interior.colorindex
I’m trying to write a custom function that always references the cell above it but I can’t figure out the proper syntax to do so i.e.
Function Multiple_Cell_Above()
Multiple_Cell_Above = cellabove * 10
End function
I am trying to use the AutoFilter/Custom function in Excel (it is available under the Data Menu). It offers me two conditions/criteria that I can apply using and/or. For eg:
Filter:
does not begin with - 3
and / or
does not begin with - 9.
I want to add a third 'and' criteria .. is it possible, and if so, how?
The column that I am trying to filter has numbers formatted as text.
I was using the macro provided by shg in post#6 of this thread: Interpolate Two-dimensional Array? ...
View 6 Replies View Related