Forum Discussion
How to correct for the frequency when ranking
Hi Tara,
Perhaps the easiest way is to add helper column like
=<score>+<frequency>/100 or =B4+C4/100 etc
assuming the highest possible frequency is less than 100 (otherwise take 1000, whatever) and rank on this column.
I think I have it working right now. I've added these columns:
- D: rank, the rank without correction;
- E: correction (with the formule you gave);
- F: endranking with:
=RANK(E4,$E$4:$E$25,1)
I needed to format the cells from the endranking to numbers, instead of the general. Otherwise it gave the same ranking for a correction of 0,33 and 0,39 for instance. Was a lot easier than I thought, was thinking to difficult with all sorts of formulas.
Thankyou for your help!
Tara, didn't catch about formatting. The value doesn't depend on how you format it. Gave the formula from my head, now play with figures (all in General format):
I see now. It was something else what I mistyped that confused me of the formatting that solved it. Got it working now, just like your example.
It's in Dutch but the cells are the same as with yours.
So, you take the correction as
=(B4+C4)/100
My formula is
=B4 + C4/100
to differenciate only the records which have the same score but different frequencies. When result with your figures looks like
The file is attached