Hi.
I'm having trouble sorting values formated as percentvalues.
When using the sort function 9.5 is ranked higher then 70.5 for example.
Excel seems to sort based on the first digit only when it comes to values in percent.
Anyone have any solution for this subject.
I could program my on sort function in VBA I suppose but this is something Excel should manage...
I use Excel 2000
Last edited by svegust; 02-25-2008 at 03:52 AM.
Hi,
I don't understand your problem. If you're sorting in ascending order, then 9.5 is 'higher' (in the list) than 70.5 because it's smaller.
Am I missing something?
Rgds
Last edited by AliGW; 12-30-2017 at 03:00 AM. Reason: Unnecessary quotation removed.
Sorry, didn't add that I was trying to sort in descending order. Highst value at the top and so on...
If I have three numbers:
1.5%
9.5%
70.5%
then when I try to sort them in descending order it ends up as:
9.5%
70.5%
1.5%
Same thing happends if I try to sort in ascending order. Then 70.5 is higher in the list then 9.5.
I believe it has to do with the procent format as this doesn't happend with ordinary numbers.
Regards
Last edited by AliGW; 12-30-2017 at 03:00 AM. Reason: Unnecessary quotation removed.
Hi,
I don't know why your sorting looks like it. But seems to be it is sorted like a text (not a number). Actually, we can choose the option, "sort the text like number".
Regards,
Thanks for your reply. Where can that option be found?
I've look in all the settings and menus.
Regards
Last edited by AliGW; 12-30-2017 at 03:00 AM. Reason: Unnecessary quotation removed.
Hi,
You must update excel version to get them. Excel 2003 (Minimum) should solve your problem.
Last edited by AliGW; 12-30-2017 at 03:01 AM. Reason: Unnecessary quotation removed.
Thanks for your answer.
A workaround could maybe be to change the type to number, then sort them and finally change back to percent?!
Edit:
I ended up sorting all values in the VBA-code before setting them as format "0.0%".
As I didn't use the ordinary "procent"-format that might have caused my problem. (Should have mentioned that from the beginning but it didn't strike me 'til now)
I still have the same problem if I want to resort the numbers after my macro's been run but it's not a significant problem to me...
Thanks for all the help!
Last edited by AliGW; 12-30-2017 at 03:01 AM. Reason: Unnecessary quotation removed.
I was having the same problem (Excel 2007). It seems that Excel cannot sort on a formula. (Actually when I think about it, that makes perfect sense: how could Excel sort on a formula? It needs values.) In my case, I realised this when I tried sorting on one column that wasn't a formula, and it worked fine.
Here's how I fixed it:
1. highlight the cells with formulae in them, cntl-c
2. place cursor on top cell in the same column, right click, paste special, choose 'values'
Essentially, I copied the formulae and pasted the values back into the same cells. Now it sorts fine.
Alternatively, I could insert a column and paste special there.
Do a search and replace to remove the percent sign first. Then do the same to replace any blanks (in both cases replace with nothing).
Check to see if your values are now numbers (e.g. Sum them). If they are, then highlight that column/row and format the cells to Percent.
If they are off by a factor of 100, then in an empty cell type 100 and copy, highlight your values again and paste special > Divide.
Your values are now formatted as numbers and percent and can be sorted properly.
ChemistB
How wonderful to hear the problem identified (sorting on formulae not percentage numbers) without simply putting forth general ideas like upgrade your version of Excel. I had the problem and inserted a column beside my percentages column and formatted it as a number with two more decimals positions than my percentage column. I then, as you said, Copied Values the percentage column to the inserted column and sorted on that column. I then hid the inserted column so I could use it for the next sort.
Thanx dhallett.
Last edited by mistophalees; 12-29-2017 at 08:09 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks