Rankings

Hi there,

I have more issues with this spreadsheet, which is beginning to drive me bonkers. Its a rank issue, with a few problems, questions:

Looking at the second table on the page, I want to rank the data for all colums except for column I in a descending fashion. IE - Highest to lowest. But column I needs to be ranked lowest to highest. I think I did this correctly, but I could be wrong.

Is it possable for the rankings to record equals? IE, the TO column has that, but in REB there should be another tie (9th and 10th) but there is not.

Further to this, the third table should have I28, and E35 listed as Ties. How do I do this for the entire table?

Any help and ideas will once more, be appreciated greatly.

Thanks.

I can't see the actual data in the first table, just the links to a remote sheet. Please confirm that cells J2 and J10 are completely equal, and neither of them have a small difference in them like 27.4002 or something. Otherwise the second table seems fine.

In the third table your IF statement doesn't allow for ties. So in cell I28 for example (and you'll need to copy it the rest of the table) try this instead:

Please Login or Register  to view this content.

HTH

Thank you, I though a simple cut paste onto a seperate sheet would do it, as its kind of a table for my entire spreadsheet, which is a good 6 meg now of data!

Anyways, thanks for the tie input, each cell is the same and it isnt doing the rankings correctly still. Is there a way I can show you without getting the linked data instead?

1. select the first table;
2. click copy;
3. (while first table is still selected) click Edit > Paste Special > Values.

That should get rid of the links, and leave just the values.

ok, uploaded sample and highlighted the equal values, hope this helps.

Thanks for sending the second version, but it hasn't helped to solve the mystery.

Continuing to work with the first sample you posted, I did discover that if you select cells B2 to W11, copy, then paste-special-values back into the same range, then the "error" in cell E16 is corrected. I just don't know why. It still leads me to suspect the remote source of data, since deleting the link eliminates the problem.

I don't see anything wrong with your formulae in the second table.

Please disregard my previous suggestion for the formula in cell I28, instead I think it should be:

Please Login or Register  to view this content.

(and copied to the rest of the third table). This is based on what I'm guessing that your trying to do with this table.

I've attached a copy of your first sample with the changes described above.

I hope what little I've done here has helped.

Would it be easier if I send you the file itself? Its large, just under 6 meg to look at all the original source?

I mean, some of these columns are correct, being 3 x ranked 6th, but in the SAME section it misses another tie. It's really confusing.

I don't think the forum server would allow that large a file, besides, it may not provide the solution either. Finding the correct formulas is the first concern.

Which cell/cells (in the Jabbitar.xls file I posted) is missing the tie?

I apologise, its different as I was trying new things and added in more cats that were ties. I wont add any more, I promise!

New sample has all the ties (at least one in each stat) and if you look at 3pm and TO, you will see it sort some correctly, and some not. But all the formulas that were used to get them was a straigh C/p from a master sheet, that was a straight C/P from a website's numbers.

Okay, when I copied my formulas from Jabbitar.xls into your latest Sample.xls, all the ties you hi-lited correctly appeared.

So the formulas are correct, it's just the source of the data in the first table that appears to give you difficulty. I recommend you use the "Edit > Paste Special... > Values" when you enter your raw data into the first table.

OK, I'll do that from now on, as the data has changed as of now, what if I go over the data with the same numbers, vut just type it in. Would that work as a for now stopgap, or should I copy the data again *I REALLY dont want to have to do that as of now*

Hey! I found a tip in another thread that may work for you.

Go to Tools > Options > Calculation, and set "Precision as displayed". Accept the warning about the loss of accuracy. The formulas should then work.

Its perfect!!!

Thank you so much, I think you have fixed my spreadsheet, making it complete, until I decide to mess with it some more!