Help please! I'm going a little crazy;
I have created a large IF and 'Between' statement that includes values from 2 different sheets to return certain values based upon where they sit within certain ranges. Every cell in the column has the below formula:
=IF(AND(Scoring!$Q2>=Ranges!$J$3,Scoring!$Q2<=Ranges!$K$3,Scoring!$V2>=Ranges!$J$4,Scoring!$V2<=Ranges!$K$4),"1",IF(AND(Scoring!$Q2>=Ranges!$J$5,Scoring!$Q2<=Ranges!$K$5,Scoring!$V2>=Ranges!$J$6,Scoring!$V2<=Ranges!$K$6),"2",IF(AND(Scoring!$Q2>=Ranges!$J$7,Scoring!$Q2<=Ranges!$K$7,Scoring!$V2>=Ranges!$J$8,Scoring!$V2<=Ranges!$K$8),"3",IF(AND(Scoring!$Q2>=Ranges!$J$9,Scoring!$Q2<=Ranges!$K$9,Scoring!$V2>=Ranges!$J$10,Scoring!$V2<=Ranges!$K$10),"4",""))))
You can see that there are four outcomes: 1,2,3,4. So far so good and the formula works and appropriately returns the right value. What has been driving me crazy is that I now want to sort these values in numerical order eg 1,2,3,4 and the sort is not working for me. I should say that some of the cells in the column that I want to sort are blank. The list seems to sort/move but not in numerical order. I've tried lots of different things eg; filter out the blanks and then sort (obviously), then even though excel recognizes these as 'numbers' I created a list and pointed the [Value_if_true] at a list rather than displaying the integer in quotation marks, I've also created a custom list to sort by, I've created a helper column to try to extract the number as an absolute value, to then sort that column....and many other things and I'm going a little crazy.
If it matters I'm on a Mac and using Excel Version 16.65
Why can I not sort the values of this formula?
