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
|