| Home |
| Search |
| Today's Posts |
|
#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 |
| 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) | |||
Threaded Mode
