Nested if(and(or)) function?

[budward]

I need a formula that does the following. Is this possible?(complete
noob!)

If no numbers match "SINGLE"..If two numbers match "DOUBLE"…If three
numbers match "TRIPLE"… All four numbers match "QUAD"

7 4 2 7 DOUBLE
1 9 6 7 SINGLE
2 3 0 1 SINGLE
8 7 8 2 DOUBLE
3 6 5 9 SINGLE
2 7 7 7 TRIPLE
9 9 7 9 TRIPLE
3 4 0 4 DOUBLE
1 1 7 6 DOUBLE
7 9 5 3 SINGLE
1 1 1 1 QUAD

 

[Rick Rothstein]

Try this array-entered** formula...

=CHOOSE(MAX(COUNTIF(A11,"="&A11)),"SINGLE","DOUBLE","TRIPLE","QUAD")

**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

 

[Teethless mama]

=CHOOSE(ISNA(MODE(A11))+COUNTIF(A11,MODE(A11)),"SINGLE","DOUBLE","TRIPLE","QUAD")

 

[Teethless mama]

Simplify solution:
array-entered

=CHOOSE(MAX(COUNTIF(A11,A11)),"SINGLE","DOUBLE","TRIPLE","QUAD")

or use non array-entered

=CHOOSE(MAX(INDEX(COUNTIF(A11,A11),)),"SINGLE","DOUBLE","TRIPLE","QUAD")

 

[Rick Rothstein]

Here is another version using your MODE concept that is slightly shorter
(and uses two less function calls, although one of them is the
SUMPRODUCT)...

=CHOOSE(SUMPRODUCT(--(A11=MODE(A11,A11))),"SINGLE","DOUBLE","TRIPLE","QUAD")

 

[Rick Rothstein]

Damn! I can't figure out why I keep adding that "="& when it is not required
(I seem to have a blind-spot for that for some reason)... thanks for noting
that.

 

[Rick Rothstein]

I don't think that formula works if the repeated cell is not the first cell.
Put these values in columns A thru D and try it out...

A B C D
1 2 3 3

 

[muddan madhu]

sorry for that

may be this one

=INDEX({"single";"double";"triple";"quad"},MAX(COUNTIF(A11,A11)))

use ctrl + shift + enter

 

[Rick Rothstein]

Yes, that seems to work. Similar to my previous array-entered formula (which
was one character shorter)...

=CHOOSE(MAX(COUNTIF(A11,A11)),"SINGLE","DOUBLE","TRIPLE","QUAD")

I wonder if anyone has measured the efficiency of the CHOOSE function as
compared to the INDEX function?

--
Rick (MVP - Excel)


sorry for that

may be this one

=INDEX({"single";"double";"triple";"quad"},MAX(COUNTIF(A11,A11)))

use ctrl + shift + enter

 

[budward]

Thanks for all of the replies.
Will try the different ways out this weekend.
Bud