Forum Discussion
How to correct for the frequency when ranking
Hi all,
So I will have a database within a few weeks with the following filled in in the columns:
- A: dataset number (ranging from 1 to somwhere around 25);
- B: scores, of the scores that where given by observers;
- C: frequencies, of the times the datasets where showed to observers.
Now I've created a simple ranking in column D with:
=RANK(B4,$B$4:$B$25,1)
I use an ascending order of ranks.
I want to correct for the times the datasets occured, so the frequency. For instance if a dataset with score 12 has a frequency of 8, I want it to be at a higher rank (i.e. rank 1) than the dataset with score 12 and frequency 10 (i.e. rank 2).
Can someone help me with finding the right formule for this?
Preferably I get a correction value and an finalranking score. This is the way I want to present the sheet.
19 Replies
I have my file added to this message, was easier then emailing it and in that way other can look at is aswel. The numbers will change, I just want the formulas to work before I have my final data.
Tara,
See columns H and I in attached.
As for emails my impression that's just collection of addresses.
Was thinking the same about the emails, so this was my solution.
Don't think the above is the solution. With that formula [B4+C4/100) I only add C4/100 to the rank from column B4. That's not correcting for the frequency.
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):
