Override rank formula

Hi all

I’ve searched loads for an answer to this, but can’t find anything.

I have tasks ranked by a calulated score, this give a priority list.

I want to override the rankings, so if I believe #3 should be ranked #1, I can add this into a field and then it goes to #1 and #1 drops to #2’ etc and #3 isn’t double counted.

I need the capability to add multiple overrides too, so #6 may move to #4 too, for example.

I’ve tried adding in extra identifiers but I’ve hit a brick wall, please help!

Many thanks

Morgraal

Do you want to manually enter the Rank of the player or alter the score?

Hi

I’m predominantly on mobile so attaching a file is tricky.

I’ll try to give a better idea though

Item Score. Rank. Override. Adjusted Rank
A. 10. 1. 2
B. 9. 2. 4
C. 8. 3. 5
D. 7. 4. 1. 1
E. 6. 5. 6
F. 5. 6. 3. 3

I can’t think how to get the adjusted rank column to work.

Thanks

The Over ride column is a manual input

Originally Posted by FDibbins

Where is the sample file I asked for?

As I stated, I’m predominantly on mobile. I can’t log into the site from work so I’ll throw something together when I get a chance.

Cheers

Ok, so I've thrown the attached together, the rank table to rank them, the override column which i want to populate with numbers higher or lower than the current rank and the 'Actual' output table.

I've put in some options of override and I want them to come out in the order that is in the 3rd 'Override' table.

I hope this makes more sense.

Cheers

ok try this
in f3 put
=IF(E3="",D3,"")
in g3
=IF(E3>0,E3,IF(COUNTIF($E$3:$E$9,RANK(F3,$F$3:$F$9,1)+COUNTIF($E$3:$E$9,"<="&F3)),RANK(F3,$F$3:$F$9,1)+COUNTIF($E$3:$E$9,"<="&F3)-1,RANK(F3,$F$3:$F$9,1)+COUNTIF($E$3:$E$9,"<="&F3)))

and copy down, F is a helper column. I am assuming you have no ties

Lovely. thank you.

I've been messing about with various calcs for days but was going in circles and hit a wall.

I amended part of it to fit as the manual override is a lookup, but it works great.

Cheers

Hi, this is quite a great formula. What if I want to insert a column to the right of column G to sort out any ties caused by:
if you have 2 override numbers and one of your override numbers (column E) you enter is less than the corresponding ranking in column D. it will have 2 duplicate adjusted rankings
I would want to prioritize the over ride rank. I attached an excel.

I could greatly use your Excel expertise for this one. Thanks in advanced.