average if, multiple if, divide if
 

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!


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

average if, multiple if, divide if
 

- average if: For numbers which are higher than 0 only, find their
mode/average
Minor Mistake.
Please read it as "... find their mean/average"

average if, multiple if, divide if
 

=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 ^-^ wrote:

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!

average if, multiple if, divide if
 

--
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.
"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

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



0-0 Wai Wai ^-^ wrote:

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!

average if, multiple if, divide if
 

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

average if, multiple if, divide if
 

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

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

??

Thanks for your answer.


"Jerry W. Lewis" ???
???...
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

average if, multiple if, divide if
 

You're welcome.

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

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 P4:P134) to give you the mode of just the non-zero numbers.

Jerry

0-0 Wai Wai ^-^ wrote:

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

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

??

Thanks for your answer.


"Jerry W. Lewis" ???
???...

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