0-0 Wai Wai ^-^ wrote:


Thanks.
I think I get your idea how to do "multiple/divide if".
For example, just like average, we only multiple/divide the numbers if they are
non-zero positive numbers.
So the answers a

=product(IF(condition,range))
=quotient(IF(condition,range))
array entered (CTRL-Shift-Enter)

And it can be extended to:
=mode(IF(condition,range))
=median(IF(condition,range))
=stdev(IF(condition,range))
array entered (CTRL-Shift-Enter)

...although I can't really read what this array forumla means to computers and
understand why this kind of formula works like a charm :P


You're welcome. You can also use this approach with COUNT, SUM, etc.
You are beginning to discover the power of array formulas as I suggested
in another of your threads.

To understand these formulas, lets deconstruct a few examples.

A1:C5=7 produces an array (5 rows by 3 colums) of boolean values
(TRUE/FALSE) according to which cells in A1:C5 contain 7 or not. You
can see this by selecting a 5x3 range of cells and array entering
=A1:C5=7

Now select another 5x3 range of cells and array enter =IF(A1:C5,D1:F5)
What you get is the values from D1:F5 that correspond to values of 7 in
A1:C5. All other cells in the range are FALSE, because no specific
value was specified when the condition was not TRUE. Wrapping this in a
numeric function like AVERAGE, SUM, MEDIAN, etc. will ignore the FALSE
values and only opperate on the numeric values that correspond to where
the condition was satisfied.

Now, consider =A1:A5=7. That produces a column vector of 5 boolean
values. The array formula =IF(A1:A5,D1:F5) will still output a 5x3
array, but since there is only one column in the condition, it gets
repeated for each column of D1:F5, so if A1=7, then the values from
D1:F1 will be in the first row of the array output ...

Hopefully the light is dawning ...

Jerry