# Solved: Excel 2007 not sorting properly



## Pegasustoo (Dec 5, 2009)

I'm a long time user of Excel but I can't seem to get my spreadsheet to sort properly and I've never seen this problem before. I don't see it when I search this forum so I'll post the problem.

Excel 2007 on Windows Vista. I'll try to upload a sample spreadsheet.

I have a list of simultaneous events, located in Toronto Canada, London UK and Sydney AU. The same type of event may repeat at a later date. So Event-1 might occur on Jan.01 and then again on Apr.10.

I want to first group the same events that are happening simultaneously (i.e. within 24 hours of each other). Then I want to sort them into a specific order by location: Toronto, then London, then Sydney. 

To group the events occurring simultaneously, I've created a column that converts the event times to GMT/UT (London time). The GMT date/time is derived from the local date/time, which is derived by combining date values from two columns: a date and a time. I sort first on this column. So far so good.

Every event has 3 occurrences: one for each of the 3 locations. To sort the 3 occurrences of each event in location order, I've tried a few things and am now just trying to get it to work the simplest way: sorting in alpha order using location names 1-Toronto, 2-London and 3-Sydney. 

It "should" work if I use a custom sort, sorting first by GMT time, then add a second sort layer to sort in alpha order on the location column.

The problem is that the second level sort (location) is not sorting accurately, nor even consistently. It's all over the place, but the first level sort (GMT date/time) seems to be fine.

I've double-checked to make sure that the values of the event dates are the same for each simultaneous event (they are). It's driving me nuts because I can't figure out why it's not sorting on the second level sort.

Any help would be most appreciated. Thanks in advance!


----------



## The Villan (Feb 20, 2006)

I have split the number and the text into seperate columns - see attached. I have used a lookup table which is sheet2 to put teh City in once the number has been typed into Column E

On sorting it,based on the City, the last 3 records appear to be the same, but I suspect that the GMT result for Sydney is not entirely the same as it sorts in front of London and Toronto. So it would be worth looking at that.


----------



## Pegasustoo (Dec 5, 2009)

Hi Villan,

Thanks very much for responding. Your idea of using a vlookup table to code the locations is what I actually tried first and it doesn't work. It mixes it up in the same way. Using 1-Toronto, 2-London, 3-Sydney was my last resort which I hoped would sort properly but it still doesn't want to work.

Like you, I wondered if maybe the GMT for each location might be slightly different, since the dates are derived, which might explain the weird sort results. That's why I put in that GMTDateValue column, which simply repeats the date number that is found in the GMTDateSort column. Then the column next to that, SameOrDifferent, tests to see if each GMTDateValue is the same as the one above it. When it's the same as the above, SameOrDifferent is blank; when it's different, it says "new value." 

Bottom line: the GMT dates for each event of the same "cluster" are indeed the same. They should therefore sort correctly. However, when I tried sorting first on GMTDateSort, then on Lcode (your location code), it's still all jumbled up. It should go 1, 2, 3, 1, 2, 3 right down the column, but it doesn't. It still groups each event "cluster" correctly (all simultaneous Event-1's together, Event-2's together, etc.), which is always did, but it fails to sort the location within each cluster.

Did you get different results when sorting than I did?

I've even tried doing the second sort by the LocalDateSort, since that should produce the same results, but it still won't work. I'd say there's something funky about doing a second sort, period, but when I try sorting (for example) first by Lcode, then by LocalDateSort, it sorts correctly.

I can't explain it, and I'm stumped on how to fix it. Any other ideas to try? I hope? 

Pegasustoo


----------



## The Villan (Feb 20, 2006)

Leave it with me. I will do some interogation on it.

its 1:05 am and I have just got home, so am off to bed. Just give me a bit of time and I will see where I get to.


----------



## Pegasustoo (Dec 5, 2009)

Thanks Villan! I'll look forward to seeing what you find. 

Peg


----------



## The Villan (Feb 20, 2006)

OK had a look and it is to do with the calculation in the background. you are using fractions for calculation and although the result looks OK, Excel can have the odd blip. So to overcome these issues you use the ROUND function. It strips the result and rounds it to the number of decimal places you want.

In Column H I put an =Round(xxxxx,4) around you formula and it sorts perfect for me. I used ,2 and ,3 and they worked fine as well.

So change H2 to

=ROUND(G2+(IF(D2="UT",0,VLOOKUP(D2,zones,2)*VLOOKUP(D2,zones,3))),4)

And then copy it down the rest of the column. and then try a sort.

Get back if it doesn't work.


----------



## Pegasustoo (Dec 5, 2009)

Hi Villan,

Thanks very much for your suggestion. It made sense, what you suggested, but unfortunately it still didn't work for me. What happened was that when the time zone changed (between Standard Time and Daylight Savings Time), for some reason it threw off the GMT date (I still have no idea why). No matter how many decimal points I rounded the date off to, it wouldn't calculate an accurate GMT date, and the more decimal places, the worse the distortion seemed to get. No matter how many ways I tinkered with it, Excel just won't seem to calculate accurate dates and time in all cases.

After huge hassle, I finally got it to work. I'll explain what I did and upload a sample spreadsheet, in case anyone else has the same problem and finds this post. I think the problem is that Excel just handles dates and times really poorly, and as you pointed out, its time calculations can easily go wonky.

I wound up having to do the time calculations manually that Excel "should" be doing but apparently isn't. I started by extracting the hour, minute and second as integers. I then adjusted the time zone hour manually (adding or subtracting depending if the city is east or west of Greenwich). That is, I didn't just let Excel's date values do it for me (since they don't seem to be consistent or accurate). 

After adding or subtracting the time zone adjustment, I adjusted the date if needed (since the event moment might be a day ahead or behind GMT). I rebuilt the time by concatenating the hour, minute and second. Then I created the whole date and time using the original date and the timevalue of the concatenated time. Using this sequence, when I sort first by the GMT Date Sort column, then by the Lcode (location code) column, it works perfectly, both in my example spreadsheet and on the real data.

Hopefully this might help someone else avoid putting their head through a wall trying to figure out how to get Excel to do something it's not able to do accurately.

Again, thanks very much for your help, Villan! Maybe you have other comments, observations or suggestions that could make it easier, but that's the only way I could find to make it work.

Peg


----------



## The Villan (Feb 20, 2006)

I would suggest that you go to the article I have given a link for below. It explains the issues to do with precision of calculation. Gets a bit involved, but read it through as there are examples of the issues.
This snippet from there is what determines the accuracy.
Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floating-point numbers. IEEE is the Institute of Electrical and Electronics Engineers, an international body that, among other things, determines standards for computer software and hardware. The 754 specification is a very widely adopted specification that describes how floating-point numbers should be stored in a binary computer. It is popular because it allows floating-point numbers to be stored in a reasonable amount of space and calculations to occur relatively quickly. The 754 standard is used in the floating-point units and numeric data processors of nearly all of today's PC-based microprocessors that implement floating-point math, including the Intel, Motorola, Sun, and MIPS processors.
So as such, blaming (not meaning you) Excel 2007 or Microsoft is probably not fair.

Anyway this is the link to this article

http://support.microsoft.com/kb/78113

It doesn't help you of course, but it may help the understanding.


----------



## The Villan (Feb 20, 2006)

Had a look at what you did and if you have read the article in the previous post, you will by now understand theproblem, or hopefully you do.

With that in mind, it is quite common to devise your own way to overcome, these issues, as you have done.

What I give you 10 out of 10 for, is that you have recognised in your own spreadsheet, that soemthing is not working the way you would like it to and have not given up on finding a solution.

I have always been very critical of people who design spreadsheets and do not test out fully the results of their efforts. Consequently, there are possibly millions of spreadsheets in the world that are inaccurate and people are making decisions on them.

You can actually create your own custom functions that can be used in Excel 2007. It is quite complex, but when done, can make life easier. I used to use them many years ago, but no longer have the need to do anymore. Consequently I have forgotten most of that, but here is a link to demonstrate how you would go about that.

http://support.microsoft.com/kb/248822/en-us


----------



## Pegasustoo (Dec 5, 2009)

Hi Villan,

Wow, 10 out of 10 -- thanks! And I didn't even know there was going to be a test! ;-) Seriously, it's always nice to have one's hard work be acknowledged by someone who appreciates what it involved. 

Thanks very much for the links (both). The article on the IEEE 754 specs and Excel makes sense and brings me back to the days when I learned Assembler a few eons ago. 

I seem to recall running into this issue with Excel in the past, probably trying to do the same kind of thing for previous years or other spreadsheets. I'm not sure what I did in previous years -- probably fudged it by just hard-copying the date and time for each cluster or something like that (which hopefully others do rather than just leaving their spreadsheets with inaccurate data). I guess this year I decided to tough it out until I got some kind of solution. 

You're right that it's not an Excel issue, per se, but it's still MS's choice to not provide time/date functions that would allow more accurate time-related calculations. For that they get a thump on the head from me.

Thanks also for the article on the custom functions. I remember seeing that when I upgraded to 2007 and was very interested, but never found the time to really look into it (or possibly started into it and found it was going to be more time consuming than I had time for). I'll have to check that out again, since custom functions for time-related data would really come in handy.

Thanks very much for your help! 

Peg


----------



## The Villan (Feb 20, 2006)

Peg
You are welcome 
Suppose we mustn't let MS completely off the hook 
Not sure if you are going to call it a day on this thread or wait to see if any bright guru out there comes up with a spanking good idea/solution.
If you think this thread is exhausted, would you scroll to the top of the thread and click on the solved button please.
Cheers
Les


----------



## Pegasustoo (Dec 5, 2009)

Oh, sure thing, Villan. Thanks for mentioning it. 

Peg


----------

