rank/small with multiple ranges

I'd like to refer to 3 different ranges of numbers and 
make a new list which(when copying down the formula) 
creates a list of all the numbers in ascending order.  Can 
I use the rank function across multiple ranges?

say a1-a10, c1-c10, and e1-e10(so you can't highlight the 
whole rnge in one shot)

and make g1 show the smallest number in all the ranges, g2 
show the second smallest, etc.

tia,
Dave
0
anonymous (74722)
3/23/2005 4:23:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
376 Views

Similar Articles

[PageSpeed] 59

Enter this & fill down 30 rows:
=LARGE(($A$1:$A$10,$C$1:$C$10,$E$1:$E$10),ROW(A1))

"dave" <anonymous@discussions.microsoft.com> wrote in message
news:0d0701c52fc4$95376c20$a601280a@phx.gbl...
> I'd like to refer to 3 different ranges of numbers and
> make a new list which(when copying down the formula)
> creates a list of all the numbers in ascending order.  Can
> I use the rank function across multiple ranges?
>
> say a1-a10, c1-c10, and e1-e10(so you can't highlight the
> whole rnge in one shot)
>
> and make g1 show the smallest number in all the ranges, g2
> show the second smallest, etc.
>
> tia,
> Dave


0
rumlas (268)
3/23/2005 4:41:34 PM
thanks - I switched it to small for my purposes.  Perfect!
>-----Original Message-----
>Enter this & fill down 30 rows:
>=LARGE(($A$1:$A$10,$C$1:$C$10,$E$1:$E$10),ROW(A1))
>
>"dave" <anonymous@discussions.microsoft.com> wrote in 
message
>news:0d0701c52fc4$95376c20$a601280a@phx.gbl...
>> I'd like to refer to 3 different ranges of numbers and
>> make a new list which(when copying down the formula)
>> creates a list of all the numbers in ascending order.  
Can
>> I use the rank function across multiple ranges?
>>
>> say a1-a10, c1-c10, and e1-e10(so you can't highlight 
the
>> whole rnge in one shot)
>>
>> and make g1 show the smallest number in all the ranges, 
g2
>> show the second smallest, etc.
>>
>> tia,
>> Dave
>
>
>.
>
0
anonymous (74722)
3/23/2005 4:48:19 PM
Reply:

Similar Artilces:

Multiple DPM Tapes
Is there a way to use more than one tape (only one drive)? I have 3TB of data and a tape is 800MB compressed. I've been able to erase the new tape but it won't use it. Are you getting an error message? When my system fills a tape it just sits and waits until a new tape comes online, then starts writing to it after a minute or so. Daniel "philt" wrote: > Is there a way to use more than one tape (only one drive)? I have 3TB of data > and a tape is 800MB compressed. I've been able to erase the new tape but it > won't use it. > &...

Multiple signatures #2
Can I set outlook 2000 up to only attach my signature to external messages? If so, how? >-----Original Message----- >Can I set outlook 2000 up to only attach my signature to external messages? >If so, how? > > >. > Two posts won't get a response twice as fast. See my responses to your other post. ...

How to: Compare multiple dates and enumerate cell in row with most recent date
Howdy All, This is what I want to do: I have a spreadsheet with 4 dates in columns I, J, K, and L starting in row 3. In the corresponding columns of row 1, i want to count the number of times each column contains the most recent date. Example: I3 = 1/1/2000, J3 = 2/2/2001, K3 = 3/3/2002, L3 = 4/4/2003 L3 would now equal 1. Then next time column L contains the most recent date, L3 would enumerate and equal 2, etc. Any ideas? Thanks, Brian You have replies at your other post - please do not multi-post. Pete ...

Subscript out of range Error? #2
I keep getting a subscript out of range error with this macro. Any help would be great. Public Sub AddSheetToEnd() 'Create a new sheet Dim NewWorksheet As Worksheet Set NewWorksheet = _ Application.Sheets.Add( _ After:=Worksheets(GetLastSheet), _ Type:=XlSheetType.xlWorksheet) ' Rename the worksheet NewWorksheet.Name = "Added Worksheet" ' Place a title in the worksheet. NewWorksheet.Cells(1, 1) = "Sample Data" ' Add some headings. NewWorksheet.Cells(3, 1) = "Lable" NewWorksheet.Cells(3, 2) = "Data" NewWorkshe...

excel to powerpoint- misalignment of ranges
I have an excel charts which i then paste on powerpoint using the code from Jon Peltier's website. On most charts this works terrific. On some of my charts, in excel I previously copyied a range and pasted it inside the chart. When these charts are pasted into powerpoint the segment which was pasted as a range, becomes misaligned and slides to the left. any solutions would be appreicated. thks ken ...

Ranking with Duplicates
I am trying to rank a list of 10 numbers and I know how to rank using the RANK function. I have a problem though. When there are two (or more) values that are the same, i want them to show .5 instead of multiple 4s. i.e. 1356 9 1394 12 1252 6 1141 2 1374 10 1388 11 1152 3 1242 5 1256 8 1252 6 1158 4 980 1 There are two occurences of 6.. but I would them to each be 6.5 rather than each of them being 6. Any thoughts? See one of your other posts -- Biff Microsoft Excel MVP <shocksterman@gmail.com> wrote in message news:33f6eee0-7d18-4715-bb17-b45188bbb...

Selecting multiple filters in a pivot table
When I use filters on pivot tables that others create, I can select multiple values on which to filter. There is a little white box next to each item that I can select by putting a check mark in it. However, when I create a pivot table, I can only select one item from the filter at a time. How do you set up the pivot table so you can select multiple items? Thanks. -- Anne If a field is in the Page area, it doesn't have check boxes. Could that be the problem? To select multiple items from a page field, you can temporarily move the field to the row area, select the items, then m...

Problem with External Data range Properties
Hi In the External Data Range Properties, there are 3 options for 'If the number of rows in the data range changes upon refresh:' I want it to stay on option 3 - Overwrite existing cells with new data, clear unused cells. but it keeps defaulting to option2 - Insert entire rows for new data, clear unused cells. I am using Excel 2000 and my data is from Access 97. Any ideas?? Steve ...

Importing multiple XML queries into Excel
I am trying to import multiple XML queries into Excel and cannot import more than one at a time. I have a list of queries and am using the XML feature to pull the data. Unfortunately, I have to cut and paste each query and then import them individually. Does anyone know how to automate this process or if there is an easier way to import batches? -- Thank you in advance for the help. I need it! ...

Multiple page sizes in one document
I need to print multiple page sizes from one document. Can someone point me to an article on this? Thanks You need to use ResetDC(...) before calling the StartPage(...).You should look at the DEVMODE and dmPaperSize member to set the appropriate paper size. Thanks Vipin "mike" <nospamplease.com> wrote in message news:%23SXiYwSkFHA.3448@TK2MSFTNGP12.phx.gbl... > I need to print multiple page sizes from one document. Can someone point me > to an article on this? > Thanks > > You need to use ResetDC(...) before calling the StartPage(...).You should look at...

parameter query
I have a parametery query setup in excel which gets its input from a cell, which does this.. Status In [EnterStatus] [EnterStatus] query is in a cell It works fine if I only enter one value in the cell but if I enter A,I,X (Active, Inactive, Expelled) for example i receive a string truncation error... if there were only two or 3 options I coult use 2 or 3 fields, but ehre are 10 possibles... any ideas how I can ask for multiple options.. the sql query would be Select * fom table where status in ('A','I','X')... aNY IDEAS ...

Creating a huge multiple table diagonally, not row by row or column by column?
Dear All: I want to creating a multiple table using Excel 2007, as follows: 1 2 3 ... 1 (1) (2) (3) ... 2 (2) (4) (6) ... 3 (3) (6) (9)... .. .. .. Every position is the product of leftmost multiply top most value. I can use formula to do it row by row or column by column. But I cannot do it diagonally. The reason I want to finish this table one step is there are too many rows and columns to do them by row or by column only. Thanks. Du In B2 enter this formula: =3D$A2*B$1 Copy this across the row into C2 to however many columns you have. Then highlight B2 to your last cell a...

Global named range ????
Is it possible to create a named range that will refer to the same cell on every worksheet in a particular workbook ie SheetType=ActiveSheet!$A$1 This does not seem to work Thanks I have set the named range as you suggested.... Now I am trying to access the value in VBA Activesheet.Range("SheetType").................. this works Worksheets("sheetname").Range("SheetType") ...............this does not can refer to the range on a specific sheet this way or must I set the activesheet and refer to it that way???? Thanks for your help "Chip Pearson&qu...

Excel VB
I've got a lot of variables to set and I'm trying to find some easie ways to do it. Say I need varWidgets, varGizmos, varThingies, and varSomethings set t different amounts. Can I avoid a line by line initialization and d something similar to the declarations: where multiple variables are al set to similar figures? Example: varWidgets, varGizmos, varThingies = 10 varSomethings = 25 --- I just have tons of variables I need to assign values to and typin them all out one by one seems innefficient. There has to be a bette way? :confused -- Message posted from http://www.ExcelForum....

Formulas for Multiple sheets in a workbook
Am creating a wookbook with muliple sheets (50) and want to link info from sheet 1 to sheet 2 and from sheet 2 to sheet 3, etc. On shhet 2 I am using the following formula: 'Sheet 1'!A2, 'Sheet 1'!etc. Not all the info needs to transferred... some rows have daily info to be typed in daily. How do I create new sheets without having to change the formula in each cell by changing: 'Sheet 1'!A2 to 'Sheet 2'!A2. Any help would be great. This workbook is a daily report that has some info that changes daily and some that does not change. Thanks, Gary this migh...

Printing from multiple sheets
In the old Quattro Pro program it was possible to select cells from numerous sheets just as you would in Excell on a single sheet, then print them all on a single page (i.e. Jan through Dec tabs and print cell b4 from each sheet). Does anyone know if this is possible with Excell? If not, it would be helpful. Dave, Probaby the only way to print stuff from separate sheets on one page is to make a sheet with links (Copy - Paste special - paste-link). Then print that sheet. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "...

Multiple instances of Dynamics CRM 4.0 on one domain
Hi, Does anybody know if this is possible. We had CRM installed on one server on the network and then attempted to install another instaance on a Virtual Server, but on the same domain. both connecting to their own local SQL Server DBs). The version on the Virtual Server works OK but now the original version doesn't work. If you try to browse it as any user you get the message: Access Error The system could not log you on. This could be because your user record or business unit you belong to has been disabled Any help would be appreciated Thanks On Jun 30, 12:05=A0pm...

copy a specified name range
I have an estimating program file with 30+ worksheets in which I need to be able to specify a specific range name from four available, then paste the data from a 5 column by 6 row range relating to that named range to a different worksheet to calculate overheads, field supervision, AFDC and profit using the specified range name (the four range names have different values). I think an 'IF' statement would work but I do not know how to tell it to choose the range and then paste it to the specific area on the 1st worksheet and be able to overwrite that data if another range name i...

multiple columns, each with a forecast line
Hi i'm trying to build a chart that will display actuals (columns) and forecasts (which is a horizontal line about the same width as the column within the column) x-axis - product a, product b, product c y-axis - sales for each column (which denotes a product), I would like to plot a horizontal line to indicate the forecast. The column height indicates actual, and the line height indicates the forecast. any help would be most appreciated thanks K Making a horizontal lines the same width as the columns in the chart could be a task, but this might be a useful workaround. Sample data...

Multiple user on 1 program
Hi, I was wondering if anyone know if it is possible to have multiple people using Money 2006 to manage their own accounts. I am able to log into my personal account with the software, but when I try to use another passport, the username and password are not accepted. It does that for all accounts except for the initial setup accounts. Any ideas? In microsoft.public.money, PigMan wrote: >Hi, >I was wondering if anyone know if it is possible to have multiple people >using Money 2006 to manage their own accounts. I am able to log into my >personal account with the softw...

multiple owners of resource account
Is it possible to have multiple owners of a resource account? ...

VBA code to hightlight range of cells based on the value of another adjacent cell
All, I'd like to know if it is possible to make a VBA code for exel that highlights a range of cells based on the value of a particular cell. Sample data: ALPHA Street Low High City Zip SEQ A Ahasta 1 98 SOUTH SAN FRANCISCO 94080 200 A Aherwood 2401 2499 SOUTH SAN FRANCISCO 94080 190 B Blyline Dr 2601 2899 SOUTH SAN FRANCISCO 94080 475 B Bouthcliff 100 215 SOUTH SAN FRANCISCO 94080 140 C Couthcliff 217 398 SOUTH SAN FRANCISCO 94080 150 C Ctamford 2600 2698 SOUTH SAN FRANCISCO 94080 170 C Cetein 3900 3998 SOUTH SAN FRANCISCO 94080 160 D Dummit 1 98 SOUTH SAN FRANCISCO 94080 190 D D...

Displaying multiple outcomes
I am currently building a spreadsheet to help track progress and other information for a local sports club. What I need is a formula that will all me to display one of three outcomes, win/loss/draw from two variables i.e Points for and points against. if for instance cell A1 is a value for "Points for" and cell a2 is a value for "Points against", then I wish to display either win/loss/draw depending on whether a1>a2, a1<a2 or a1=a2. Can anyone help by providing me with a formula for such Thanks Crossroader35 One way: =IF(A1>A2,"Win",IF(A1<A2...

removing rows but not based on a range
Hello: Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove ...

Multiple rules cause multiple emails
I have this issue that's driving me nuts. I'm running XP and Outlook 2003. I have basic rules that are in place to move emails from my inbox to specific folders based upon sender. I also have a custom rule for moving emails based upon certain phrases to another folder. What's happening is that both rules are getting applied to the same email, and I wind up with two copies of the email. One in the sender specific folder, and another in the general catch folder. This happens quite frequently, and is very annoying. Question is how do I stop this? First off, the sender sp...