Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Numbers will not sort numerically in formatted cells with if formula
Solved by S. H. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 05/09/2018 - 12:08
Welcome to got it pro. We are delighted to have you here with us.
Excelchat Expert 05/09/2018 - 12:10
You need help with a sorting problem in excel, right?
User 05/09/2018 - 12:10
Hello. I have a column of cells that have a complicated/ length if formula that are also conditionally formatted to change the cells to a colour dependent on their value. However when I try to sort them numerically they don't sort correctly.
Excelchat Expert 05/09/2018 - 12:10
May I see the file please?
User 05/09/2018 - 12:11
sorry it contains confidential info I can copy the formula
User 05/09/2018 - 12:11
=IF(AND(Register!$O60=1,Register!$P60=1),1,IF(AND(Register!$O60=2,Register!$P60=1),2,IF(AND(Register!$O60=3,Register!$P60=1),4,IF(AND(Register!$O60=4,Register!$P60=1),7,IF(AND(Register!$O60=1,Register!$P60=2),3,IF(AND(Register!$O60=2,Register!$P60=2),5,IF(AND(Register!$O60=3,Register!$P60=2),8,IF(AND(Register!$O60=4,Register!$P60=2),11,IF(AND(Register!$O60=1,Register!$P60=3),6,IF(AND(Register!$O60=2,Register!$P60=3),9,IF(AND(Register!$O60=3,Register!$P60=3),12,IF(AND(Register!$O60=4,Register!$P60=3),14,IF(AND(Register!$O60=1,Register!$P60=4),10,IF(AND(Register!$O60=2,Register!$P60=4),13,IF(AND(Register!$O60=3,Register!$P60=4),15,IF(AND(Register!$O60=4,Register!$P60=4),16,"Not Scored"))))))))))))))))
Excelchat Expert 05/09/2018 - 12:11
The formula is correct, right?
User 05/09/2018 - 12:12
the first and second columns has values 1 - 4 then then this gives numbers 1 - 16 yes the formula works and the conditional formatting woks correctly it does this dependent on value into 4 colours
User 05/09/2018 - 12:13
it is purely when I have tried to sort the data it will not sort correctly 1 - 16
Excelchat Expert 05/09/2018 - 12:13
Usually it is hard to solve any sorting related problem without looking at the file.
Excelchat Expert 05/09/2018 - 12:14
However I will give it a try. Please use this formula =VALUE(IF(AND(Register!$O60=1,Register!$P60=1),1,IF(AND(Register!$O60=2,Register!$P60=1),2,IF(AND(Register!$O60=3,Register!$P60=1),4,IF(AND(Register!$O60=4,Register!$P60=1),7,IF(AND(Register!$O60=1,Register!$P60=2),3,IF(AND(Register!$O60=2,Register!$P60=2),5,IF(AND(Register!$O60=3,Register!$P60=2),8,IF(AND(Register!$O60=4,Register!$P60=2),11,IF(AND(Register!$O60=1,Register!$P60=3),6,IF(AND(Register!$O60=2,Register!$P60=3),9,IF(AND(Register!$O60=3,Register!$P60=3),12,IF(AND(Register!$O60=4,Register!$P60=3),14,IF(AND(Register!$O60=1,Register!$P60=4),10,IF(AND(Register!$O60=2,Register!$P60=4),13,IF(AND(Register!$O60=3,Register!$P60=4),15,IF(AND(Register!$O60=4,Register!$P60=4),16,"Not Scored")))))))))))))))))
Excelchat Expert 05/09/2018 - 12:14
And then try sorting the file again.
Excelchat Expert 05/09/2018 - 12:16
Let me know if that doesn't work. We will try other solutions in that case.
User 05/09/2018 - 12:16
I have just tried this and it hasn't worked
Excelchat Expert 05/09/2018 - 12:18
Here is what we can do. You can strip all columns Excepts column O and column P and the result column AND send me the file. That way your sensitive data will be protected but at the same time I would be able to look at the file.
Excelchat Expert 05/09/2018 - 12:18
How does that sound?
User 05/09/2018 - 12:18
Im an just trying to do this
Excelchat Expert 05/09/2018 - 12:20
Hmm, what is that you are trying to say again?
User 05/09/2018 - 12:21
I am just trying to copy the columns across to other spreadsheet however the original is a large document so it is slow
Excelchat Expert 05/09/2018 - 12:22
I see. However copying might not reveal the problem since google sheet often doesn't inherit all formatting from excel file.
Excelchat Expert 05/09/2018 - 12:25
Please look at column H and I in preview window.
Excelchat Expert 05/09/2018 - 12:26
I'm trying to replicate the formula in a table.
Excelchat Expert 05/09/2018 - 12:26
Did I get it correctly?
User 05/09/2018 - 12:27
Yes that looks correct sorry I have copied the datainto another spreadsheet but as just altering the formula as it ref another workseheet
Excelchat Expert 05/09/2018 - 12:29
We can wait for the sheet or I have wrote another formula that we can try using in your file.
User 05/09/2018 - 12:32
I can try the formula in the meantime
Excelchat Expert 05/09/2018 - 12:32
Sure, go ahead.
User 05/09/2018 - 12:34
which formula am I trying
Excelchat Expert 05/09/2018 - 12:35
We need to create the table in column H:J. Then use the formula =INDEX($J$2:$J$17,MATCH(1,INDEX(($H$2:$H$17=E3)*($I$2:$I$17=F3),0,1),0)) assuming inputs are in E3 and F3.
User 05/09/2018 - 12:37
I have just tried sorting the data in the new spreadsheet and it works correctly
Excelchat Expert 05/09/2018 - 12:38
Great. Wow, that is fantastic.
Excelchat Expert 05/09/2018 - 12:39
Then it seems there was problem with formatting of the column in original file.
User 05/09/2018 - 12:39
sorry I meant the data I copied across with the original formula and formatting sorting the data correctly so does that mean their is a problem wiothin the original spreadsheet
Excelchat Expert 05/09/2018 - 12:40
Yeah, this confirms there was problem with formatting of original sheet. That was the reason I was asking for the file.
User 05/09/2018 - 12:40
The only difference between the original formula and the new formula is the "Register!" part could that cause the problem
Excelchat Expert 05/09/2018 - 12:41
Nah, register is the sheet name.
Excelchat Expert 05/09/2018 - 12:41
It doesn't make any difference.
User 05/09/2018 - 12:43
sorry just cleared data to try and upload but is saying file is to large just going to delete some lines to see if it helps
Excelchat Expert 05/09/2018 - 12:43
Excelchat Expert 05/09/2018 - 12:43
Please remember that we are in the last 24 minutes of the chat.
Excelchat Expert 05/09/2018 - 12:46
Just want to make sure, while trying to sort, you did select all your data, not only the column which contains formula, right?
User 05/09/2018 - 12:48
[Uploaded an Excel file]
User 05/09/2018 - 12:48
Finally cleared the extra stuff to make it small enough
Excelchat Expert 05/09/2018 - 12:48
Thank you.
Excelchat Expert 05/09/2018 - 12:48
Let me take a look at it.
Excelchat Expert 05/09/2018 - 12:50
Ah, I don't see any problem. It is sorting perfectly.
Excelchat Expert 05/09/2018 - 12:50
Oh wait. I see those now.
Excelchat Expert 05/09/2018 - 12:50
At the end of the file.
User 05/09/2018 - 12:50
yeah it starts from 1 again part way through
User 05/09/2018 - 12:56
I have noticed on mine that the numbers in O and P change to 1 aswell when the data sorts
Excelchat Expert 05/09/2018 - 12:57
Changing the IF formula worked.
Excelchat Expert 05/09/2018 - 12:58
I'm sharing the file in a couple of minutes.
Excelchat Expert 05/09/2018 - 01:00
[Uploaded an Excel file]
Excelchat Expert 05/09/2018 - 01:00
Please download and see the file.
Excelchat Expert 05/09/2018 - 01:00
I have used the formula =INDEX($Q$2:$Q$17,MATCH(1,INDEX(($O$2:$O$17=F2)*($P$2:$P$17=G2),0,1),0))
Excelchat Expert 05/09/2018 - 01:01
For this formula to work, I had to create the Likelihood/Impact table in column O:Q
User 05/09/2018 - 01:01
can I have that table on another worksheet or will it have to be on the same one
Excelchat Expert 05/09/2018 - 01:01
This is a dynamic formula, so for example if you decide to change the outcome of a likelihood/impact result, you just have to edit the table.
Excelchat Expert 05/09/2018 - 01:01
You have place the table anywhere you want.
User 05/09/2018 - 01:02
would I just need to link the worksheet before each cell references
Excelchat Expert 05/09/2018 - 01:03
Yeah. I'm sending a file for that too.
User 05/09/2018 - 01:05
I presume the conditional formatting will still work with the index formula
Excelchat Expert 05/09/2018 - 01:06
[Uploaded an Excel file]
Excelchat Expert 05/09/2018 - 01:06
Yeah, absolutely. The conditional formatting would work just fine.
Excelchat Expert 05/09/2018 - 01:07
Now I have created the same table in sheet1, so the formula is =INDEX(Sheet1!$C$2:$C$17,MATCH(1,INDEX((Sheet1!$A$2:$A$17=F2)*(Sheet1!$B$2:$B$17=G2),0,1),0))
User 05/09/2018 - 01:07
That's great thank you I will try it on the original spreadsheet. Thank you again
Excelchat Expert 05/09/2018 - 01:07
Thanks for your patience. Have a great day ahead!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc