Re: Formula logic explaination

Tech-Archive recommends: Fix windows errors by optimizing your registry

anonymous_at_discussions.microsoft.com
Date: 04/01/04 

Date: Thu, 1 Apr 2004 08:59:07 -0800

In the Countif help it shows Countif (range,criteria), you
mention Countif (range,range) is there a difference here?

Also, I'm not understanding how the array returned by the
Countif is actually generated, what do the numbers in the
array represent?

Thanks for the time spent helping. . .

>-----Original Message-----
>the formula boils down to
>
>=if(max(Countif(range,range))>1,"Duplicates,"No
Duplicates")
>
>Countif(range,range) would return an array like
{1;4;1;1;1;1;1;2;4;4;4;2}
>
>for a column with data like:
>
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 2
> 2
> 2
> 8
>
>
>Max finds the max number in the array. If it is greater
than 1, then there
>are duplicates. Otherwise, there are not.
>
>The other part of the formula is just finding the max row
with a value so
>the range dynamically determines the range of cells to
check.
>
>--
>Regards,
>Tom Ogilvy
>
>
>"Steve" <cupps@trinity-health.org> wrote in message
>news:1262101c41806$94985c60$a001280a@phx.gbl...
>> I found the formula below some place and I am trying to
>> understand the logic behind it. It finds duplicate
entries
>> in a column. If anyone would like to give me a hand in
>> understanding it I would appreciate it. I am a beginner
in
>> the fomula creation world and I think by understanding
>> other people's logic it will help me as I develope my
own
>> formulas.
>>
>> =IF(MAX(COUNTIF(INDIRECT("A2:A"&(MAX((A2:A500<>"")*ROW
>> (A2:A500)))),INDIRECT("A2:A"&(MAX((A2:A500<>"")*ROW
>> (A2:A500))))))>1,"Duplicates","No Duplicates")
>>
>> Thanks
>
>
>.
>

Relevant Pages

  • Re: Formula logic explaination
    ... The array is generated by counting each entry in the list in turn, ... > In the Countif help it shows Countif, you ... >>are duplicates. ... >>> understanding it I would appreciate it. ...
    (microsoft.public.excel.programming)
  • Re: The Kth element in a text list
    ... I have a bunch of formulae (e.g. LOOKUP, ... We use COUNTIF, < etc. because it is TEXT. ... =SMALL) for all numbers and no blanks. ... Or, an array formula: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Explanation of SUMPRODUCT
    ... COUNTIF returns 4. ... But A3 also holds Bob, ... The array results of the COUNTIF are then divided into 1 to get a fractional ... SUMPRODUCT then adds these up to come up with the number of unique entries, ...
    (microsoft.public.excel.programming)
  • Re: SOLVED: How do I scope a variable if the variable name contains a variable?
    ... Either I have it right but am expressing it poorly, ... so I wonder if my understanding is all wrong. ... table so you can't get access to them via symrefs. ... DF> You suggested using a hash INSTEAD of an array, ...
    (comp.lang.perl.misc)
  • Re: COUNTIF with DATE Logic
    ... Normally I use a> CountIf on this array to ascertain how many occurences occur over> certain data ranges over the entire year. ...
    (microsoft.public.excel)