[sims] POSITION formula in Template

[tben2505]

Hello folks

 

Bit of a tricky one here

 

I have an aspect called EFFORT AVERAGE which averages all the effort aspects in the template. The effort gradeset is 1, 2, 3 or 4 - where 1 is the best.

 

I want to rank the position of the effort, so obviously use the position formula. Trouble is it ranks ascending, so an average of "4" is the highest position, instead of the lowest.

 

Obviously the default way in which position works is the most common, the highest grade should indeed be ranked first.

 

But does anyone have a sneaky way of eliciting the "correct" position from the average column, whilst retaining the same value??

 

Excel allows =RANK(RANGE,N) where N is ascending or descending - a nice tick box in this formula on SIMS would be nice!

 

Cheers

 

Tom

[RootsSchImp]

Add a columnas subtracting effort avg from 5, then rank from that columnas?

[tben2505]

Still has the same effect - look at it if the numbers were higher:

 

 

100 becomes 95

99 becomes 94

98 becomes 93

 

and so on - the rank, therefore, would be identical :/

[Jonsi]

If you create a second position column, referring to the first position column in it, then it should show in the correct order. You could then hide the first position column.

[tben2505]

Genius!!

[vikpaw]

Still has the same effect - look at it if the numbers were higher:

 

 

100 becomes 95

99 becomes 94

98 becomes 93

 

and so on - the rank, therefore, would be identical :/

 

I think what was meant by @RootsSchImp was that 5 minus 4 = 1 So it converts the false highest value into the lowest.

5 minus 3 = 2 becoming your second lowest score... etc.

Edited December 1, 2015 by vikpaw

[tben2505]

I think what was meant was that 5 minus 4 = 1 So it converts the false highest value into the lowest.

5 minus 3 = 2 becoming your second lowest score... etc.

 

Yes that is another way of doing it too - cheers all!