Excel® Spreadsheet question?

You keep asking this question. The answer to your last question, "Must I have to use 5 separate formulas and 5 separate cells?", is yes. You have to do this because the array formula in R3 will look at R2, and compare the values.

See explanation below the formulas.

I put my formulas in column R, but you can put them in any column. If you use another column, change the R's in the array formula to match.

Start with Sheet 1 (assuming you are still doing this over multiple pages), R2:

=IFERROR(MODE(A1:E45, Sheet2!A1:E45), "")

Now, in R3:

=MODE(IF(ISNA(MATCH($A$1:$E$45, $R$2:$R2, 0)), $A$1:$E$45), IF(ISNA(MATCH(Sheet2!$A$1:$E$45, $R$2:$R2, 0)), Sheet2!$A$1:$E$45))

This is an array formula, so confirm with CTRL + SHIFT + ENTER.

Copy that down to R6 and you will have your top 5 most frequently occurring values on both pages.

Let me know if you require further assistance with this. I've tested it on random #'s and it works (no circular references.)

The ARRAY formula explained using a smaller sample of numbers.

In A1:A15, I have a list of numbers, say: 2, 2, 3, 4, 5, 2, 5, 5, 4, 1, 5, 3, 4, 3, 1

Start with this formula (I'll use just the first part of it) is this in C2:

=MODE(IF(ISNA(MATCH($A$1:$A$15, $C$2:$C2,0)), $A$1:$A$15))

What this is doing is populating an array with fiftteen values (one for each cell in A1:A15), if the numbers in A1:A15 can not be found in B1 (ie ISNA = TRUE) then the number from A1:A15 is returned to the array, or else FALSE is returned.

In C2, since no value in A1:A15 can be found in C1 (it is blank) so you end up with an array of:

{2; 2; 3; 4; 5; 2; 5; 5; 4; 1; 5; 3; 4; 3; 1}

This is then used in the MODE and the most common value is 5.

Now shift to the 2nd most common value in A1:A15 our formula once copied from C2 will read as:

C3: =MODE(IF(ISNA(MATCH($A$1:$A$15, $C$1:$C2,0)), $A$1:$A$15))

Now in this case some of the values in A1:A15 will be found in the preceding range of C1:C2 as C2 contains 5... so you end up with an Array of:

{2; 2; 3; 4; FALSE; 2; FALSE; FALSE; 4; 1; FALSE; 3; 4; 3; 1}

You will note the FALSE replace the instances of 5...

The MODE will look only at the remaining numerical values (Booleans (TRUE/FALSE) are ignored) so the most common value in that array is 2.

in C4, the array looks like this:

{FALSE; FALSE; 3; 4; FALSE; FALSE; FALSE; FALSE; 4; 1; FALSE; 3; 4; 3; 1}

So the value in C4 is 3... and so on as you copy it down each another row.

{FALSE; FALSE; FALSE; 4; FALSE; FALSE; FALSE; FALSE; 4; 1; FALSE; FALSE; 4; FALSE; 1}

Next value is 4.

{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 1; FALSE; FALSE; FALSE; FALSE; 1}

Finally, the only value remaining is a 1. If you copy it down further, you will get #N/A errors because there are no more numeric values. The array will have nothing but FALSE values, resulting in the #N/A error.

I know this was a long explanation, but I hope it helps in understanding why you need to use more than just one to get 2nd, 3rd, 4th, etc.. values when using array formulas like this one.