Formula to return which quartile a given number falls in

    I'm having some problems working out how to get excel to show me one value dependant on if another value is between two others, I've tried a nested IF formula, but I'm really struggling to get it to work.


    Here's the problem.


    I Have a list of figures between 0.1 and 1.0 (these figures are predominantly decimals such as 0.23, 0.61, 0.8 etc), and I want that to be converted to a number between 1 and 4 dependant on where they fall in the range.


    So if the number is between 0 and 0.25 it would equal "1"


    If the number is between 0.26 and 0.5 it would equal "2"


    If the number is between 0.51 and 0.75 it would equal "3"


    If the number is between 0.76 and 1.0 it would equal "4"


    I'm hoping there is a way to do this! Thanks very much in advance for all your help!


    Also I hope the thread title is okay! I don't really know how to phrase it!


    CCA BSM

    Re: show me one value dependant on if another value is between 2 others


    You can use a formula like this:


    =VLOOKUP(PERCENTRANK.INC({0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1},A1,2),{0,1;0.17,2;0.45,3;0.73,4},2,TRUE)


    Where A1 is the value between 0.1 and 1 that you are assessing.


    I've also changed the thread title to something a bit more search friendly :)


    Hope that helps,


    S O

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login