I have a spreadsheet where I need to count the number of instances where a value in a given column is the highest against the equivalent values in several other columns. I found a formula that will do this for 2 columns but can't work out how to extend this. The formula for two columns is:
=SUMPRODUCT(--(A1:A100>=B1:B100))
I've managed to do this using =IF(AND(A1>B1, A1>C1, A1>D1, A1>E1),1 ,0) and then producing a table showing where each criteria comes out on top and then summing the number of wins. I then used =IF(A1=MAX(B1:E1), 1, 0) as this makes it easier to drag out.
I would like, however, to do this in a single cell. My dataset is very large (Monte Carlo Analysis for 23 technologies) and so managing this in a single cell would save me something in the range of 46000 cells and make everything a bit neater.
As an example, if I had:
1 5 2 6 3
2 3 7 4 8
1 8 9 7 2
1 4 0 8 2
2 1 4 2 1
2 4 5 6 1
9 3 4 5 6
I would hope for an output of 1, 0, 2, 3, 1 for the respective columns.
I've attached an example file with some random data and my methods. In the case of a tie, both should score a point preferably as is the case in the example.
I'm currently running excel 2007
Bookmarks