|
Nested if(and(or)) function?
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" Also, each number is in a separate cell. 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 |
Nested if(and(or)) function?
Try this array-entered** formula...
=CHOOSE(MAX(COUNTIF(A1:D1,"="&A1:D1)),"SINGLE","DO UBLE","TRIPLE","QUAD") **Commit this formula using Ctrl+Shift+Enter, not just Enter by itself. -- Rick (MVP - Excel) "budward" wrote in message ... 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 -- budward |
Nested if(and(or)) function?
=CHOOSE(ISNA(MODE(A1:D1))+COUNTIF(A1:D1,MODE(A1:D1 )),"SINGLE","DOUBLE","TRIPLE","QUAD")
"budward" wrote: 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 -- budward |
Nested if(and(or)) function?
Simplify solution:
array-entered =CHOOSE(MAX(COUNTIF(A1:D1,A1:D1)),"SINGLE","DOUBLE ","TRIPLE","QUAD") or use non array-entered =CHOOSE(MAX(INDEX(COUNTIF(A1:D1,A1:D1),)),"SINGLE" ,"DOUBLE","TRIPLE","QUAD") "Rick Rothstein" wrote: Try this array-entered** formula... =CHOOSE(MAX(COUNTIF(A1:D1,"="&A1:D1)),"SINGLE","DO UBLE","TRIPLE","QUAD") **Commit this formula using Ctrl+Shift+Enter, not just Enter by itself. -- Rick (MVP - Excel) "budward" wrote in message ... 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 -- budward |
Nested if(and(or)) function?
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(--(A1:D1=MODE(A1:D1,A1:D1))),"SINGLE","DOUBLE","TRIP LE","QUAD") -- Rick (MVP - Excel) "Teethless mama" wrote in message ... =CHOOSE(ISNA(MODE(A1:D1))+COUNTIF(A1:D1,MODE(A1:D1 )),"SINGLE","DOUBLE","TRIPLE","QUAD") "budward" wrote: 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 -- budward |
Nested if(and(or)) function?
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 (MVP - Excel) "Teethless mama" wrote in message ... Simplify solution: array-entered =CHOOSE(MAX(COUNTIF(A1:D1,A1:D1)),"SINGLE","DOUBLE ","TRIPLE","QUAD") or use non array-entered =CHOOSE(MAX(INDEX(COUNTIF(A1:D1,A1:D1),)),"SINGLE" ,"DOUBLE","TRIPLE","QUAD") "Rick Rothstein" wrote: Try this array-entered** formula... =CHOOSE(MAX(COUNTIF(A1:D1,"="&A1:D1)),"SINGLE","DO UBLE","TRIPLE","QUAD") **Commit this formula using Ctrl+Shift+Enter, not just Enter by itself. -- Rick (MVP - Excel) "budward" wrote in message ... 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 -- budward |
Nested if(and(or)) function?
try this
=INDEX({"single";"double";"triple";"quad"},COUNTIF (A1:D1,A1:D1)) use ctrl + shift + enter On Dec 8, 6:44*am, budward wrote: 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 -- budward |
Nested if(and(or)) function?
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 -- Rick (MVP - Excel) "muddan madhu" wrote in message ... try this =INDEX({"single";"double";"triple";"quad"},COUNTIF (A1:D1,A1:D1)) use ctrl + shift + enter On Dec 8, 6:44 am, budward wrote: 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 -- budward |
Nested if(and(or)) function?
sorry for that
may be this one =INDEX({"single";"double";"triple";"quad"},MAX(COU NTIF(A1:D1,A1:D1))) use ctrl + shift + enter On Dec 8, 10:45*am, "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "muddan madhu" wrote in message ... try this =INDEX({"single";"double";"triple";"quad"},COUNTIF (A1:D1,A1:D1)) use ctrl + shift + enter On Dec 8, 6:44 am, budward wrote: 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 -- budward |
Nested if(and(or)) function?
Yes, that seems to work. Similar to my previous array-entered formula (which
was one character shorter)... =CHOOSE(MAX(COUNTIF(A1:D1,A1:D1)),"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) "muddan madhu" wrote in message ... sorry for that may be this one =INDEX({"single";"double";"triple";"quad"},MAX(COU NTIF(A1:D1,A1:D1))) use ctrl + shift + enter On Dec 8, 10:45 am, "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "muddan madhu" wrote in message ... try this =INDEX({"single";"double";"triple";"quad"},COUNTIF (A1:D1,A1:D1)) use ctrl + shift + enter On Dec 8, 6:44 am, budward wrote: 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 -- budward |
Thanks for all of the replies.
Will try the different ways out this weekend. Bud Quote:
|
| All times are GMT +1. The time now is 08:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com