Cannot understand this CSE formula and it's driving me nuts!!!!!

[Bengo]

Hi,

I have used the following formula to look at a data range (in J3:V3) and return the most common string (the data is simple, either "RED", "GREEN" or "AMBER")

{=INDEX(J3:V3,MATCH(MAX(COUNTIF(J3:V3:,J3:V3)),COUNTIF(J3:V3,J3:V3),0))}

It works fine and returns what I was after, but I can't quite get how it works. I've researched the Index Match element and understand that in isolation, although how the Max/Countif part fits in I do not understand. I also do not understand the syntax for the countif if I compare it to other tutorials on Countif...

So I suppose I'm not really understanding this any more than I did when I first used it! If anyone could explain in simple English how these work in unison you would be sparing my sanity!!!

Thanks in advance!
Ben

 

[Andrew Poulsom]

Cant you use Formula Auditing to Evaluate the formula? It returns the item from J3:V3 which has the largest number of entries in that range.

 

[Bengo]

Hi Andrew,

I'm trying that as we speak... Just struggling with the MAX(Countif part I think, when I use the countif on it's own as a CSE formula, using the same range twice looks for the number of times the letter in J3 occurs rather than the most occuring. I gather that's where the MAX comes into play...

 

[Vidar]

Hi


For example this is the values in J3 to V3
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Red
[/TD]
[TD]Green
[/TD]
[TD]Green
[/TD]
[TD]Green
[/TD]
[TD]Green
[/TD]
[TD]Amber
[/TD]
[TD]Amber
[/TD]
[TD]Green
[/TD]
[TD]Amber
[/TD]
[TD]Red
[/TD]
[TD]Red
[/TD]
[TD]Amber
[/TD]
[TD]Green
[/TD]
[/TR]
</tbody>[/TABLE]

The result will be "Green" (6 Green, 4 Amber, 3 Red).
{=INDEX(J3:V3,MATCH(MAX(COUNTIF(J3:V3:,J3:V3)),COUNTIF(J3:V3,J3:V3),0))}

The first "COUNTIF(J3:V3:,J3:V3))"part of the formula gives a list of how many times each color appear in the range. If you select only that part of the formula and press F9 it will show:
=INDEX(J3:V3,MATCH(MAX({3\6\6\6\6\4\4\6\4\3\3\4\6}),COUNTIF(J3:V3,J3:V3),0))
The "Red" is given a "3" for each time it appears in the list beacuse it appears three times, Green is given "6" and Amber is given "4".
The Max function will pick "6" as the highest number in the list.
MAX({3\6\6\6\6\4\4\6\4\3\3\4\6}) gives "6".

So the formula is now:
=INDEX(J3:V3,MATCH(6,COUNTIF(J3:V3,J3:V3),0))

The last COUNTIF function will create the same list as the first one:
=INDEX(J3:V3;MATCH(6;{3\6\6\6\6\4\4\6\4\3\3\4\6};0))
The MATCH function will look for an exact match (given by the "0" at the end of the MATCH function) to the "6", which appear as #2 in that list, and then gives that to the INDEX function.
=INDEX(J3:V3,2)
INDEX will then pick the 2nd. value from the the J3:V3 range which is "Green".

Hope this explains the workings of the formula.

Vidar

 

[Bengo]

Vidar thank you so much that makes perfect sense!! I was nearly there having worked my way backwards step by step but that makes it all clear! Thanks very much!!!