| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 Last edited by budward : December 8th 08 at 01:55 AM |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
|
|||
|
|||
|
Thanks for all of the replies.
Will try the different ways out this weekend. Bud Quote:
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested IF Function | Excel Discussion (Misc queries) | |||
| can you nested sum and round function within if function? | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Nested "If" Function | Excel Worksheet Functions | |||
| Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions | |||
Linear Mode
