Re: Formula logic explaination

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 04/01/04 

Date: Thu, 1 Apr 2004 11:37:43 -0500

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: 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)
  • Need help understanding an Array push
    ... complex script that generates some reports based on some text input ... Need help understanding what this section of Perl code does: ... new arrangement of the data pulled out of the other array. ...
    (comp.lang.perl.misc)
  • RE: Byte Array Question
    ... "mikebres" wrote: ... I'm not a VB.Net programmer and I don't know what the double greater than ... Binary Field is being placed into the byte Array in preparation to be ... if you have any references I could study to get a better understanding ...
    (microsoft.public.dotnet.languages.vb)
  • Re: [PATCHv2] drivers/misc: Altera Cyclone active serial implementation
    ... ARRAY_SIZEis not relevant to the understanding of this ... this array is of that length. ... I don't think anything else can change ppos. ... My original rationale was to skip the delay in case of error, ...
    (Linux-Kernel)
  • RE: Array of datarows
    ... Hi Brian, ... I would like to confirm my understanding of your issue. ... your description, I understand that when binding to an array of DataRows, ...
    (microsoft.public.dotnet.framework.adonet)