Forum Discussion
How to correct for the frequency when ranking
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
If I do it with your formula:
B4+C4/100
I just get the frequency, divided by 100, added to the score right? That won't correct for the frequency I think. If I need to know what the score was when the frequency was 1, for all of the scores, shouldn't I do something like this:
(B4/C4)