hello,
i'm having problems with the following formula:
it is used to take an index to average, and associate a "ranking" based on certain parameters (between 1-2, or 2-3, or 3-4, etc). I have used this formula in many instances with a lot of success, however, i'm having issues this one time, and can't figure out why. it's is only working when i have a value that is "greater than" (please see formula), and when the value is less than, it is giving me a "value" error, which seems to be derived from the "match" part of the formula.
=INDEX($B$21:$B$24,IF(B56>$H$21,1,MIN(MATCH((AY$19:AY$45),($H$21:$H$24),-1)+1,8)),0)
here is a "translation" of the formula:
=INDEX(value to be returned,IF(lookup value>highest # in range,1,MIN(MATCH((list of all lookup values),(minimums to qualify for the value to be returned),-1,+1,8)),0)
thanks for any help.
k
i'm having problems with the following formula:
it is used to take an index to average, and associate a "ranking" based on certain parameters (between 1-2, or 2-3, or 3-4, etc). I have used this formula in many instances with a lot of success, however, i'm having issues this one time, and can't figure out why. it's is only working when i have a value that is "greater than" (please see formula), and when the value is less than, it is giving me a "value" error, which seems to be derived from the "match" part of the formula.
=INDEX($B$21:$B$24,IF(B56>$H$21,1,MIN(MATCH((AY$19:AY$45),($H$21:$H$24),-1)+1,8)),0)
here is a "translation" of the formula:
=INDEX(value to be returned,IF(lookup value>highest # in range,1,MIN(MATCH((list of all lookup values),(minimums to qualify for the value to be returned),-1,+1,8)),0)
thanks for any help.
k