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.
I am currently tracking online PPC keyword reports with Excel and need to know if there is a function that will find and match words and phrases and then add the columns that are queried for the matching words/phrases. I think an example is definately in order.
December PPC Keyword Clicks Cost Revenue large dogs 45 .18 $12.00 small dogs 35 .25 $15.00
January PPC Keyword Clicks Cost Revenue large dogs 12 .14 $8.25 small dogs 18 .18 $5.35
Now using the example above I need a function or maybe even a macro that will scan all "keywords" and find a match for each keyword each month, say large dogs, and then add the clicks, costs, and revenue columns and post them on a particular row or rows.
So when running the function it will find and match "large dogs" for each month, it will then add the clicks for all months with "large dogs" in it and then populate a column or row with the total along with the keyword "large dogs" next to it. Is there a function that will do this or maybe even a macro?
I have a PT with multiple data fields that I want to Rank individually and then give those ranks a weight percentage. Once I have that weighted rank, then I can sort PT items based on the rank.
Is there a way to do this inside the PT?
I.E.
Items are PNs
Data fields are NUMBER OF TRANSACTIONS, QTY SOLD, DOLLARS SOLD
I want to rank each field and then assign a weight to it. Then I can sort PT based on the RANK data field.
I have a PT with multiple data fields that I want to Rank individually and then give those ranks a weight percentage. Once I have that weighted rank, then I can sort PT items based on the rank .....
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.
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'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?
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)?
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.
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.
I'm attempting to create a formula that will find the name associated with a value, and return that name on the same column as that value in a later equation.
OK OK to illustrate it a little better:
There are three people: Bill, Ted, and Andy. Each one is ranked in Points, so my table looks like this:
A B
1 Bill 10 2 Ted 20 3 Andy 30
Later on, I want to rank the individuals based on their score, using a formula. Right now, I can rank the scores based using LARGE(B1:B3,1), then LARGE(B1:B3,2), and lastly LARGE(B1:B3,3). That ranks the numbers in descending columns. However, I want the information to automatically populate the name associated with that particular point total. So, I want the system to know that B1 is Bill's score, and rank it, in descending order, later on in the spreadsheet, with Bill's name.
I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.
So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15. Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.
How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).
As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.
The answer I need is 2 because, discounting the zero value, 126 is the second highest value.
I have some text I wish to add some content to the front and back of it. i.e. the text in the cell is "214.212.134.62" and I want to add "http://" to the front of it and ":9999" to the back to it, so it looks like "http://214.212.134.62:9999" in a different cell.
I have a column which has either EDC or EDT in it, can I add a function to this column which says 'if EDC then output Eau De Toilette' and then Eau De Toilette get's ouputted into a seperate column? Is this kind of thing even possible in Excel?
that I want to look up the Day (mon, tue, etc) and then return the date entered in cell A1
So if the date in cell A1 is 01/05/08, the formula should return Thu01/05/08. Currently it returns Thu39569, even though the cell is formatted as a date format... How do I get it to return the date in a date format?
I have a bit of code someone on this board provided and I want to make it work for two different ranges. If I just paste it, I get an ambiguous name error. How do I make it work for a second range?
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rng As Range Set rng = [A2:A101] If Intersect(Target, rng) Is Nothing Then Exit Sub If IsEmpty(Target) Then Target.Offset(0, 4).Value = "" Target.Offset(0, 1).Value = "" Else Target.Offset(0, 4).Value = Application.UserName Target.Offset(0, 1).Value = Date End If End Sub
I would like to edit this function to also count profit only if Cell A15:A8998 in my Sold sheet has the words "Shipped". The text value Shipped is not a text value I entered, it is automatically written based on a Vlookup formula I created.
