Ranking and sorting

[FOXYWALES]

Hi, As a newbie I'm asking for some help please. I have created in Calc a spreadsheet which has a list of names in column A2 to A100. I also have several number columns representing activities and which points can be added for each name. I have included columns for Total and Position. In the total column I have included a SUM in order for the Total column to show the relevant total of points in a range of cells for each name, i.e. for first name from E2 to S10 and for second name from E3 to S10 and so on for each name.
What I want to do is to get the 'Total' column to automatically update the Position column to show the position of each name from 1 at he top down to 100 and also if possible to update the 'Name' column with the highest position at A2 down to the lowest position at A100.
I really don't know if this is possible and as a newbie I also don't know where in the tutorials to start looking, so any help would be very gratefully received.
Thanks in advance and sorry for my lack of knowledge.

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[FJCC]

Sorry, I am not sure what you want to do.

First, I assume your total column is the sum of each row, so from E2 to S2. You wrote E2 to S10 but I don't see how that would work.

Is your position column the rank of the total score, so that the highest score gets a rank of 100 or 1? If that is what you want, take a look at the RANK() function. If you want the highest score to get the highest rank, the formula would be like

Code: Select all

=RANK(A2;$A$2:$A$100;1)

If you then want to reorder the rows to get the highest rank at the top, select all of the cells and then use the menu Data -> Sort.

[FOXYWALES]

Hi sorry I did make a mistake however your explanation seems fairly clear, I’m not at home to have look and try this out but will do so tomorrow and let you know. I’m very pleased that you found the time to help me.
I’ll get back tomorrow.
Thanks again.

[FOXYWALES]

HI,
I have had a look and although I understand the formula I am not sure where the formula is placed to achieve the result.
I have attached a sample so that I can explain a little better.
Cells E2 - S2 are the points awarded to each name in column A2 - A100.
I have put the formula in for auto update of totals, in column C, for each of the Names.
In column B I want the RANK function to auto show highest total at the top lowest at the bottom.
Then the SORT function to auto re-order the Names column to match the Total column C.
I would be very grateful if you could explain where they are placed to achieve the above if it can be done please.
Many thanks.

[FJCC]

The attached file shows what I understand to be your request. After inserting the RANK() function in column B, I selected the cells A1:S11 and then did the Sort using column B as the Sort By column. Since your data are sparse, with empty cells beyond column H, it is important to explicitly select the range to sort. I understand your real data may be a more complete set.

[FOXYWALES]

Many thanks for the quick reply, I now understand the function for the RANK, but cant quite see where the SORT function is placed or works. I will never need to add any further cells containing data rows than A2 to A100 and the columns up to S2 to S100. Can the example you provided be modified to cover what you have done to apply to all of the cells so that when a new name is entered and new points are added to the relevant columns, that they will be auto updated please? As I say I know how the function Rank is entered and works but the SORT is still a bit of a mystery.
Sorry if I am being a little obtuse here, your help is very much appreciated.

[FJCC]

Sorry I was unclear. There is no sort function that goes in a cell. Functions in cells cannot change the position of cells. The sort is done by highlighting all of the cells A1:S100 and then choosing the menu Data -> Sort. You can make that a little quicker by defining the cells A1:S100 as a data range. To do that, select all of those cells and then go to the menu Data -> Define Range. Give the range a name and then click OK. With the cells still selected, go to the menu Data -> Sort, choose to sort by the Position column and, on the Options tab, make sure the Range Contains Column Labels box is checked. Click OK and the data will be sorted. Any future sorts can now be done by clicking on any cell in A1:S100, no need to select the whole range, and choosing Data -> Sort. Calc will remember how the sort was set up for this defined data range.

[FOXYWALES]

Ah, I see, that now mgkes sense to me. Many thanks for your kind and understanding help, very much appreciated and spurred me on to do a bit more in depth study of the workings of Calc.
Thanks again.