[email protected] wrote...
....
>>=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
>>COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
>>0,1,COLUMNS(Rng)),Rng&""))
>
>So far I think it creates an array the size of the range that identifies
>(1 or 0) the lowest number (or if n multiples uses a fraction 1/n)
>This is then Sumproducted with the original array.
>
>Please could you explain how the two COUNTIF(OFFSET()) functions are
>constructed and how they work
The key is the OFFSET calls. They return what are effectively arrays of
range references, in this case arrays of references to each row in Rng.
That's due to the 2nd argument which evaluates to an array of serial
numbers from 0 to N-1 where N is the number of rows in Rng. COUNTIF,
SUMIF, N and T seem to be the only functions that can handle such
arrays. COUNTIF and SUMIF return array results as if their (scalar)
criteria argument had been applied to each range in the array of range
references.
The first COUNTIF call returns an array of nonnegative numbers in which
zero corresponds to the max value (the value for which there are no
values strictly larger). The equality test after boolean to numeric
conversion then has ones for max values and zeros for smaller values.
The second COUNTIF call in the denominator returns the number of max
values.
As for the interaction of OFFSET-generated arrays of ranges and an
array criteria argument to COUNTIF, it seems to work when the arrays
conform. The return value is the same size as the criteria argument and
is equivalent to applying each separate range to the entire criteria
array.
Bookmarks