ranking calculation

[MJK.NSW]

Hi, I've got a clalc field as follows

Empl 1::average= .07865

Empl 2::average= .8723

Empl 3::average= .5432

Empl 4::average = 1.22234

and I want to put another field "ranking" so that

Empl 1::ranking=4

Empl 2::ranking=2

Empl 3::ranking=3

Empl 4::ranking=1

it's in an already sorted layout that can't be re-sorted (otherwise I'll loose the summary calcs) and it doesn't need to be displayed in order (1,2,3 etc) just show the ranking calc field, so I'm hoping some mathimatical genius might come up with a calculation ?

cheers

[LaRetta]

Hi MJK,

Can you provide the file or a sample file showing exactly what you have? :^)

[MJK.NSW]

Hi, the file is about 240megs, but it's really just the cacl I'm after. in excel there's a rank() function which returns a ranking 1,2,3,4, etc for a chosen list of numbers

basically I was hoping someone would know the maths behind that excel function

[MJK.NSW]

actually, after digging around I found this post from Comment :-

Usually this is done by defining a self-join relationship matching the event (and age group, etc.) and comparing the results, e.g.

Scores::EventID = Scores 2::EventID

AND

Scores::AgeGroup = Scores 2::AgeGroup

AND

Scores::Score < Scores 2::Score

The rank is then given by a calculation field =

Count ( Scores 2::ScoreID ) + 1

[MJK.NSW]

mmm... the only problem is the "scores::score" in mine (Hours::average) is based summary fields which I can't index and I get a 'missing index' error ... grrrr

[LaRetta]

You need relational solution not summaries which is why I asked for sample originally...

I suggest you stay on the other thread since that is the logical approach and you are receiving assistance there from the best there is. :^)