# Comparing columns for greatest value across multiple columns

1. ## Comparing columns for greatest value across multiple columns

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

2. ## Re: Comparing columns for greatest value across multiple columns

I think it should be something along the lines of:

Col A Max Count -
``Please Login or Register  to view this content.``
Col B Max Count-
``Please Login or Register  to view this content.``
Col C Max Count-
``Please Login or Register  to view this content.``
Col D Max Count-
``Please Login or Register  to view this content.``
Col E Max Count-
``Please Login or Register  to view this content.``

3. ## Re: Comparing columns for greatest value across multiple columns

If I simply copy and paste your formula (into a different column to avoid circular references) it returns a zero. I then changed it to A1:A100 etc (to fit the example sheet) and it still returned zeros as did most of my efforts before.
I had a similar line of reasoning when trying to work this out before but couldn't get it to work.

There are currently 1 users browsing this thread. (0 members and 1 guests)