none
compare multiple values in Excel to Rank RRS feed

  • Question

  • How can Excel vba break tie between two scores. For example: Three judges scored 5-companies on 5-selection criteria. After totaling scores, there is a tie in the ranking of the companies. How can excel break the ties by first determining the tied Total scores AND then, looking at the first Criteria scores of the tied totals: the Company that has a higher value in the first criteria will get ranked higher (lower ordinal value). If the totals are tied and the first criteria scores are also tied then Excel needs to look at the second criteria and rank companies based on the same logic using the second criteria...

    Thanks,

    Monday, June 16, 2014 6:55 PM

All replies

  • The technique that I prefer is to calculate the total score, and then calculate a modified score to do the actual ranking. It is easy to do using worksheet formulas.

    So, let's say that the total score is in H4, it is an integer value, and that the subscores are in importance order in the cells C4:G4. Then in I4, use a formula like

    =H4+C4/100+D4/10000+E4/1000000+F4/100000000+G4/10000000000-ROW()/1000000000000

    and rank based on the values in I.

    A similar technique can be used in VBA....


    Wednesday, June 18, 2014 1:46 PM