[SOLVED] Calculated column not sorting by largest to smallest

[essaytee]

I've created a spreadsheet that records my club's weekly run data for each member. The data is extracted from Strava, bit of a process, screenshot taken, then converted to PDF, then OCR'd and then to Excel (temporary sheet to iron out any OCR hiccups) then copied to the final Excel data file.

The raw data is: name, distance for week, number of runs, longest run, pace (string format ie "4:30 /km" and elevation. (Along with week ending date, always a Sunday)

I've added three additional columns to the final spreadsheet, all are calculated, TimeOnFeet (calculated by distance * pace - output "10:15:35"), Pace in Seconds (calculated from the Pace string, and Total Seconds (same as TimeOnFeet but seconds. Pace in Seconds and Total Seconds there for sorting purposes.

My problem is, I cannot sort from largest to smallest on either of the Pace Seconds or Total Seconds, the rows disappear completely. I have to then sort on A-Z to recover all the rows. The sort options for these two columns are A-Z, Z-A and not smallest to largest or largest to smallest as appear on the other number columns. The two columns are formatted as numbers with zero digits. For the two columns there is no green bit at the top left of the cells to indicate they are text.

Anyway, hope it wasn't a long preamble, but I'm at a loss, searching the internet the main piece of advice is to make sure they are numbers.

For the column, 'Pace to Seconds,' I do use a bit of vba, the formula is:

=IF(ISBLANK(G3),"",PaceToSeconds(G3))

For the column 'Total Seconds' the formula relies partly on the column 'Pace to Seconds'. The formula is:

=IF(ISBLANK(G3),"",D3 * J3)

Can anyone shed any light, please?

 

[essaytee]

Update:

Well, don't I feel like a complete dill. I applied the largest to smallest sort on the affected columns and the same result, rows disappear. I scrolled down many, many rows and there were the other rows, in largest to smallest order.