Hi Richard,

I did wonder where you had used the AND() from, it looked familiar,

I've just been testing your answers and found that with this data set the results are

SN F1 F2 F3 TS SS BS
a1 1 2 3 3 2 1
a2 1 3 2 3 2 1
a3 2 1 3 3      |3| 1
a4 2 3 1 3 2 1
a5 3 1 2 3 2 1
a6 3 2 1 3      |1| 1
a7 1 1 1 1      |1| 1
a8 2 2 1      |1| 2 1
a9 1 2 2 2 2 1

|x| are incorrect

I re-tested my formulas and also go an incorrect result on Top Score
SN U C S TS SS
a1 1 2 3 3 2
a2 1 3 2 3 2
a3 2 1 3 3 2
a4 2 3 1 3 2
a5 3 1 2 3 2
a6 3 2 1 3 2
a7 1 1 1 1 1
a8 2 2 1      |1| 2
a9 1 2 2 2 2

So I'm going to have a look at it further, I'll post more soon,

Alex

---

Reply from Richard Rost:

Wow... thanks for testing this so thoroughly. OK, I've revised the equations. I tested them with your data sets above and everything seems to work. I didn't really do too much testing with sets of data that had a lot of duplicates in them. I was using mostly three unique values. Anyhow, these equations seem to work:

TopScore: IIf([F1]>=[F2] And [F1]>=[F3],[F1],IIf([F2]>=[F1] And [F2]>=[F3],[F2],[F3]))

SecondScore: IIf(([F1]<=[F2] And [F1]>=[F3]) Or ([F1]>=[F2] And [F1]<=[F3]),[F1],IIf(([F2]>=[F1] And [F2]<=[F3]) Or ([F2]<=[F1] And [F2]>=[F3]),[F2],[F3]))

BottomScore: IIf([F1]<=[F2] And [F1]<=[F3],[F1],IIf([F2]<=[F1] And [F2]<=[F3],[F2],[F3]))

With the Top and Bottom scores it was just a matter of including the = signs. With the Middle score, we were checking to see if F1>F2 and F1F3.

Test it. Try to break it. If this doesn't work, we might need to make a custom VBA function - which probably would have been easier in the first place... but this is so much fun. :)