With one simple tweak, CSE formula goes haywire! Is this a bug?

Hello Everyone,

I have 1700 company names populating the column of a large table. All the company names are text, without any zero or blank entries. There are, however, many duplicate names in this column.

I'm doing some analysis that requires me to calculate the number of unique company names. For reasons that are too complex to explain here, it is not appropriate in this situation to filter out, hide or remove the duplicates names (and the rows of data of which they are a part).

So, I found a lovely little array formula that counts the distinct values in a table column that only contains text entries. In this case, the name of my column is "C7", for column seven. Here's the formula:

{=SUM(

1/COUNTIF(Table[C7],Table[C7])

)}


This formula returns an intelligent answer of 1135 unique company names.

I've also realized that I can add a simple little condition that will exclude some specific entries (though not necessarily all instances of that company name) before the number of distinct names is counted. That formula is:

{=SUM(

IF(Table[C8]="Yes",

1/COUNTIF(Table[C7],Table[C7])

))}


The answer now is an intelligent 630, meaning that 630 unique company names remain in the column after removing those entries whose adjacent cell in column C8 is not "Yes".

Now, here's where things go haywire. If I add a second condition, like this:


{=SUM(

IF(Table[C8]="Yes",

IF(Table[C12]<>0,

1/COUNTIF(Table[C7],Table[C7])

)))}


Suddenly, the answer is a nonsensical 591.9184412 !!!

How can that be? At a minimum, I expect a whole number answer from this formula! What is happening? Have I stumbled on an Excel bug?

I look forward to your workarounds and comments!

Cheers,

Jay

PS: I am, however, looking for a single (array) formula that will go into a single cell.

Try:

=COUNT(1/FREQUENCY(IF(Table[C8]="yes",IF(Table[C12]<>0,MATCH(Table[C7],Table[C7],0))),ROW(Table[C7])-ROW(OFFSET(Table[C7],0,0,1,1))+1))

confirmed with CSE

If as it seems you're using XL2007 you could perhaps use a non-volatile SUMPRODUCT with COUNTIFS

Please Login or Register  to view this content.

Given your file has external links - I can't really test the above against NBVC's meaningfully...

Points:

SUMPRODUCT is not really more efficient than a CSE Array { } however it does not require Array entry so some people consider it preferable from end-user perspective

The main bonus in this instance is that the above approach is non-volatile which is a very good thing when it comes to Arrays/Sumproducts ... however unlike the earlier Frequency Array it would not (given use of COUNTIFS) be backwards compatible with earlier version of XL should that be a concern.
You could still make a non-volatile backwards compatible SUMPRODUCT but if not required I'd advocate above in first instance.

If the formula is being used once or twice in the sheet as per sample, then does volatility really affect it much?

Well that would depend not only on volume & nature of the Volatile calcs but also on the frequency of Volatile actions undertaken in the model...
(unless of course one opted for Manual Calc but I'd always view that as method of last resort - others don't necessarily have the same opinion)