array question

[Harvey Waxman]

{=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?

thanks

 

[JE McGimpsey]

{=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%

 

[Paul Berkowitz]

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

So here Excel doesn't treat FALSE as 0 , but just ignores it? (If FALSE were
treated as 0 here, wouldn't the average ==> 12.5%?)

 

[Harvey Waxman]

Lets take just four values:

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

Great explanation. Many thanks, as usual

 

[JE McGimpsey]

So here Excel doesn't treat FALSE as 0 , but just ignores it? (If FALSE were
treated as 0 here, wouldn't the average ==> 12.5%?)

AVERAGE ignores booleans ("logical values"). From Help:

If an array or reference argument contains text, logical values, or empty
cells, those values are ignored; however, cells with the value zero are
included.

XL coerces TRUE/FALSE to 1/0 in math operations, such as

-FALSE
FALSE + 0
FALSE * 1
FALSE/TRUE

etc. Functions normally don't coerce booleans.

Interestingly (at least to me), the unary minus is an operator in XL:

=-TRUE ===> -1

while the unary plus is not (it's ignored):

=+TRUE ===> TRUE

 

[Domenic]

Interestingly (at least to me), the unary minus is an operator in XL:

=-TRUE ===> -1

while the unary plus is not (it's ignored):

=+TRUE ===> TRUE

Very interesting! I didn't realize it!

Thanks, JE!