Sorting large IF AND Between statement issue

New Contributor

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:




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?


3 Replies


After you have all your formulas in the rows, you have to set a fixed referencing. So Scoring!$Q2 must become Scoring!$Q$2. Do the same with column V. You can easily do this automatically for all formulas at the same time by using the Find and Replace dialog.

Search: Scoring!$Q

Replace: Scoring!$Q$

Replace all
After that, the sorting should work.


Thank you @dscheikey
I did what you suggested and still no luck. Any other thoughts? Is it perhaps something with using "1", vs an actual number?


That cannot be the reason. Text is also sorted. Excel interprets the text as a number.

Can you upload an example of your file? Please remove data that is not intended for the public. You can also send it to me as a direct message.