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
Okay.
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.
