Posted 03 July 2009 - 11:24 AM
If you're still listening:
I may have come to grips with your design once pasted your message into excel.
It will actually be better if you put the ranks to the right of your points 20 columns C:V. For instance to X:AQ. Perhaps the total could be in between, in colW.
That will make it easier to calculate the max of the entries as well as expand/fill the range with formulas for ranks.
Also, while the columns C:V are fixed, row number have to move, therefore the formula should be
=RANK(C14,$C14:$V14,1) in colC where we drop $ before the row number in my previous version.
Then in column W you pick ranges above 14 and add up the points corresponding to those ranges, like this:
=SUMIF($X73:$AQ73,">14",$C73:$V73) and pull them down for all Exhibitor rows.
Finally, to declare the overall winner, get a max of column W
=MAX(W14:W114) assuming you got 100 or so Exhibitors.
I have a problem with picking 6 highest because ties could/will do funny things. But since this is only guessing this design, I leave it like that.