0

I make two sheets : one called 'Result' which tracks values between 0 and 100 and another one called 'range' which display a value if a result!value is in a specific range (0-14, 15-19, 20-24, ..., 76-80, 81-85, 86-100). On the 'range' sheet, I want to display the value linked to the correct 'result!value' range.

For instance, if range!value for 61-65 is 43, the formula should say 43 if result!value is between 61-65 (61,65 included).

I created the following formula, which works perfectly but is pretty ugly : 

=if(AND(Result!G3>85,Result!G3<100),C$61, if(AND(Result!G3>80,Result!G3<86),C$62,if(AND(Result!G3>75,Result!G3<81),C$63,if(AND(Result!G3>70,Result!G3<76),C$64,if(AND(Result!G3>65,Result!G3<71),C$65,if(AND(Result!G3>60,Result!G3<66),C$66,if(AND(Result!G3>55,Result!G3<61),C$67,if(AND(Result!G3>50,Result!G3<56),C$68,if(AND(Result!G3>44,Result!G3<50),100-C$68,if(AND(ResultG3>39,Result!G3<45),100-C$67,if(AND(Result!G3>34,Result!G3<40),100-C$66,if(AND(Result!G3>29,Result!G3<35),100-C$65,if(AND(Result!G3>24,Result!G3<30),100-C$64,if(AND(Result!G3>19,Result!G3<25),100-C$63,if(AND(Result!G3>14,Result!G3<20),100-C$62,100-C$61)))))))))))))))

The basic element from this formula is :

if(AND(Result!G3>x,Result!G3<y),range!value,if(AND(Result!G3>x-5,Result!G3<range!y-5))

With x=80 and y=86 (but the 86-100 range). When x>50, range!value becomes 100-range!value if 71-75 sends 43, 25-29 sends (100-43) = 57.

It checks every range then send the result if TRUE or try the following range is FALSE. I'm looking for a nicer and maybe shorter solution to my problem.

Improve this question
5
  • 2
    look into VLOOKUP(), it does right what you want.
    – Aganju
    Jul 28, 2016 at 11:42
  • VLOOKUP() needs an additional specific table for it, right ? Three columns : one for the min.value (included), one for the max. value (excluded) and one for the value to use. Is that right ?
    – MarkUs0
    Jul 28, 2016 at 11:53
  • No, VLOOKUP() can use a (sorted) table with two column, it interprets the first column as the cutting points. That assumes you have neither overlapping ranges nor holes, but that's typically the case. Having your cutoff points in a table is a clearer way than burying them inside the formula anyway; and you can put that table on a separate (even hidden) sheet, if you want them out of sight.
    – Aganju
    Jul 28, 2016 at 12:54
  • 1
    either VLOOKUP or MATCH probably will help you, please post some sample data, without that it's difficult to advice anything more specific
    – Máté Juhász
    Aug 3, 2016 at 12:58
  • agreed.. need sample data to assist.
    – p._phidot_
    Oct 10, 2018 at 2:56

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .