League Table position assignments

[harpscardiff]

Hi,

based on data users enter, the data is summarised into a tables.
Please note the totalmoney column will be a


Summary table
emp cat1 cat2 cat3 Totalmoney cat4
bob
graham
fred
gary
john
jo
bloggs



league table
emp| Total| Position|
bob| 12|
gra| 19| 3rd|
fred| 30| 2nd|
gary| 20|
john| 15|
jo| 17|
bloggs| 35| 1st|

(hope the table comes out correct)

Based on the summary table i have totalled up each row, see league
table.
What i want to happen is the position column to to be updated
automatically, to say 1st, 2nd and 3rd.
The users are going enter data on a daily basis, so if the position
changed dynamically it would be ideal.
I can't work out how to do the formula, so I get dynamic results.

Please help

If you need more information let me know.

Thanks

 

[SIR Knight]

Harpscardiff,

There is a function called RANK, you can use as follows assuming
Col A = Name
Col B = Number
Col C = Position

In Col C use the formula:

=Rank(B2,B2:B10)

This will generate a number ie 1 as the top rank etc.

To use this for generating "1st", "2nd" etc combine with an IF. The
below only gives an answer if the position is above 4

=IF(RANK(B2,B2:B10)>4,"",IF(RANK(B2,B2:B10)=3,"3rd",IF(RANK(B2,B2:B10)=2,"2nd",IF(RANK(B2,B2:B10)=1,"1st"))))

change the cell references for the ones you need in your spreadsheet

This should work provided you position table has volumes that update
automatically

Steve

 

[SIR Knight]

Harpscardiff,

I forgot to mention to absolute the range so use $B$2:$B$10, and I
spotted I had put ">4" instead of ">3"

Steve

 

[harpscardiff]

Steve, thanks for the quick reply - i'll crack and let you know how
get on.

Cheer

 

[harpscardiff]

Hi steve,

I've managed to do what you said, it worked brilliantly!! But the
about 2 hours ago the boss decided to change the way he wanted t
assign points....

so for every columns/catergoy the person with the highest score get
10, then its goes down to 8, 4, 3, 2, 1 - still showing 1st, 2nd an
3rd.

What i have done, is inserted a column after every catergoy and worke
out the Min, median and max - would that give me a true figure?

If not is there any other way to do it?

Thank

 

[SIR Knight]

harpscardiff,

You can use the same formula to give you the 10,8,4,3,2,1 in a column
next to each criteria if that is what you are asking:

Just update the >3 to >6, then you will need another level of IF for
each of the criteria....
.... so If(Rank("x","y:z")=6,1
If(Rank("x","y:z")=5,2
If(Rank("x","y:z")=4,3 ETC

I think I have understood your change, but it is a little unclear.

You can then use the "1st" "2nd" & "3rd" on a table that sums what you
need. I don't think I understand why you would need the Min, Median
and Max element.

If I have not given you what you need then perhaps you can provide a
little more detail

Steve

 

[harpscardiff]

sorry for the late reply steve, been off for a few days.

Also apologies for the lack the detail in my query. But what you got
was spot on. but i'll just confirm what i need just to confirm:

1. I got 4 catergories - cat 1, 2, 3 and 4.
2. Next to each catergoy i got another column called position.
3. The position column holds the rank for each person within that
catergory, in total there are 4 position columns, in each catergory
they could have different ranks.
4. The rank should go from 10th down to 1st, should this formula work
or are too many nested ifs?


Code:
--------------------

=IF(B382=0,"",IF(RANK(B382,$B$382:$B$390)>10,"10th",IF(RANK(B382,$B$382:$B$390)=9,"9th",IF(RANK(B382,$B$382:$B$390)=8,"8th",IF(RANK(B382,$B$382:$B$390)>7,"7th",IF(RANK(B382,$B$382:$B$390)>6,"6th",IF(RANK(B382,$B$382:$B$390)>5,"5th",IF(RANK(B382,$B$382:$B$390)>4,"4th",IF(RANK(B382,$B$382:$B$390)=3,"3rd ",IF(RANK(B382,$B$382:$B$390)=2,"2nd",IF(RANK(B382,$B$382:$B$390)=1,"1st","")))))

--------------------


5. The person with the higest rank in each column get 10 points, 2nd
highest, 8, 4, 3, 2, 1. This will be in column at the end of the
table.


Hope it make more sense?

Thanks

 

[SIR Knight]

harpscardiff

I have also been away. You are very nearly there!

You would have been limited to 7 levels of If statements, so you will
have to use the number of the rank if greater than 4 and add the "th"
to it. Then do the top 3 as before, the second element will need to be
done separately, see below:

I have made a couple of assumptions, but perhaps you can try the
following.....

=IF(RANK(B382,$B$382:$B$390)>3,CONCATENATE(RANK(B382,$B$382:$B$390),"th"),IF(RANK(B382,$B$382:$B$390)=3,"3rd",IF(RANK(B382,$B$382:$B$390)=2,"2nd",IF(RANK(B382,$B$382:$B$390)=1,"1st"))))

This will allocate the 1st to 10th Ranking. For your Points scoring,
you may need to split this up into 4 points columns, one for each
table, then total them.

=IF(RANK(B382,$B$382:$B$390)>6,0,IF(RANK(B382,$B$382:$B$390)=6,1,IF(RANK(B382,$B$382:$B$390)=5,2,IF(RANK(B382,$B$382:$B$390)=4,3,IF(RANK(B382,$B$382:$B$390)=3,4,IF(RANK(B382,$B$382:$B$390)=2,8,IF(RANK(B382,$B$382:$B$390)=1,10)))))))

I have assumed you want to always show the rank, that points are only
awarded to the top 6 in each table, and that you will be able to insert
another column, or a separate table to calculate points for each
ranking table)

Hope this Helps

Steve