Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the average across multiple sheets | Excel Worksheet Functions | |||
Calculating average scores from multiple sheets' information | Excel Worksheet Functions | |||
MAX, MIN, MEAN, AVERAGE, MODE for multiple groups...how? | Excel Worksheet Functions | |||
What is this kind of average called? | Excel Worksheet Functions | |||
Statistics (average of multiple divisions) | Excel Worksheet Functions |