| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many nested IF functions!
I am trying to determine if a data array has duplicate numbers. The numbers
in C4:K4 are randomly 0 through 9, however sometimes an error occurs and a duplicate number is inserted into the array. If a duplicate number is entered, I want the duplicate number to show up in the cell. I am running up against the max limit of 7 nested IF() functions. The function string below works, however I need to check for duplicate 8's and 9's. How can I get around this nested IF limit? =IF(COUNTIF(C4:K4,"1")1,"1",IF(COUNTIF(C4:K4,"2") 1,"2",IF(COUNTIF(C4:K4,"3")1,"3",IF(COUNTIF(C4:K 4,"4")1,"4",IF(COUNTIF(C4:K4,"5")1,"5",IF(COUNTI F(C4:K4,"6")1,"6",IF(COUNTIF(C4:K4,"7")1,"7","") )))))) Thank you in advance!!! Tom |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many nested IF functions!
On 4 Jul, 18:20, Skyscan wrote:
I am trying to determine if a data array has duplicate numbers. The numbers in C4:K4 are randomly 0 through 9, however sometimes an error occurs and a duplicate number is inserted into the array. If a duplicate number is entered, I want the duplicate number to show up in the cell. I am running up against the max limit of 7 nested IF() functions. The function string below works, however I need to check for duplicate 8's and 9's. How can I get around this nested IF limit? =IF(COUNTIF(C4:K4,"1")1,"1",IF(COUNTIF(C4:K4,"2") 1,"2",IF(COUNTIF(C4:K4,"3")1,"3",IF(COUNTIF(C4:K 4,"4")1,"4",IF(COUNTIF(C4:K4,"5")1,"5",IF(COUNTI F(C4:K4,"6")1,"6",IF(COUNTIF(C4:K4,"7")1,"7","") )))))) Thank you in advance!!! Tom Tom, Trying to understand. You need 9 random numbers with no duplicates chosen from whole integers between 1 and 9 but to also include all numbers ? Rather than constructing a nested if statement why not select 1 number from 1 to 9 at random ? If you need more than one selection then if it needs to be random you will need repeats or else you will end up with a probability of 1:9 of getting any number and this would not be random. If you gave some more context I may be able to help better. Matthew |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many nested IF functions!
Enter the following formula in C5 and fill across to K5.
=IF(COUNTIF($C$4:$K$4,C4)1,C4,"") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Skyscan" wrote in message ... I am trying to determine if a data array has duplicate numbers. The numbers in C4:K4 are randomly 0 through 9, however sometimes an error occurs and a duplicate number is inserted into the array. If a duplicate number is entered, I want the duplicate number to show up in the cell. I am running up against the max limit of 7 nested IF() functions. The function string below works, however I need to check for duplicate 8's and 9's. How can I get around this nested IF limit? =IF(COUNTIF(C4:K4,"1")1,"1",IF(COUNTIF(C4:K4,"2") 1,"2",IF(COUNTIF(C4:K4,"3")1,"3",IF(COUNTIF(C4:K 4,"4")1,"4",IF(COUNTIF(C4:K4,"5")1,"5",IF(COUNTI F(C4:K4,"6")1,"6",IF(COUNTIF(C4:K4,"7")1,"7","") )))))) Thank you in advance!!! Tom |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many nested IF functions!
Try this:
=INDEX(C4:K4,MATCH(TRUE,INDEX(COUNTIF(C4:K4,C4:K4) 1,1,),0)) If more than one number is duplicated the formula will return the *first* matched duplicate. -- Biff Microsoft Excel MVP "Skyscan" wrote in message ... I am trying to determine if a data array has duplicate numbers. The numbers in C4:K4 are randomly 0 through 9, however sometimes an error occurs and a duplicate number is inserted into the array. If a duplicate number is entered, I want the duplicate number to show up in the cell. I am running up against the max limit of 7 nested IF() functions. The function string below works, however I need to check for duplicate 8's and 9's. How can I get around this nested IF limit? =IF(COUNTIF(C4:K4,"1")1,"1",IF(COUNTIF(C4:K4,"2") 1,"2",IF(COUNTIF(C4:K4,"3")1,"3",IF(COUNTIF(C4:K 4,"4")1,"4",IF(COUNTIF(C4:K4,"5")1,"5",IF(COUNTI F(C4:K4,"6")1,"6",IF(COUNTIF(C4:K4,"7")1,"7","") )))))) Thank you in advance!!! Tom |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many nested IF functions!
.... The numbers in C4:K4 are randomly 0 through 9, however sometimes
an error occurs and a duplicate number is inserted into the array. I was wondering how these random numbers are being produced. It is not too difficult to construct a macro that will produce nine numbers numbers with no repeats... guaranteed. Rick |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many nested IF functions!
Thanks everyone for your assistance in answering this question for me. I
apologize for not being more clear in my description of the situation. The numbers 1-9 (another error I made in my problem description was that I wrote 0 through 9!) are entered manually in a random order, but all 9 numbers must be represented. The solution which Biff provided below resolved my situation completely!!! THANKS SO MUCH!! "T. Valko" wrote: Try this: =INDEX(C4:K4,MATCH(TRUE,INDEX(COUNTIF(C4:K4,C4:K4) 1,1,),0)) If more than one number is duplicated the formula will return the *first* matched duplicate. -- Biff Microsoft Excel MVP "Skyscan" wrote in message ... I am trying to determine if a data array has duplicate numbers. The numbers in C4:K4 are randomly 0 through 9, however sometimes an error occurs and a duplicate number is inserted into the array. If a duplicate number is entered, I want the duplicate number to show up in the cell. I am running up against the max limit of 7 nested IF() functions. The function string below works, however I need to check for duplicate 8's and 9's. How can I get around this nested IF limit? =IF(COUNTIF(C4:K4,"1")1,"1",IF(COUNTIF(C4:K4,"2") 1,"2",IF(COUNTIF(C4:K4,"3")1,"3",IF(COUNTIF(C4:K 4,"4")1,"4",IF(COUNTIF(C4:K4,"5")1,"5",IF(COUNTI F(C4:K4,"6")1,"6",IF(COUNTIF(C4:K4,"7")1,"7","") )))))) Thank you in advance!!! Tom |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many nested IF functions!
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Skyscan" wrote in message ... Thanks everyone for your assistance in answering this question for me. I apologize for not being more clear in my description of the situation. The numbers 1-9 (another error I made in my problem description was that I wrote 0 through 9!) are entered manually in a random order, but all 9 numbers must be represented. The solution which Biff provided below resolved my situation completely!!! THANKS SO MUCH!! "T. Valko" wrote: Try this: =INDEX(C4:K4,MATCH(TRUE,INDEX(COUNTIF(C4:K4,C4:K4) 1,1,),0)) If more than one number is duplicated the formula will return the *first* matched duplicate. -- Biff Microsoft Excel MVP "Skyscan" wrote in message ... I am trying to determine if a data array has duplicate numbers. The numbers in C4:K4 are randomly 0 through 9, however sometimes an error occurs and a duplicate number is inserted into the array. If a duplicate number is entered, I want the duplicate number to show up in the cell. I am running up against the max limit of 7 nested IF() functions. The function string below works, however I need to check for duplicate 8's and 9's. How can I get around this nested IF limit? =IF(COUNTIF(C4:K4,"1")1,"1",IF(COUNTIF(C4:K4,"2") 1,"2",IF(COUNTIF(C4:K4,"3")1,"3",IF(COUNTIF(C4:K 4,"4")1,"4",IF(COUNTIF(C4:K4,"5")1,"5",IF(COUNTI F(C4:K4,"6")1,"6",IF(COUNTIF(C4:K4,"7")1,"7","") )))))) Thank you in advance!!! Tom |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many nested IF functions!
"T. Valko" wrote...
Try this: =INDEX(C4:K4,MATCH(TRUE,INDEX(COUNTIF(C4:K4,C4:K4 )1,1,),0)) .... If willing to work from right to left, =LOOKUP(2,1/(COUNTIF(C4:K4,C4:K4)1),C4:K4) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested Functions | Excel Discussion (Misc queries) | |||
| Nested Functions | Excel Discussion (Misc queries) | |||
| More then 7 nested If functions. | Excel Worksheet Functions | |||
| Nested functions HELP! | Excel Worksheet Functions | |||
| nested if(and) functions | Excel Discussion (Misc queries) | |||
Linear Mode
