Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
I would be very grateful if someone could provide me with a formula that will
look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
I'm sure there are more elegant solutions, but try this...
Assuming your lookup data is A1:D10, the type of interval (ARC) to look up is in A15, and the value to look up is in A16: In the cell where you want to return the answer, enter =INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$10=A15),--($B$2:$B$10<=A16),--($C$2:$C$10=A16),ROW($A$2:$A$10)),4)) Adjust the cell references as needed. Hope this helps, Hutch "Christine" wrote: I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
Hi,
The best way to do this is by having the minimum and maximum values in 2 different columns (say A1:A10 and B1:B10). Also in a third column (say C1:C10) enter numbers starting with 1 I.e. 1,2,3.... Now use the SUMPRODUCT() formula =sumproduct((A1:A10<=9923)*(B1:B10=9923),C1;C10) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Christine" wrote in message ... I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
Thank you, Tom. However, the result that comes back is Result 4 when it
should be Result 2. I've also tried it with different references, but the result of the Address portion of the formula comes back with a reference a few rows below what it should be. Would you have any idea why that would be happening? "Tom Hutchins" wrote: I'm sure there are more elegant solutions, but try this... Assuming your lookup data is A1:D10, the type of interval (ARC) to look up is in A15, and the value to look up is in A16: In the cell where you want to return the answer, enter =INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$10=A15),--($B$2:$B$10<=A16),--($C$2:$C$10=A16),ROW($A$2:$A$10)),4)) Adjust the cell references as needed. Hope this helps, Hutch "Christine" wrote: I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
Thank you, Ashish. However, this brings back a result of 0.
"Ashish Mathur" wrote: Hi, The best way to do this is by having the minimum and maximum values in 2 different columns (say A1:A10 and B1:B10). Also in a third column (say C1:C10) enter numbers starting with 1 I.e. 1,2,3.... Now use the SUMPRODUCT() formula =sumproduct((A1:A10<=9923)*(B1:B10=9923),C1;C10) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Christine" wrote in message ... I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
Hi Christine
Tom's formula returns the correct result for me. The only way I can see it returning a value 2 rows below the value required, is if your data starts in Row 4, not in row 2 as Tom assumed. Change $2 to $4 throughout the formula, and see what that does. -- Regards Roger Govier "Christine" wrote in message ... I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
I don't know where my formula is wrong, because I still get the wrong result.
Please see below, where the data is from cells A1:D10, "ARC" in cell A15 and 9923 in cell A16. Minimum Maximum ARC 0 10,000 Result 1 ARC 4,000 10,000 Result 2 ARC 10,000 9,999,999 Result 3 ARD 0 10,000 Result 4 ARD 10,000 9,999,999 Result 5 DCC 0 15,000 Result 6 DCC 4,000 9,999,999 Result 7 DCC 15,000 Result 8 DCD 0 9,999,999 Result 9 ARC 9923 Result 4 =INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$10=A15),--($B$2:$B$10<=A16), --($C$2:$C$10=A16),ROW($A$2:$A$10)),4)) "Roger Govier" wrote: Hi Christine Tom's formula returns the correct result for me. The only way I can see it returning a value 2 rows below the value required, is if your data starts in Row 4, not in row 2 as Tom assumed. Change $2 to $4 throughout the formula, and see what that does. -- Regards Roger Govier "Christine" wrote in message ... I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
Actually, it seems to work for some entries but not others. I think the
formula does work. "Roger Govier" wrote: Hi Christine Tom's formula returns the correct result for me. The only way I can see it returning a value 2 rows below the value required, is if your data starts in Row 4, not in row 2 as Tom assumed. Change $2 to $4 throughout the formula, and see what that does. -- Regards Roger Govier "Christine" wrote in message ... I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
Your sample data is inconsistent. For example, ARC 9923 falls into your
Result 1 range and also into your Result 2 range. The DCC ranges don't make sense. I adjusted the data to the following when I was creating the formula: Minimum Maximum ARC 0 4,000 Result 1 ARC 4,000 10,000 Result 2 ARC 10,000 9,999,999 Result 3 ARD 0 10,000 Result 4 ARD 10,000 9,999,999 Result 5 DCC 0 4,000 Result 6 DCC 4,000 15,000 Result 7 DCC 15,000 9,999,999 Result 8 DCD 0 9,999,999 Result 9 Could that be why you are getting erratic results? Hutch "Christine" wrote: Actually, it seems to work for some entries but not others. I think the formula does work. "Roger Govier" wrote: Hi Christine Tom's formula returns the correct result for me. The only way I can see it returning a value 2 rows below the value required, is if your data starts in Row 4, not in row 2 as Tom assumed. Change $2 to $4 throughout the formula, and see what that does. -- Regards Roger Govier "Christine" wrote in message ... I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
Unfortunately, there are other factors involved, and the limits cannot be
changed. It's also a matter of tiering. For example, if the building is less than 5 years' old and the amount falls in between 4000 and 10000, it gets tiered at level A; if the building is less than 5 years' old but the amount falls between 1000 and 10000, it gets tiered at level B, etc. with additional factors. What I want to do is concatenate all the factors and bring back the appropriate tiering, and that means finding the closest minimum and maximum based on the amount and the subject (e.g. ARC, DCC). "Tom Hutchins" wrote: Your sample data is inconsistent. For example, ARC 9923 falls into your Result 1 range and also into your Result 2 range. The DCC ranges don't make sense. I adjusted the data to the following when I was creating the formula: Minimum Maximum ARC 0 4,000 Result 1 ARC 4,000 10,000 Result 2 ARC 10,000 9,999,999 Result 3 ARD 0 10,000 Result 4 ARD 10,000 9,999,999 Result 5 DCC 0 4,000 Result 6 DCC 4,000 15,000 Result 7 DCC 15,000 9,999,999 Result 8 DCD 0 9,999,999 Result 9 Could that be why you are getting erratic results? Hutch "Christine" wrote: Actually, it seems to work for some entries but not others. I think the formula does work. "Roger Govier" wrote: Hi Christine Tom's formula returns the correct result for me. The only way I can see it returning a value 2 rows below the value required, is if your data starts in Row 4, not in row 2 as Tom assumed. Change $2 to $4 throughout the formula, and see what that does. -- Regards Roger Govier "Christine" wrote in message ... I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
Hi,
Try this =sumproduct((A1:A10<9923)*(B1:B109923),C1;C10) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Christine" wrote in message ... Thank you, Ashish. However, this brings back a result of 0. "Ashish Mathur" wrote: Hi, The best way to do this is by having the minimum and maximum values in 2 different columns (say A1:A10 and B1:B10). Also in a third column (say C1:C10) enter numbers starting with 1 I.e. 1,2,3.... Now use the SUMPRODUCT() formula =sumproduct((A1:A10<=9923)*(B1:B10=9923),C1;C10) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Christine" wrote in message ... I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
You are getting Result4 for ARC 9923 because it falls into the ranges for
both Result1 and Result2. The SUMPRODUCT part of the formula returns the row number of the solution. Because your min/max ranges are inconsistent, it is returning 2 and 3, then adding them to get 5. Row 5 has Return4. Hutch "Christine" wrote: Unfortunately, there are other factors involved, and the limits cannot be changed. It's also a matter of tiering. For example, if the building is less than 5 years' old and the amount falls in between 4000 and 10000, it gets tiered at level A; if the building is less than 5 years' old but the amount falls between 1000 and 10000, it gets tiered at level B, etc. with additional factors. What I want to do is concatenate all the factors and bring back the appropriate tiering, and that means finding the closest minimum and maximum based on the amount and the subject (e.g. ARC, DCC). "Tom Hutchins" wrote: Your sample data is inconsistent. For example, ARC 9923 falls into your Result 1 range and also into your Result 2 range. The DCC ranges don't make sense. I adjusted the data to the following when I was creating the formula: Minimum Maximum ARC 0 4,000 Result 1 ARC 4,000 10,000 Result 2 ARC 10,000 9,999,999 Result 3 ARD 0 10,000 Result 4 ARD 10,000 9,999,999 Result 5 DCC 0 4,000 Result 6 DCC 4,000 15,000 Result 7 DCC 15,000 9,999,999 Result 8 DCD 0 9,999,999 Result 9 Could that be why you are getting erratic results? Hutch "Christine" wrote: Actually, it seems to work for some entries but not others. I think the formula does work. "Roger Govier" wrote: Hi Christine Tom's formula returns the correct result for me. The only way I can see it returning a value 2 rows below the value required, is if your data starts in Row 4, not in row 2 as Tom assumed. Change $2 to $4 throughout the formula, and see what that does. -- Regards Roger Govier "Christine" wrote in message ... I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up a value within an interval
Thank you, Tom.
"Tom Hutchins" wrote: You are getting Result4 for ARC 9923 because it falls into the ranges for both Result1 and Result2. The SUMPRODUCT part of the formula returns the row number of the solution. Because your min/max ranges are inconsistent, it is returning 2 and 3, then adding them to get 5. Row 5 has Return4. Hutch "Christine" wrote: Unfortunately, there are other factors involved, and the limits cannot be changed. It's also a matter of tiering. For example, if the building is less than 5 years' old and the amount falls in between 4000 and 10000, it gets tiered at level A; if the building is less than 5 years' old but the amount falls between 1000 and 10000, it gets tiered at level B, etc. with additional factors. What I want to do is concatenate all the factors and bring back the appropriate tiering, and that means finding the closest minimum and maximum based on the amount and the subject (e.g. ARC, DCC). "Tom Hutchins" wrote: Your sample data is inconsistent. For example, ARC 9923 falls into your Result 1 range and also into your Result 2 range. The DCC ranges don't make sense. I adjusted the data to the following when I was creating the formula: Minimum Maximum ARC 0 4,000 Result 1 ARC 4,000 10,000 Result 2 ARC 10,000 9,999,999 Result 3 ARD 0 10,000 Result 4 ARD 10,000 9,999,999 Result 5 DCC 0 4,000 Result 6 DCC 4,000 15,000 Result 7 DCC 15,000 9,999,999 Result 8 DCD 0 9,999,999 Result 9 Could that be why you are getting erratic results? Hutch "Christine" wrote: Actually, it seems to work for some entries but not others. I think the formula does work. "Roger Govier" wrote: Hi Christine Tom's formula returns the correct result for me. The only way I can see it returning a value 2 rows below the value required, is if your data starts in Row 4, not in row 2 as Tom assumed. Change $2 to $4 throughout the formula, and see what that does. -- Regards Roger Govier "Christine" wrote in message ... I would be very grateful if someone could provide me with a formula that will look up the closest minimum and maximum based on a value and bring back the contents of a cell to the right. In the example below, I want the formula to look up 9,923, within the ARC intervals, and bring back Result 2. ARC 9,923 Formula will bring back Result 2 A B C D Minimum Maximum 1 ARC 0 10,000 Result 1 2 ARC 4,000 10,000 Result 2 3 ARC 10,000 9,999,999 Result 3 4 ARD 0 10,000 Result 4 5 ARD 10,000 9,999,999 Result 5 6 DCC 0 15,000 Result 6 7 DCC 4,000 9,999,999 Result 7 8 DCC 15,000 Result 8 9 DCD 0 9,999,999 Result 9 Thank you in advance for your help. Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how i create a interval | Excel Discussion (Misc queries) | |||
interval formula | Excel Discussion (Misc queries) | |||
Confidence Interval | Excel Worksheet Functions | |||
How to find row in interval? | Excel Discussion (Misc queries) | |||
interpolation in a particular interval (HELP) | Excel Discussion (Misc queries) |