average if, multiple if, divide if

[0-0 Wai Wai ^-^]

Hi.
I would like to do some conditional math (which is similar to sumif)
- average if: For numbers which are higher than 0 only, find their mode/average
- multiple if
- divide if

Thanks!

 

[0-0 Wai Wai ^-^]

- average if: For numbers which are higher than 0 only, find their

mode/average
Minor Mistake.
Please read it as "... find their mean/average"

 

[Jerry W. Lewis]

=AVERAGE(IF(condition,range))

array entered (CTRL-Shift-Enter)

An example would help me understand what you mean by "multiple if" and
"divide if".

Jerry

 

[0-0 Wai Wai ^-^]

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

?????????. ??????, ???????!!
My ability is very limited. Hope you will not mind to enlighten me if I do
wrongly.

=AVERAGE(IF(condition,range))

array entered (CTRL-Shift-Enter)

An example would help me understand what you mean by "multiple if" and
"divide if".

Jerry

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 are:

=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

 

[Jerry W. Lewis]

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 are:

=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

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

 

[0-0 Wai Wai ^-^]

So how about the following example:
{=MODE(IF(P4134<>0, P4134)) }

Does computer read it as:
- mode(
-- if(
--- P4<>0, P4
--- P5<>0, P5
--- ...
--- P134<>0, P134
-- )
- )

??

Thanks for your answer.

 

[Jerry W. Lewis]

You're welcome.

Yes, you seem to be grasping the concept. If you array enter
=IF(P4134<>0, P4134) into a column range of 131 cells (say Q4:Q134,
for example) you will see that every nonzero (and non-empty) value from
P4134 is returned, but you get FALSE values corresponding to zero
values and empty cells in P4134.

MODE() will then ignore all the non-numeric values in that output array
(both the FALSE values generated by the IF() and any text, etc. that was
originally in P4134) to give you the mode of just the non-zero numbers.

Jerry