Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between category limits
Hi,
I hope this description will be clear, please forgive me if its not. I do not know whats the easiest or neatest way to achieve this. I have a list of data composed of input numbers, then a category column then a category limits column, such as A C D 0,2 1 0 0,4 2 0,3 0,6 3 0,6 4 0,9 So i have input data in A. In B I have a category, which is numerical. In D I have a regularily increasing range of data. I would like excel to look at the data in A, (0,2 for example) then search through D to find which two values it lies between and then return the category that corresponds to that input. The category is the higher ranking number of the two values it lies between so a value of 0,1 for example is category 2. If it is equal to a limit it takes the category that corresponds to that limit, so 0,3 is category 2 for example. The result output I would like to see in col E would read 2, 3, 3. What is the best function to use to achieve this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between category limits
Hi,
I assume that's a typo where in your data table the categories are shown in column C and you mean column B as in your narrative. Maybe this =OFFSET(INDEX($B$1:$B$4,MATCH(A1,$C$1:$C$4)),1,0) Drag down as required. You don't say what you would want for (say) 0.3 so I have assumed the higher category. Mike "LiAD" wrote: Hi, I hope this description will be clear, please forgive me if its not. I do not know whats the easiest or neatest way to achieve this. I have a list of data composed of input numbers, then a category column then a category limits column, such as A C D 0,2 1 0 0,4 2 0,3 0,6 3 0,6 4 0,9 So i have input data in A. In B I have a category, which is numerical. In D I have a regularily increasing range of data. I would like excel to look at the data in A, (0,2 for example) then search through D to find which two values it lies between and then return the category that corresponds to that input. The category is the higher ranking number of the two values it lies between so a value of 0,1 for example is category 2. If it is equal to a limit it takes the category that corresponds to that limit, so 0,3 is category 2 for example. The result output I would like to see in col E would read 2, 3, 3. What is the best function to use to achieve this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between category limits
The most common solution to your problem is to use Vlookup, as in:
=vlookup(a1,C:D,2,true) However, you would have to rearrange your data to accommodate this. Vlookup needs the lookup value (ie, your D column) in the first column, and the value to be returned in columns to the right. And you'd have to change your rows so you start with 0.3, rather than 0. If it's required to have the category first, you can use a combination of Index and Match to do this. In your case, there's a mathematical relationship between category and limits, so you can use: =ceiling(a1/0.3,1)+1 Regards, Fred. "LiAD" wrote in message ... Hi, I hope this description will be clear, please forgive me if its not. I do not know whats the easiest or neatest way to achieve this. I have a list of data composed of input numbers, then a category column then a category limits column, such as A C D 0,2 1 0 0,4 2 0,3 0,6 3 0,6 4 0,9 So i have input data in A. In B I have a category, which is numerical. In D I have a regularily increasing range of data. I would like excel to look at the data in A, (0,2 for example) then search through D to find which two values it lies between and then return the category that corresponds to that input. The category is the higher ranking number of the two values it lies between so a value of 0,1 for example is category 2. If it is equal to a limit it takes the category that corresponds to that limit, so 0,3 is category 2 for example. The result output I would like to see in col E would read 2, 3, 3. What is the best function to use to achieve this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between category limits
I do hope there is a more elegant answer but here is a start:
=IF(INDEX($C$1:$C$20,MATCH(A1,$C$1:$C$20,1))=A1,IN DEX($B$1:$B$20,MATCH(A1,$C$1:$C$20,1)),INDEX($B$1: $B$20,MATCH(A1,$C$1:$C$20,1)+1)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "LiAD" wrote in message ... Hi, I hope this description will be clear, please forgive me if its not. I do not know whats the easiest or neatest way to achieve this. I have a list of data composed of input numbers, then a category column then a category limits column, such as A C D 0,2 1 0 0,4 2 0,3 0,6 3 0,6 4 0,9 So i have input data in A. In B I have a category, which is numerical. In D I have a regularily increasing range of data. I would like excel to look at the data in A, (0,2 for example) then search through D to find which two values it lies between and then return the category that corresponds to that input. The category is the higher ranking number of the two values it lies between so a value of 0,1 for example is category 2. If it is equal to a limit it takes the category that corresponds to that limit, so 0,3 is category 2 for example. The result output I would like to see in col E would read 2, 3, 3. What is the best function to use to achieve this? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between category limits
Thanks to all for the replies. I used this one and it worked and I dont need
to re arrange tha data. Thanks for the tips though. I've stayed away from the lookups in the past, or just never needed thm actually but i should look at them sometime too. Thanks "Mike H" wrote: Hi, I assume that's a typo where in your data table the categories are shown in column C and you mean column B as in your narrative. Maybe this =OFFSET(INDEX($B$1:$B$4,MATCH(A1,$C$1:$C$4)),1,0) Drag down as required. You don't say what you would want for (say) 0.3 so I have assumed the higher category. Mike "LiAD" wrote: Hi, I hope this description will be clear, please forgive me if its not. I do not know whats the easiest or neatest way to achieve this. I have a list of data composed of input numbers, then a category column then a category limits column, such as A C D 0,2 1 0 0,4 2 0,3 0,6 3 0,6 4 0,9 So i have input data in A. In B I have a category, which is numerical. In D I have a regularily increasing range of data. I would like excel to look at the data in A, (0,2 for example) then search through D to find which two values it lies between and then return the category that corresponds to that input. The category is the higher ranking number of the two values it lies between so a value of 0,1 for example is category 2. If it is equal to a limit it takes the category that corresponds to that limit, so 0,3 is category 2 for example. The result output I would like to see in col E would read 2, 3, 3. What is the best function to use to achieve this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum, ave with limits, help pls. | Excel Discussion (Misc queries) | |||
Sum with limits | Excel Discussion (Misc queries) | |||
Query on Category & Sub-category | Excel Discussion (Misc queries) | |||
Row Limits | Excel Worksheet Functions | |||
Limits | Excel Discussion (Misc queries) |