Highest Mode

Sorry for the earlier edit, wasn't thinking, I wonder if perhaps:

=MODE(LARGE($D3:$K3,{1,2,3,4,5,6,7,8}))

or to be "safer"

=MODE(LARGE($D3:$K3,ROW(A$1:INDEX(A:A,COUNT($D3:$K3)))))

would either of those work for you ?


(on a final note - is this question specific to Mac XL2008 - ie the forum you've posted this to - if not I will move it)

Sorry, I didn't know this was a mac section?
I just read Excel Help. I must have got turned around at some point..

and YES, the function does work, both of them, but I see the safter one still works if I remove an entry out the middle..

I just have one question now...

HOW do they work, I read up on "larger" and I must be dump as I just can't understand how {1,2,3,4...} works..

or what the A:A and A:1 means in the "safer" version..

Guess I have a lot to learn and the help files are just not written in a way that I can understand them.

PS: I am getting 1 little niggle though.
I am having to store the number and refer to it in a conditional formatting, as it says you cannot use "unions, intersections or array constants" for criteria

First, let's deal with the Conditional Formatting question...

If your intention is to highlight all instances of the highest mode within the referenced range you should find the 2nd option works...
(the first does not given the use of the inline array / array constants - ie {1...})

If we assume for sake of continuity that the data values are (if listed) to be stored within D3:K3 then with that range highlighted (having selected D3 first) we can apply a Conditional Formula rule of:

Please Login or Register  to view this content.

Regards, how both approaches work in the first instance

They both take the terms and sort them in an array in Desc order from largest to smallest .
The MODE function (as outlined already by yourself) will return the first value in the data array that has the requisite frequency - it follows that with sorted data the first value is going to be the biggest.

The second approach (ie that used above) is slightly more "robust" than the first given that rather than assume 8 data points (explicit assumption in the first - {1...8} ) the second only looks for as many data points as exist within the specified range.

The count of data values is determined with a basic COUNT function

Please Login or Register  to view this content.

INDEX is used to create a reference based on that Count, assume the above count returns 5:

Please Login or Register  to view this content.

ROW is subsequently applied to the referenced range

Please Login or Register  to view this content.

thereby returning an array of values {1;2;3;4;5} for use as "k" in the LARGE

So the LARGE then builds an array of 5 values against which the MODE is applied.


I am moving this thread to the more general Worksheet Functions forum

You need to create an array of terms for "k" in the LARGE function.

Where

a) "k" will range from 1 to n

b) n being the lesser of max terms (in this instance 8) and count of available terms
EDIT: the use of 8 and subsequent MIN function is optional and is not actually in the initial formula ... so you can disregard the MIN function elements - it doesn't affect the underlying logic

we can create that array by using ROW and an appropriate range reference, the starting point for which will always be row 1 and the end point determined by b)

To generate b) we use INDEX with the MIN & COUNT,eg:

Please Login or Register  to view this content.

If there are 10 numeric values within the specified range the INDEX will return A8, if there were five numeric values within the specified range INDEX will return A5

INDEX can return either value or range pending the context in which it's used... in this case it's being used in range form:

Please Login or Register  to view this content.

range form given i) use of colon (range delimiter) and ii) use of ROW which expects a range input

Using the 10 term example the above would equate to

Please Login or Register  to view this content.

ie lesser of 8 and 10 is used

Using the 5 term example

Please Login or Register  to view this content.

In conjunction with an Array formula the above use of ROW will generate the requisite "k" array for use with encompassing LARGE, eg:

10 terms (uses 8 as this the "cap"):

Please Login or Register  to view this content.

5 terms:

Please Login or Register  to view this content.

So putting it altogether, step by step using the 10 term example:

Please Login or Register  to view this content.

For 5 terms the only difference is from step 3 onwards as the output of the MIN would be 5 rather than 8... this is to ensure where fewer valid values exist than desired (8) the function adapts accordingly so as to avoid error.

I hope that helps.