Re: array question

In article <info-376006.10564128072005@xxxxxxxxxxxxxxxxxxxx>,
Harvey Waxman <info@xxxxxxxxxxxxxxxxxxxxxx> wrote:

> {=AVERAGE(IF(($J$12:$J$10786>R1)*($J$12:$J$10786<=R2),$S$12:$S$10786))}
>
> In the above, column J is a column of values
> R1 - R5 is a list of values in numerical order 150, 300, 800, 5000 etc.
> Column S contains percentages
>
> The formula displays the average percentage of all values in column J that
> are
> greater than R1 AND less than or equal R2.
>
> Can someone explain why the formula works by multiplying the ranges?


Lets take just four values:

J R S
1 150
2 300
....
12 180 10%
13 425 20%
14 146 30%
15 294 40%

Then

($J$12:$J$15>R1) ==> {TRUE, TRUE, FALSE, TRUE}

($J$12:$J$15<R=R2) ==> {TRUE, FALSE, TRUE, TRUE}

In math operations, XL treats TRUE/FALSE as 1/0, respectively:

TRUE x TRUE ==> 1 x 1 ==> 1
TRUE x FALSE ==> 1 x 0 ==> 0
FALSE x TRUE ==> 0 x 1 ==> 0
TRUE x TRUE ==> 1 x 1 ==> 1

So the result of the multiplication is {1, 0, 0, 1}

THe IF() statement requires a boolean, so XL coerces 1/0 back to
TRUE/FALSE:

{1, 0, 0, 1} ===> {TRUE, FALSE, FALSE, TRUE}

and the array formula returns an array:

IF(TRUE, $S$12) ==> S12 ==> 10%
IF(FALSE, $S13) ==> FALSE ==> FALSE
IF(FALSE, $S$14) ==> FALSE ==> FALSE
IF(TRUE, $S$15) ==> S15 ==> 40%

and

=AVERAGE(10%, FALSE, FALSE, 40%) ==> 25%
.

Relevant Pages

  • Re: Countif worksheet function frustrations
    ... It coerces a True/False result into a 1/0. ... Best place to read up on this is Bob Phillips site. ... I've been looking in a few ebooks that I have and i'm unable to locate ...
    (microsoft.public.excel.worksheet.functions)
  • 2 Column Data lookup
    ... Is there a better way of accomplishing the above (an array formula or ... Hari ... India ... Prev by Date: ...
    (microsoft.public.excel.misc)
  • Re: Index and match functions help needed.
    ... Resolved via an email exchange .. ... The array formula was correctly entered and it correctly returned #N/A due ... Prev by Date: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: OFFSET??
    ... > Use the array formula (entered using Ctrtl-Shift-Enter) ... >> I've tried with combinations of MATCH and OFFSETR as well with no luck. ... Prev by Date: ...
    (microsoft.public.excel)
  • Re: minimum along rows
    ... > The simplest way to handle your example data (or any 2-column ... > comparison) would be to use an array formula like ... Bruno ... Prev by Date: ...
    (microsoft.public.excel.worksheet.functions)
Loading