array question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Harvey Waxman - 28 Jul 2005 15:56 GMT

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

Signature

Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Remove thefrown to email me

Reply to this Message

JE McGimpsey - 29 Jul 2005 07:38 GMT

> {=AVERAGE(IF(($J$12:$J$10786>R1)*($J$12:$J$10786<=R2),$S$12:$S$10786))}
>
[quoted text clipped - 7 lines]
>
> 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%

Reply to this Message

Paul Berkowitz - 29 Jul 2005 17:43 GMT

On 7/28/05 11:38 PM, in article
jemcgimpsey-A207E6.00385929072005@msnews.microsoft.com, "JE McGimpsey"
<jemcgimpsey@mvps.org> wrote:

> =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%?)

Signature

Paul Berkowitz

Reply to this Message

JE McGimpsey - 29 Jul 2005 18:38 GMT

> 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

Reply to this Message

Domenic - 29 Jul 2005 22:24 GMT

> Interestingly (at least to me), the unary minus is an operator in XL:
>
[quoted text clipped - 3 lines]
>
> =+TRUE ===> TRUE

Very interesting! I didn't realize it!

Thanks, JE!

Reply to this Message

Harvey Waxman - 29 Jul 2005 17:50 GMT

> > {=AVERAGE(IF(($J$12:$J$10786>R1)*($J$12:$J$10786<=R2),$S$12:$S$10786))}
> >
[quoted text clipped - 18 lines]
> 14 146 30%
> 15 294 40%

Great explanation. Many thanks, as usual

Signature

Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Remove thefrown to email me

Reply to this Message

Mac Forum / Applications / Excel / July 2005

array question