Ranking Data

Hello, any help would be appreciated!

I have a sheet with four different data sets which I would like to
rank, here is the problem.  Each data set consists of two columns, one
column is an ID number and the other column is an associated numerical
score, these need to be linked.  This is the same for each of the four
sets of two columns.  Then, the numerical score for each data set needs
to be multiplied by a factor, creating another numerical value, linked
to the ID number.  Then each of these numerical values, linked to their
ID number will be sumed to created an overall score for each ID number.
Finally I would like to rank the associated ID numbers with the
computed score.  If this does not make sense, please email me at
pearsonewa@msn.com!  Thanks for your time.


---
Message posted from http://www.ExcelForum.com/

0
1/13/2004 11:19:35 AM
excel 39879 articles. 2 followers. Follow

1 Replies
450 Views

Similar Articles

[PageSpeed] 53

Erik,

You need to first make up a list of your unique ID numbers.

This solution assumes that each ID number appears only once in each
data set, and that all ID numbers appear in each data set.

Extract your scores using 4 separate VLOOKUPs, multiplying them by
their factors and adding them together, along the lines of this, with
your ID number in cell J1, and your data tables in columns A:B, C:D,
E:F, and G:H, from row 1 to 20:

=1.1*VLOOKUP(J1,A1:B20,2,FALSE) + 1.2*VLOOKUP(J1,C1:D20,2,FALSE) +
1.3*VLOOKUP(J1,E1:F20,2,FALSE) + 1.4*VLOOKUP(J1,G1:H20,2,FALSE)

Copy that formula down, to match your ID numbers, then use the RANK
function in another column to return their rank.

HTH,
Bernie
MS Excel MVP

"pearsonerik >" <<pearsonerik.zywil@excelforum-nospam.com> wrote in
message news:pearsonerik.zywil@excelforum-nospam.com...
> Hello, any help would be appreciated!
>
> I have a sheet with four different data sets which I would like to
> rank, here is the problem.  Each data set consists of two columns,
one
> column is an ID number and the other column is an associated
numerical
> score, these need to be linked.  This is the same for each of the
four
> sets of two columns.  Then, the numerical score for each data set
needs
> to be multiplied by a factor, creating another numerical value,
linked
> to the ID number.  Then each of these numerical values, linked to
their
> ID number will be sumed to created an overall score for each ID
number.
> Finally I would like to rank the associated ID numbers with the
> computed score.  If this does not make sense, please email me at
> pearsonewa@msn.com!  Thanks for your time.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Bernie
1/13/2004 1:56:06 PM
Reply:

Similar Artilces:

Problem importing data from webpage
I am trying to import some data from a website where the data 'pops up' in a window and does not give you the web page address, so I can't use Data / Import External Data / New Web Query to obtain it. I tried a simple copy and paste but all I get is an annoying bitmap icon in the top left of my screen. How can it change this into the data I want? Thanks, Noel "Noel S Pamfree" <Noel.spamfree@hotmail.com> wrote in news:e6ZlU9GsFHA.1172@TK2MSFTNGP11.phx.gbl: > I am trying to import some data from a website where the data 'pops > up' in a win...

Chart Text Box Data Table
For the boss, I would like to display a series of figures (as text) that are derived from the data underlying a chart. For example, the chart shows electricity demand in kW over the course of the day. In the upper corner of the chart, I want to display the total usage in kWhr for the whole day, its cost, and the percentage difference from our budget. I know how to do this with just one value: create a text box, then use the formula bar to enter something like "=Sheet1!$C$4". But to do that with all the information I want to display would require about a dozen text boxes, a...

find rows for unique data in 1 column and different data in other.
I have a spreadsheet that contains 1 column that contains cells that may contain the same information such as A2 and A4 and A6 all contain 4144638872 - in columns B-P there may be information that may be different for each 1234 cell: A2=4144638872 B2=278954 A4=4144638872 B4=195871 A6=4144638872 B6=238521 I want to return a result of unique rows based on only 1 phone # that exists in column A. I need the information in column B and other columns and I don't care which of the 3 remain in the result sheet. Basically I have a list of file#s with the same phone #s and I only want ...

List a ranking
I'm a newbie at this, so help is appreciated. I have an Excel sheet like this: Team A .600 =rank(b1,b1:b3) Team B .750 =rank(b2,b1:b3) Team C .300 =rank(b3,b1:b3) I want to get another cell to list how far they are from the #1 rank. So, cell d1 should be equal to .150 and d3 equal to .450. The percentages will change frequently, so it needs to figure out what is #1. I know I could just do =b2-b1 and b2-b3, but only because I can see the b2 is the highest percentage. Am I making sense? Is there a way to do this? TIA. BR =MAX($B$1:$B$3)-B1 etc. -- HTH RP ...

Inverting a set of data
Hi, I'm going through a large data base and need to invert the data so the reverse route is shown. A B A B e.g. 1 2 6 5 3 4 4 3 5 6 to 2 1 Is there a quick way of doing this either through a formula or function that anybody knows? Any help would be greatly appreciated. Maybe sort both columns in descending order and move column A after column B! Regards, Stefi „simac” ezt írta: > Hi, I'm going through a large data base and need to invert the data so the > reverse route is show...

passing data from external app into field
We have a standalone tool (.exe) that came with our license manager (Elan) that we use for the software we develop. With this tool, we enter in all of the customer/license criteria, and it returns a string that we use for the license key. Unfortunately, we have to enter this twice; once in the crm, then again in the tool to generate the key, followed by cutting and pasting the string into the field in the CRM. I'm looking for a way to streamline this process. I figure I can extract the information via T-SQL or stored procedures, but am unsure of how it would be best to place tha...

How can I create multiple charts using variable data?
I have a series of formulas that generate pie charts for all my employees. To manually generate a chart, I have to input the salary and name of each employee. Is there a way to automate this process as only two values are changing for each generated chart? I'm using Excel 2003 for this task. Can you put name and salary into two columns, and use a value in a cell to indicate which employee in the list to display? Say the data is in A1:B100, and the indicator cell is C1. In D1 enter this formula =index(A1:A100,$C1) and copy this cell onto E1. You can change what's in D1:E1 by cha...

How to sort time data?
I have hours worth of second by second data and need to sort it, but I only need to see every 10 seconds. Is there an easier way than just either deleting hundreds of rows or selecting them (copy and paste)? I am sure you could also write a macro, but I am too familiar with those either. Thank you, I don't fully understand what you mean about "every 10 seconds", but here is the general truth about times: Times are numbers. If you can do something with a number, you can do it with time, even if it doesn't make sense: AVERAGE, STANDARD DEVIATION, MIN, MAX, ROUND, DIVID...

Outlook Today data disappears OL2007
When I start Outlook (OL2007 B2TR), Outlook Today briefly displays my information, but then the info completely goes away. Everything else displays correctly. Any ideas? Thanks! Does it go to Inbox after doing this? Robert M. Atkinson wrote: > When I start Outlook (OL2007 B2TR), Outlook Today briefly displays my > information, but then the info completely goes away. Everything else > displays correctly. > > Any ideas? Thanks! Not unless I click manually on Inbox. Otherwise, it will just stay in an empty OL Today. Thanks for the response! "Justin Brown - ...

Querying data
I have to create a report for the query below SELECT ClericalMetrics.PS_ID, PerformanceSupervisor.PerformanceSupervisor, ClericalMetrics.[Time Sheet Errors], Count(ClericalMetrics.[Time Sheet Errors]) AS [CountOfTime Sheet Errors], ClericalMetrics.TimeSheetErrors2, Count(ClericalMetrics.TimeSheetErrors2) AS CountOfTimeSheetErrors2, ClericalMetrics.TimeSheetErrors3, Count(ClericalMetrics.TimeSheetErrors3) AS CountOfTimeSheetErrors3, ClericalMetrics.TimeSheetErrors4, Count(ClericalMetrics.TimeSheetErrors4) AS CountOfTimeSheetErrors4 FROM ClericalMetrics LEFT JOIN PerformanceSuper...

Formatting lines between data points in a line chart
I have a simple line chart which graphs data over a 3-year timeframe. Between each of the years, I would like there to be a gap in the line. In my previous version of Excel, I was able to do this by choosing "None" for the formatting of the line between the datapoints. In my current version of Excel (Excel 2002, XP operating system), if I choose "none" it simply charts a line between 2 non-contiguous months. For example, if I remove the line between December 2004 and January 2005, Excel 2002 will simply graph the line between December 2004 and February 2005. Any...

is it possible to find if there are data not written to data storage
Hi! Assume I have a stream. This stream should write some data to a data storage Is it possible to find out if there are any data left in the stream that will be written if I do a flush. //Tony Tony Johansson wrote: > > Assume I have a stream. This stream should write some data to a data storage > Is it possible to find out if there are any data left in the stream that > will be written if I do a flush. > Without more specifications: No. Define "a stream" and "a flush". If System.IO.Stream is what you are talking about, then...

Rank within Categories
I am trying to work out a way to rank within categories. Column E has different categories. Column i has the data. Column am working on i want to give the ranking where each data point falls within its own category. i have tried to hash something out but am stuck...here is my feeble attempt =IF($E5="SE",IF($E$5:$E$40="SE",IF(I5>0,RANK(I5,$I$5:$I$40,0),"N/ A"), ...... ..... signfies a string of IFs. the Next one being IF($E5="C".... I have also tried an array.....that doesnt seem to work =IF(OR(E6={"C","SE","NE","...

Opening an excel file which has a query to external data
I have an access application which opens an excel file which has a query to that access application to import data. * I open the excel file when the access application is closed, i get the message "this workbook contains etc ... Enable automatic refresh". Until here everything works as expected and the data is refreshed fine. * When I open the excel file when the access application is opened i get the message that the db is opened exclusively by another user. This I understand ass well, but is it possible to resolve this? * Now the thing I'm looking for is: Is it possible to ...

Comparing data intervals
If 2 observers collected data, what formula would be needed to count the total number of intervals (potentially 30 intervals) observer 1 agreed with observer 2. Observer 1 will enter data (1-5) on row 3 (cells T3-AW3) Observer 2 will enter data (1-5) on row 4 (cells T4-AW4) In other words, how many times does T3=T4, does U3=U4, etc.? Is there a short formula to use without having to identify all 30 intervals? =sumproduct(--(t3:aw3=t4:aw4)) May work for you. smoutsat wrote: > > If 2 observers collected data, what formula would be needed to count the > total number of interva...

Chart non consecutive data
Is it possible to chart non-consecutive data. I haven't been successful in my attempts so far. Data is in same row, ie. row 4; columns needed are H4 and J4 (need to be stacked), next series is K4 , L4 & M4. Unfortunately, I can not rearrange the cells to make this easier. Any help or suggestion would be appreciated. I haven't done that much charting with Excel (2003). TIA XL doesn't stack data in the same series. It stacks one series on top of the previous. You can simulate the effect you want as follows. Select H4:J4 and create a stacked column chart. In step ...

Data validation #51
I am using Data Validation to restrict entry into a cell to the items in a list and I have two questions. Firstly, can I adjust the width of the drop down box which appears when I click on the arrow in the cell? The items in the list are only a few characters and the box is much wider - I just want to "tidy" this up a bit. Secondly is there a way of making the list drop down automatically when the cell has the focus rather than having to click on the arrow - this would just speed up the date entry as I have a number of columns with Data Validation on the sheet? Many thanks for an...

rank
I would like to know how to get around duplicate issues when using th rank functio -- Message posted from http://www.ExcelForum.com Hi see: http://www.cpearson.com/excel/rank.htm and http://www.xldynamic.com/source/xld.RANK.html -- Regards Frank Kabel Frankfurt, Germany > I would like to know how to get around duplicate issues when using the > rank function > > > --- > Message posted from http://www.ExcelForum.com/ ...

sorting data and automatic graphs
i have a large amount of data that is an xy scatter. it is xy data that pertains to many subjects. i need to make a graph for each subject. as of now i make the first one and then cut and paste to a new one, use source data and change the x and y data. this does take me a long time since i have alot of subjects. is there another way to pick my data range and sort by subject to make differenct graphs without cutting/pasting and constantly changes the source data. i tried tinkering with a pivot table but everytime i changed the table, all tables changed. There may be depending on the s...

Java Data into Excel?
In trying to get live data into an Excel database using a web query, I found a streamed source of data. However this is in a java format. It resembles a table, and I was wondering if I could import the data into each respective cell in a spreadsheet, or somehow link a Cell reference to the Java. Any ideas? I have a spreadsheet with all my math and an html page open with my data?!?! Close but no cigar! The java is not in an importable table (as typically required in a web query). THis java is streamed and not limited to the Web-query 'refresh'. Thanks in advance for any ideas/filter...

repeating data
I need to submit a list of locations to our programmer which consists o about 4170 rows. Is there any way to have this list generated by formula since there is a pattern to the repetition. col level slot 39 1 a 39 1 b 39 1 c 39 2 a 39 2 b 39 2 c 41 1 a 41 1 b 41 1 c 41 2 a 41 2 b 41 2 c This is the general format of what I need to submit -- actually for every col there are 10 levels and each ...

Textbox Format as you enter data...
Is there a way to make a userform textbox auto format data while it being entered. I.E. The user is entering a telephone number, they enter the first 3 digits then Parathesis in case these numbers (Area Code), then they enter th next 3 digits, a (-) is inserted afterwards, then they enter the last and tab to exit the field. I guess you can call this active formatting, is there anyway. I ca make the format change when the user exits the textbox, but I want i to format it as they are typing -- Message posted from http://www.ExcelForum.com ...

subtotal rank
I'm working with a filtered list and I'm looking for a rank formula that works like the subtotal formula does. So when I filter the list it ranks by the particular filter. Thanks in advance BW --- Message posted from http://www.ExcelForum.com/ ...

Is the CRM Data Migration Framework compatible with CRM 1.2?
I am attempting to use the MS CRM Data Migration Framework to move data into CRM via the CDF staging database. We are running version 1.2 of CRM and I am attempting to use the Data Migration Framework that is currently available on the Microsoft Partners page. When I attempt to run the CDF Initialization tool (Microsoft.Crm.DataMigration.InitializeCDF.exe, which is tagges as version 1.0.3107.0) I receive a message stating that the version of the CDF database differs from that of the CRM databases. I can see the CDF database in SQL Server Enterprise Manager, so the Migration Framew...

After compaction
Hi everyone! After standard compacting I have some emails shortage, sent emails from date1 to date2 have disappeared. I do not have the bakup and wondering if there is a chance to get this back? Any help much appreciated, Tomek Your system is? Mail is OE 6? DAS To reply directly replace 'nospam' with 'schmetterling' -- "tob" <informatyka@mzk.zgora.pl> wrote in message news:u0Pnl0irKHA.4284@TK2MSFTNGP04.phx.gbl... > Hi everyone! > > After standard compacting I have some emails shortage, sent emails from > date1 to d...