Adding Text To Rank Function

Aug 12, 2008

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.

View 9 Replies


ADVERTISEMENT

Function For Recognizing Matching Text And Adding Up Columns

Jan 8, 2007

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?

View 10 Replies View Related

Adding Pivot Table Field With RANK

Dec 17, 2008

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.

View 9 Replies View Related

Adding A Pivot Table Field With RANK

Dec 17, 2008

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

View 9 Replies View Related

VLOOKUP W/ RANK Or MAX Function

Jul 26, 2007

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.

View 11 Replies View Related

Rank Function With Condition

Apr 25, 2006

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 Related

Using Rank Worsheet Function In VBA

Aug 22, 2006

I 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 Related

Breaking Ties In Rank Function

Jun 21, 2014

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.

View 14 Replies View Related

How To Break Ties In The RANK Function

Dec 8, 2008

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

View 7 Replies View Related

Rank Function Ignoring Only Zero Values

Sep 12, 2013

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))

View 9 Replies View Related

RANK With Several Conditions (like For The SORT Function)

Feb 5, 2007

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)?

View 9 Replies View Related

Rank Function With Array Inputs

Mar 10, 2009

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

View 9 Replies View Related

Rank Function And Percentage Ties

Jun 23, 2009

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.

View 9 Replies View Related

Rank Function In The Top Ranked Column

Jul 23, 2009

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 Related

Custom Function To Rank Two Ranges

Oct 4, 2007

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

View 9 Replies View Related

How To Rank Based On Text In Another Cell

Dec 12, 2013

how to rank values in one column based on criteria in another column?

For example: How do I rank Cells in Column A that only contain the text "AL" in column B?

COL...A....B
ROW
1......5....NL
2......6....AL
3......7....AL
4......8....NL

View 5 Replies View Related

Formula To Rank Text Objects Based On Numbers, Interesting Read

Dec 30, 2008

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.

View 9 Replies View Related

Conditional Rank Based On Whether A Criteria Cell Includes A Specific Text String

May 8, 2009

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.

View 9 Replies View Related

How To Combine Text Values Of Formulas In Addition To Adding Text

Jun 15, 2013

I am trying to combine the text values of several formulas in addition to adding some text (punctuation) in between.

Here is the data that I am trying to combine

C14: 2013
C15: Period 6
C16: Week 1

In Cell C13, I would like to have this value returned: "2013 - Period 6, Week 1"

Is there such a way to do this?

View 9 Replies View Related

Adding Text To The Start And End Of A Cells Text In Another Cell?

Nov 28, 2013

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.

View 3 Replies View Related

Adding A Function On A Column

Aug 14, 2007

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?

View 14 Replies View Related

Adding Day In Date Function

Oct 27, 2008

I have the following formula in a cell

=LOOKUP(WEEKDAY(A1),D2:D8,C2:C8)&A1

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?

View 9 Replies View Related

Adding More Than One Worksheet_Change Function

Feb 1, 2007

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

View 9 Replies View Related

Adding Time Function

Dec 9, 2008

I want to add two times together.

14:00 (time) + 03:42 (duration) = 17:42

Is there vba code to be able to do this or an excel function?
I have tried searching but strangely couldnt find anything?

View 2 Replies View Related

Rank A List Then Re-rank The List While Excluding Certain (or By Criteria) Items

Dec 30, 2013

I'm trying to Rank a list and than re-rank the list while excluding certain (or by Criteria) items

Vendor Co
Cost Fee
Rank

Vertox
500
4

BV
1520
3

[code].....

View 4 Replies View Related

Adding Another Condition To Sumif Function?

Jan 8, 2014

Below is my function.

="Profit $"&SUM(IF(Sold!D15:D8998<>"",IF(MONTH(Sold!D15:D8998)=A4,Sold!H15:H8998)))

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.

View 2 Replies View Related

Adding Third Criteria To Sumproduct Function

Feb 5, 2014

=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480)*(MONTH(Sold!$M$15:$M$8998)=12)),"")

I would like to edit the function above and add a third criteria. If Sold!S:S,"Returns" to my sumproduct.

I tried to edit it myself with the function below but it didn't work.

=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480,Sold!S:S,"Returns")*(MONTH(Sold!$M$15:$M$8998)=12)),"")

View 3 Replies View Related

Adding Space After Period In If Function?

Apr 24, 2012

I have the following IF function in a spreadsheet. =IF(AE2 = "AZ", "We've recently changed our name from Company A to Company B.", "")

How do you Make it so there is a Space at the end of the period?

View 3 Replies View Related

Extending Sum Function When Adding Rows

Dec 7, 2006

If I have a sum function that adds up:

= SUM(U41:U45)

but I add a row at 41 I now get sum function

=SUM(S42:S46) when I really want it to incorporate the just add row to look like this:

=SUM(U41:U46)

View 3 Replies View Related

Adding CC Address And Body To SendMail Function?

Mar 19, 2012

I have the below code of which I would like to addresses added to the CC list Also, Would it possible to add a range as the body? E.G:

.CCaddress = "somebody@mail.com"
.CCaddress ="somebody.else@mail.com"

Body text = Range("A1"& "" &"B1")

Code that need the above inputted:

With ActiveWorkbook
.SendMail Recipients:=Array("steve.howard@kuehne-nagel.com"), _
Subject:="610 Cambridge " & Format(Date, "yyyymmdd") & " " & "Counts"
[add cc address + body]
.Close SaveChanges:=False
End With

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved