| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing data using the SMALL function
Hello all,
I'm having a bit of a problem that I hope that someone can help me with. Here's my sample data range: City, Rate Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 I want to populate a two-column, five row table with the city and rate. I've used the SMALL function in the rate column to include the five lowest rates. My problem is, I'm able to retrieve the rates, but I can't populate the first column with the City. What I need is if column B=14, then A=Baltimore and such. Does anyone have any ideas how I can set that up? Holli - who hopes that was semi-coherent |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing data using the SMALL function
Use this to get the cities in order of smallest first
=INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0)) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message ups.com... Hello all, I'm having a bit of a problem that I hope that someone can help me with. Here's my sample data range: City, Rate Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 I want to populate a two-column, five row table with the city and rate. I've used the SMALL function in the rate column to include the five lowest rates. My problem is, I'm able to retrieve the rates, but I can't populate the first column with the City. What I need is if column B=14, then A=Baltimore and such. Does anyone have any ideas how I can set that up? Holli - who hopes that was semi-coherent |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing data using the SMALL function
Try this:
This table is in the range A2:B7 Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 Enter this formula in D2: =INDEX(A$2:A$7,MATCH(E2,B$2:B$7,0)) Enter this formula in E2: =SMALL(B$2:B$7,ROWS($1:1)) Select both D2 and E2 and copy down 5 rows. Based on your sample data there are no duplicate numbers. If there are this will be more complicated. Post back if that's the case. Biff "HBuck" wrote in message ups.com... Hello all, I'm having a bit of a problem that I hope that someone can help me with. Here's my sample data range: City, Rate Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 I want to populate a two-column, five row table with the city and rate. I've used the SMALL function in the rate column to include the five lowest rates. My problem is, I'm able to retrieve the rates, but I can't populate the first column with the City. What I need is if column B=14, then A=Baltimore and such. Does anyone have any ideas how I can set that up? Holli - who hopes that was semi-coherent |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing data using the SMALL function
Biff,
Going through the numbers, not only are there a few duplicates, but the data is actually in more than one column. It just seems to get messier as we go along. Can you please tell me how to work around that? Thanks! Holli Biff wrote: Try this: This table is in the range A2:B7 Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 Enter this formula in D2: =INDEX(A$2:A$7,MATCH(E2,B$2:B$7,0)) Enter this formula in E2: =SMALL(B$2:B$7,ROWS($1:1)) Select both D2 and E2 and copy down 5 rows. Based on your sample data there are no duplicate numbers. If there are this will be more complicated. Post back if that's the case. Biff "HBuck" wrote in message ups.com... Hello all, I'm having a bit of a problem that I hope that someone can help me with. Here's my sample data range: City, Rate Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 I want to populate a two-column, five row table with the city and rate. I've used the SMALL function in the rate column to include the five lowest rates. My problem is, I'm able to retrieve the rates, but I can't populate the first column with the City. What I need is if column B=14, then A=Baltimore and such. Does anyone have any ideas how I can set that up? Holli - who hopes that was semi-coherent |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing data using the SMALL function
Thanks for that, Bob!
Can you please tell me how I would do that for multiple columns? I thought I had a handle on it, but I received an error message that I can't seem to get myself out of. Thanks, Holli Bob Phillips wrote: Use this to get the cities in order of smallest first =INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0)) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message ups.com... Hello all, I'm having a bit of a problem that I hope that someone can help me with. Here's my sample data range: City, Rate Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 I want to populate a two-column, five row table with the city and rate. I've used the SMALL function in the rate column to include the five lowest rates. My problem is, I'm able to retrieve the rates, but I can't populate the first column with the City. What I need is if column B=14, then A=Baltimore and such. Does anyone have any ideas how I can set that up? Holli - who hopes that was semi-coherent |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing data using the SMALL function
What does the data look like?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message oups.com... Thanks for that, Bob! Can you please tell me how I would do that for multiple columns? I thought I had a handle on it, but I received an error message that I can't seem to get myself out of. Thanks, Holli Bob Phillips wrote: Use this to get the cities in order of smallest first =INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0)) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message ups.com... Hello all, I'm having a bit of a problem that I hope that someone can help me with. Here's my sample data range: City, Rate Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 I want to populate a two-column, five row table with the city and rate. I've used the SMALL function in the rate column to include the five lowest rates. My problem is, I'm able to retrieve the rates, but I can't populate the first column with the City. What I need is if column B=14, then A=Baltimore and such. Does anyone have any ideas how I can set that up? Holli - who hopes that was semi-coherent |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing data using the SMALL function
Thanks for responding, Bob.
The data looks like the following: Cells B6 to C12 C-Sites Score Detroit 25 Chicago 50 Baltimore 14 Atlanta 23 San Francisco 42 San Jose 39 Cells E6 to F9 D-Sites Score Palo Alto 12 Canton 39 Omaha 14 I need to pull the city data from B7:B12, E7:E9 and the scores from C7:C12, F7:F9. There can definitely be duplicates in the scores. Hope that this helps. Thanks! Holli Bob Phillips wrote: What does the data look like? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message oups.com... Thanks for that, Bob! Can you please tell me how I would do that for multiple columns? I thought I had a handle on it, but I received an error message that I can't seem to get myself out of. Thanks, Holli Bob Phillips wrote: Use this to get the cities in order of smallest first =INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0)) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message ups.com... Hello all, I'm having a bit of a problem that I hope that someone can help me with. Here's my sample data range: City, Rate Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 I want to populate a two-column, five row table with the city and rate. I've used the SMALL function in the rate column to include the five lowest rates. My problem is, I'm able to retrieve the rates, but I can't populate the first column with the City. What I need is if column B=14, then A=Baltimore and such. Does anyone have any ideas how I can set that up? Holli - who hopes that was semi-coherent |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing data using the SMALL function
Holli,
does the data need to be in two separate tables? Can you combine it into one table using another column for Site-type, like the following? Type Site Score C Detroit 25 C Chicago 50 C Baltimore 14 C Atlanta 23 C San Francisco 42 C San Jose 39 D Palo Alto 12 D Canton 39 D Omaha 14 This would make it a bit easier to do what you are asking. Hope this helps. Pete HBuck wrote: Thanks for responding, Bob. The data looks like the following: Cells B6 to C12 C-Sites Score Detroit 25 Chicago 50 Baltimore 14 Atlanta 23 San Francisco 42 San Jose 39 Cells E6 to F9 D-Sites Score Palo Alto 12 Canton 39 Omaha 14 I need to pull the city data from B7:B12, E7:E9 and the scores from C7:C12, F7:F9. There can definitely be duplicates in the scores. Hope that this helps. Thanks! Holli Bob Phillips wrote: What does the data look like? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message oups.com... Thanks for that, Bob! Can you please tell me how I would do that for multiple columns? I thought I had a handle on it, but I received an error message that I can't seem to get myself out of. Thanks, Holli Bob Phillips wrote: Use this to get the cities in order of smallest first =INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0)) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message ups.com... Hello all, I'm having a bit of a problem that I hope that someone can help me with. Here's my sample data range: City, Rate Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 I want to populate a two-column, five row table with the city and rate. I've used the SMALL function in the rate column to include the five lowest rates. My problem is, I'm able to retrieve the rates, but I can't populate the first column with the City. What I need is if column B=14, then A=Baltimore and such. Does anyone have any ideas how I can set that up? Holli - who hopes that was semi-coherent |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing data using the SMALL function
Pete,
I agree with you completely about it being much simpler to do what I want. Unfortunately, I have no control over the way that the data is actually listed in the worksheet. It was brought to me to see if I could perform that specific function, leaving everything AS IS. I wonder if what I am asking is even possible. I can call up the lowest numeric values, but I can't match up the corresponding labels. If it can't be done, then that is what I have to let them know. Either that, or I can let them know that it would be better to list everything in one table. Thanks again! Holli Pete_UK wrote: Holli, does the data need to be in two separate tables? Can you combine it into one table using another column for Site-type, like the following? Type Site Score C Detroit 25 C Chicago 50 C Baltimore 14 C Atlanta 23 C San Francisco 42 C San Jose 39 D Palo Alto 12 D Canton 39 D Omaha 14 This would make it a bit easier to do what you are asking. Hope this helps. Pete HBuck wrote: Thanks for responding, Bob. The data looks like the following: Cells B6 to C12 C-Sites Score Detroit 25 Chicago 50 Baltimore 14 Atlanta 23 San Francisco 42 San Jose 39 Cells E6 to F9 D-Sites Score Palo Alto 12 Canton 39 Omaha 14 I need to pull the city data from B7:B12, E7:E9 and the scores from C7:C12, F7:F9. There can definitely be duplicates in the scores. Hope that this helps. Thanks! Holli Bob Phillips wrote: What does the data look like? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message oups.com... Thanks for that, Bob! Can you please tell me how I would do that for multiple columns? I thought I had a handle on it, but I received an error message that I can't seem to get myself out of. Thanks, Holli Bob Phillips wrote: Use this to get the cities in order of smallest first =INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0)) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message ups.com... Hello all, I'm having a bit of a problem that I hope that someone can help me with. Here's my sample data range: City, Rate Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 I want to populate a two-column, five row table with the city and rate. I've used the SMALL function in the rate column to include the five lowest rates. My problem is, I'm able to retrieve the rates, but I can't populate the first column with the City. What I need is if column B=14, then A=Baltimore and such. Does anyone have any ideas how I can set that up? Holli - who hopes that was semi-coherent |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Referencing data using the SMALL function
I'm hesitant to say something can't be done but in this case......
I'll say that it can probably be done with VBA code but I don't know how to do it using worksheet formulas. If you posted this in the programming forum everyone that responds will tell you the same thing: put all the data in a single table. Biff "HBuck" wrote in message oups.com... Pete, I agree with you completely about it being much simpler to do what I want. Unfortunately, I have no control over the way that the data is actually listed in the worksheet. It was brought to me to see if I could perform that specific function, leaving everything AS IS. I wonder if what I am asking is even possible. I can call up the lowest numeric values, but I can't match up the corresponding labels. If it can't be done, then that is what I have to let them know. Either that, or I can let them know that it would be better to list everything in one table. Thanks again! Holli Pete_UK wrote: Holli, does the data need to be in two separate tables? Can you combine it into one table using another column for Site-type, like the following? Type Site Score C Detroit 25 C Chicago 50 C Baltimore 14 C Atlanta 23 C San Francisco 42 C San Jose 39 D Palo Alto 12 D Canton 39 D Omaha 14 This would make it a bit easier to do what you are asking. Hope this helps. Pete HBuck wrote: Thanks for responding, Bob. The data looks like the following: Cells B6 to C12 C-Sites Score Detroit 25 Chicago 50 Baltimore 14 Atlanta 23 San Francisco 42 San Jose 39 Cells E6 to F9 D-Sites Score Palo Alto 12 Canton 39 Omaha 14 I need to pull the city data from B7:B12, E7:E9 and the scores from C7:C12, F7:F9. There can definitely be duplicates in the scores. Hope that this helps. Thanks! Holli Bob Phillips wrote: What does the data look like? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message oups.com... Thanks for that, Bob! Can you please tell me how I would do that for multiple columns? I thought I had a handle on it, but I received an error message that I can't seem to get myself out of. Thanks, Holli Bob Phillips wrote: Use this to get the cities in order of smallest first =INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0)) and copy down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HBuck" wrote in message ups.com... Hello all, I'm having a bit of a problem that I hope that someone can help me with. Here's my sample data range: City, Rate Chicago, 50 New York, 23 San Francisco, 43 San Jose, 55 Baltimore, 14 Ann Arbor, 28 I want to populate a two-column, five row table with the city and rate. I've used the SMALL function in the rate column to include the five lowest rates. My problem is, I'm able to retrieve the rates, but I can't populate the first column with the City. What I need is if column B=14, then A=Baltimore and such. Does anyone have any ideas how I can set that up? Holli - who hopes that was semi-coherent |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find the maximum value that is less than the lookup value in a unsorted range
Hi Biff,
I accidentally found your postings in Google Groups from the following link http://groups.google.ca/group/micros...64a6492673fe67 I think you are really good at excel. I have the same problem as what the guy had. I tried what you wrote, however, the formula MAX(IF(A1:A10<D1,A1:A10).... doesn't work for me. It simply shows a #Value. I was wondering if there is another way to find the maximum value that is less than the lookup value in a unsorted range? Your help is highly appreciated. Thank you. Karat |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find the maximum value that is less than the lookup value in a unsorted range
See this screencap:
http://img213.imageshack.us/img213/7764/maxifke1.jpg Make sure you enter the formula as an array: CTRL,SHIFT,ENTER (not just ENTER). If there is no max value that is less than the lookup value then the formula will eturn 0. Biff "JoyCarrot" wrote in message oups.com... Hi Biff, I accidentally found your postings in Google Groups from the following link http://groups.google.ca/group/micros...64a6492673fe67 I think you are really good at excel. I have the same problem as what the guy had. I tried what you wrote, however, the formula MAX(IF(A1:A10<D1,A1:A10).... doesn't work for me. It simply shows a #Value. I was wondering if there is another way to find the maximum value that is less than the lookup value in a unsorted range? Your help is highly appreciated. Thank you. Karat |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
| Having Trouble with the RANK Function...Try LARGE or SMALL Functio | Excel Worksheet Functions | |||
| Macro question | Excel Worksheet Functions | |||
| Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
| Sort pages? | Excel Discussion (Misc queries) | |||
Linear Mode
