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
|