New Contributor

# Sorting large IF AND Between statement issue

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?

3 Replies

# Re: Sorting large IF AND Between statement issue

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.

# Re: Sorting large IF AND Between statement issue

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?

# Re: Sorting large IF AND Between statement issue

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.