I have a column of events,there are 7000 in my sheet.The scores for each contestant in the event are in col.2. In col 4 I have the rank printed for each contestant in each event in ascending order.I want to know how to print them in opposite order i.e highest in each event is ranked 1 etc into column 5.
View 9 RepliesRanking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.
Example:
20
18
18
15
12
11
11
9
The ranking I am looking for is:
1
2
2
3
4
5
5
6
I am facing some problem with ranking formulae. Attached is a file illustrating them.
Problem 1: Ranking without zero I have a set of data for products. They have a number next to it. Some of the products have a ZERO value next to them. I want to rank the data in an ascending order while ignoring the products with ZERO value next to them.
Problem 2: Ranking on multiple factors I have a set of data for 50 products. There are 5 factors based on which I want to pick the best and the worst. Each factor has a value which can be a negative number, zero or positive number.
This is what I am doing presently: Ranking the products on each factor in a separate column. Points are assigned based on the ranking, i.e. if the ranking of "product A" on Factor 1 is 32, then it gets 32 points It is assumed that equal importance [weight] is given to each factor, thus average points is calculated based on ranking of each product on each factor. Average points are again ranked in descending order The top & bottom 10 are picked from this ranking.
Number of products and number of factors can be different for each analysis. Presently I am giving equal importance to the rankings for each of the factors, what if I have to give varied importance [weight] to the factors.
I am trying to write a formula to reverse data in a cell. Basically I am converting a number to hex then I want to take that hex string and reverse it. So it would be something like this my original number is 400001001 my hex number would be 17D787E9 because I am only allowing it to show 8 characters. I want to reverse the 17D787E9 to read 9E787D71.
My question is: How can I reverse that cell? I have searched google and this forum and can't seem to figure it out. I am sure I could do it in VB but I don't know any VB code.
I've written a list and I want it the other way around. Top at the bottom, bottom at the top.
There's no sortable column in it. If it gets sorted it'll be out of order. I just want the same order but reverse the whole thing.
I've looked around a lot and failed to find it so far.
I've got to do is add a column at the side, number it in order then sort on that column, remove it later, right?
I need a formula that would search a string in a cell for spaces starting from the right. similar to MID but with the start and end pos reversed.
eg. col A below stores the text. col B to D is where i need to write the formulaes and drop down for the number of rows in A (in this case 3)
A
33 444 555
444 55 666
666 777 98 88
here is what the result in cols B C and D should be after the formula and drop down:
B
555
666
88..................
I'm fairly proficient in Excel, but this one has me stumped (I have attached a spreadsheet to show what I'm talking about): For example, take these two rows of data:
55 , 105 , 101
4 , 3 , 1
The first row represents the production level in years 1, 2, and 3 of production of the life of a unit. The second row represents the number of units added in a particular year. So, if you are in the third year, the one unit added that year will produce 55 widgets, the three units added the previous year will each produce 105 widgets, and the four units added two years ago will each produce 101 widgets.
So: 1*55 + 3*105 + 4*101. The result for this formula would go underneath the "1" (ie its the total production in year 3, from all those units added in years 1-3).
Then when we roll through to the fourth year, where 2 units are added (and where we have the extra info that in the fourth year of production, a unit produces 50 widgets) it looks like this:
55 , 105 , 101, 50
4 , 3 , 1, 2
2*55 + 1*105 + 3*101 + 4*50.
The result for this formula would go underneath the "2" (ie its the total production in year 4, from all those units added in years 1-4). I need to produce a single formula which I can drag across the row. Without having a single formula, I need to copy the formula across to the cell to the right (with "$" in the right places), then add a new element to it. Seems very inefficient (take a look at my attachment)
i have to reverse a very big wordlist containing four coulmns. Column A words, Column B Transcription, Column C Grammar and Column D Meanings. Now i would like to make the meanigs (seperator is ";") to words and words to meaning in another new worksheet added by a macro. For example English-French would then become French-English wordlist.
If the original worksheets name is "x" then a new worksheet should be added with name "Re-x". This new sheet should then contain the new wordlist. For example:
I know how to do two-way lookup with the INDEX and MATCH functions (example, =INDEX(B2:H11,MATCH(A6,A2:A11,0),MATCH(E1,B1:H1,0)) to lookup a value in a table given a value in the row range and a value in the column range. I also know how to use the space intersector to do a two-way lookup (example, =A6:H6 E1:E11).
My question is, is it possible to do the reverse. Instead of finding the row and column positional and then returning the value at the intersection of the two, is it possible to have the value at the intersection and then find the two values in the row and column positions? So this would mean somehow returning two values in two cells.
I have a column, call it E.
I have table, call it Cities. It has a column of, um, cities, but entered in various ways; and a column of regions:
South Cumberland SW
So. Cumberland SW
So. Cumb. SW
Cumberland SW
Galveston S
Rangeley NE
Galv S
If any one of the entries in Column 1 is contained in column E, I want my formula to return the appropriate region code for each row of E.
Note that E8 may or may not be an exact match for the table entry - it may have additional text before and/or after the match text. It may also not appear in the table at all.
I could contruct a whole sequence of "Find" formulae, but I'm thinking there has to be a better way. Also, if I find I need to add another city permutation, it's way easier - & less error-prone - to just extend the table than to create a whole new "Find" calculation column, and then add that column into the calculation that extracts the region.
I need to reverse concatenate a column of addresses, but text to columns won't work. I'd like to have a formula that takes into account each of the following scenarios (basically any standard address you can think of):
102 Bart St
104 Homer Simpson Ave
106 US HWY BSN 805 W
108 N Springfield Rd
What I need is to have the result in four columns. The first field would the house number. The second column would be the prefix (direction) of the street name, IF PRESENT (so the first three examples would have no value in the second column, but the fourth one would have an "N". The FOURTH column would have the suffix, whether that is a street type (like Rd or Ave), or a post-directional like in the third example ("W"). The THIRD column would have everything else (whatever is between the prefix and the suffix). In other words, using the examples above (* indicating a new column):
102**Bart*St
104**Homer Simpson*Ave
106**US HWY BSN 805*W
108*N*Springfield*Rd
I have a cell (a1) that contains "Kent; Andrew George" and I want a formula to change this to "Andrew George Kent" (b1)
a way in which to split the following line into say 5 separate pieces
George Julius Aaronson (81) 12th Feb 1945 D 9th Jan 1948.jpg
George Julius Aaronson
(81)
12th Feb 1945
D
9th Jan 1948
so that they are in 5 separate cells on a row. just to make things harder no name in the first colum has the same number of forenames or the same number of digits.there are possibly 600+ of these lines of which the next one is below
(Hanna Auerbach (73) 2nd Oct 1949 D 13th Feb 1955.jpg)
I am trying to find a way to use the match function to return the last matching value in an unsorted row. For example if I have (with each individual number representing a new column):
A: 4 5 4 3 6 7 5 4 5 0 2 6 3
B: 5
I would like to get a formula similar to this one =match(B1,A1:13,FALSE) to return 9 instead of 2 like it does now.
I dont know if that is the correct title to use but here goes. I am trying to help my friend with some work that he is struggling with.
We have a model where we can change the % of the Service Level in field E8 and it will tell us the number of people required within field E17. Is their a way we can reverse this by creating another spreadsheet where we could put in the number of people we have for it to tell us the service level that would reach?
how to reverse the entries in a 3D chart. I want to have the blue ones appearing before the red ones visually, not the other way around?
View 1 Replies View RelatedOkay, say I have a spreadsheet like this:
Cell1 Cell2 Cell3
Cell4 Cell5 Cell6
How do I make it:
Cell6 Cell5 Cell4
Cell3 Cell2 Cell1
I have a list like this
Dog
Elephant
Hippo
Cat
Morris Minor
Yak
Jaguar
+ about 300 others
I need to copy these and then paste then in another column in the reverse order. Is this possible ?
I have a list of search words in column A and in column B is the products that address that particular search word and the products are separated by a comma in column B. Each product is listed multiple times under various search words.
What I am hoping to do is reverse this so that the product is listed in column A and the related search words are listed under that product in column B.
Excel has a DATA - IMPORT EXTERNAL DATA - NEW DATABASE QUERY Function built into the toolbar. I need to lookup a table in access **TableTest**. Find the Record with a **Door_Number** Equal to an input I change everytime. Then I need to lookup a table in access **TableTest2**.
Find a Record with a **Door_Number** Equal to an input I change everytime And then paste the records in a line going DOWNWARDS not Right to Left. To summarise. Hit button, Type Key1/2 input, find record(s) paste into excel. Im afraid providing a sample is gonna be a little hard on this one, my files are HUGE
I am half way through my VBA for beginners book and did one Excel VBA tutorial lesson online. I am trying to learn by reverse engineering the 2 written functioning programs I have now. Today am posting the 2nd program
View 14 Replies View RelatedIs it possible to have a formula for cells B1 to B10 in order to reverse the order of cell A1:A10 as you can see below.
A
B
1
BO
RENT
2
BOS
WALL
[Code] .........
I have a control sheet that I am building and my sliderbar is making my data just disapear and not replacing it with any new data. Can this be fixed if I am able to reverse the sliderbar
View 2 Replies View RelatedI need a macro to reverse a number in the cell, suppose a user inputs the number the 48596, after running the macro it should show the output in other cell as 69584.
View 6 Replies View RelatedI have the following date problem, for example I need to reverse the following date:
8/02/2003 (which is recognised as a date) to:
2/08/2003 (the real date).
Is there an easy way to do this using a formula?
I have a column of cells which looks like this:
"JUMPING LAZY brown fox"
I need to put the non capitals in the front as so:
"brown fox JUMPING LAZY"
I've got a chess board in B2:I9, and would like a function that returns the location of a particular piece.
Say I have Q1 in cell B9, I'd like to return A-1
B1:I1 = A ---> H
A2:A9 = 8 ---> 1
I have an appointment schedule shaped like a table and I need to extract data from it into a sub table. The schedule has date column headers, time row headers, and the intersection of the date and time headers is a cell with a patient’s name. There are duplicate patient names in the schedule. The extract table should list the patient names vertically and next to each patient name should be a list of dates and times. The time and date for a single appointment should be contained in one cell.
For simplicity, I will not use dates and times in my example, but instead I will use letters.
Schedule looks like this:
Date Column headers are as follows: B1 = m, C1 = n, D1 = o, E1 = p
Time Row headers are as follows: A2 = q, A3 = r, A4 = s, A5 = t, A6 = u, A7 = v
The cells that contain patient names are: B2 = Jo, C3 = Sioux, C4 = Sioux, D2 = Sioux, E3 = Chin
The extract table looks like this:
Column headers are as follows: B20 = # App, C20 = App 1, D20 = App 2, E20 = App 3
Row headers (patient names) are as follows: A21 = Chin, A22 = Jo, A23 = Sioux
The cells that contain times and dates are: C21 = “r, p”, C22 = “q, m”, C23 = “r, n”, D23 = “s, n”, E23 = “q, o”, where the first letter is a time and the second letter is a date.
A while back I posted a question about this sort of reverse two way lookup:
[url]
Since then I have encountered the problem of the patients showing up in more than one time slot on a single day and also showing up in the schedule on more than one day. The formulas I am using are not working. So in my example, the trouble I am having comes from the fact that Sioux’s name shows up in more than one time slot for one day and also he shows up on more than one day.
If it is easier to see the table with dates and times, the data is here:
Schedule looks like this:
Date Column headers are as follows: B1 = 1/8/2010, C1 = 1/9/2010, D1 = 1/10/2010, E1 = 1/11/2010
Time Row headers are as follows: A2 = 8:00 AM, A3 = 9:00 AM, A4 = 10:00 AM, A5 = 11:00 AM, A6 = 12:00 PM, A7 = 1:00 PM
The cells that contain patient names are: B2 = Jo, C3 = Sioux, C4 = Sioux, D2 = Sioux, E3 = Chin
The extract table looks like this:
Column headers are as follows: B20 = # App, C20 = App 1, D20 = App 2, E20 = App 3
Row headers (patient names) are as follows: A21 = Chin, A22 = Jo, A23 = Sioux
The cells that contain patient names are: C21 = 9:00 AM, 1/11/10, C22 = 8:00 AM, 1/8/10, C23 = 9:00 AM, 1/9/10, D23 = 10:00 AM, 1/9/10, E23 = 8:00 AM, 1/10/10
I set up a formula knowing only two variables?
1. Assuming I know how much per month one can pay in total, and
2. The percent interest rate
... I need the total amount that can be financed.
Example:
A. $3,900 maximum per month
B. 5.00% interest rate
C. = x (the amount to be financed)
I have a userform into which a user can enter a number. When they exit the TextBox the number that they entered must be formatted into the number format. eg 12345,67 -> 12.345,67 and -12345,67 -> (12.345,67). To do this is not the problem: formatnumber does the trick. What I am struggling with is to get VB to still recognise this as a number. I need the code that does the opposite of formatnumber. (12.345,67) -> -12345,67. I excell it works perfectly when you format a cell as custom: _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_). I want to be able to do the exact same thing in VB.
View 3 Replies View Related