Home 
Search 
Today's Posts 
#1




Average the POSITIVE values only
How do I average all the numbers in a column but only the positive numbers in
that column? 
#2




Average the POSITIVE values only
for example:
=AVERAGE(IF(A1:A60,A1:A6)) array entered with Cntrl+Shift+Enter or try: =AVERAGE(IF((A1:A6=0)*(LEN(A1:A6)),A1:A6)) to include zero values (and exclude empty cells that would be treated as zero values) "John" wrote: How do I average all the numbers in a column but only the positive numbers in that column? 
#3




Average the POSITIVE values only
On Nov 7, 7:09 pm, John wrote:
How do I average all the numbers in a column but only the positive numbers in that column? Two methods come to mind. 1. =average(if(A1:A200,A1:A20)) That is an array formula; commit with ctrlshiftEnter. 2. =sumif(A1:A20,"=0") / countif(A1:A20,"=0") That is not an array formula; commit with Enter, as usual 
#4




Average the POSITIVE values only
Thanks! By the way, how do you enter an arrayed function...or what does that
mean exactly? Is it just a formula that, after you enter into the Fx bar, you hit ctrl+shft+enter instead of just enter? "joeu2004" wrote: On Nov 7, 7:09 pm, John wrote: How do I average all the numbers in a column but only the positive numbers in that column? Two methods come to mind. 1. =average(if(A1:A200,A1:A20)) That is an array formula; commit with ctrlshiftEnter. 2. =sumif(A1:A20,"=0") / countif(A1:A20,"=0") That is not an array formula; commit with Enter, as usual 
#5




Average the POSITIVE values only
On Nov 7, 8:24 pm, John wrote:
By the way, how do you enter an arrayed function...or what does that mean exactly? Is it just a formula that, after you enter into the Fx bar, you hit ctrl+shft+enter instead of just enter? Yes, that is what we mean by "commit" with ctrl+shift+Enter. An array formula is one that operates on an array of values. In the example, if A1,A3,A5,etc (all odd rows) are positive, then: =average(if(A1:A20=0, A1:A20)) is effectively: =average({A1,A3,A5,...,A19}) But we cannot enter an array of that form. The problem with array formulas is they are difficult to edit. After editing, always remember to "commit" with ctrl+shift+Enter. If you get #VALUE, select the cell, press F2, then press ctrl+shift+Enter. Sometimes, you will not get #VALUE, but you will not get the result you expect either; it depends on the function(s) used in the array formula. Confusing! To verify that it is an array formula, select the cell and be sure that the entire formula after "=" is enclosed in curly braces (i.e. ={...}). 
#6




Average the POSITIVE values only
Errata....
On Nov 7, 11:31 pm, I wrote: On Nov 7, 8:24 pm, John wrote: By the way, how do you enter an arrayed function...or what does that mean exactly? Is it just a formula that, after you enter into the Fx bar, you hit ctrl+shft+enter instead of just enter? Yes, that is what we mean by "commit" with ctrl+shift+Enter. That much is correct. But lots of mistakes in details in the rest of my explanation. An array formula is one that operates on an array of values. In the example, if A1,A3,A5,etc (all odd rows) are positive, then: =average(if(A1:A20=0, A1:A20)) To gain some appreciation of the difference between an array and non array formula, try entering the above example as both  that is, commit with Enter one time (nonarray formula) and with ctrl+shift +Enter another time (array formula). Each time, use Tools = Formula Auditing = Evaluate Formula to step through the evaluation of the formula. But we cannot enter an array of that form. [....] To verify that it is an array formula, select the cell and be sure that the entire formula after "=" is enclosed in curly braces (i.e. ={...}). What I should have said is: An array formula is entirely embraced in curly brackets (i.e. {=...}). And what I meant to say is: We cannot enter an array formula by typing the outermost curly braces explicitly. To gain some insight (or not) into the vagaries of Excel, try the above example with the following formulas committed as both nonarray and array formulas. =sum(if(A1:A20=0,A1:A20)) =sumproduct(if(A1:A20=0,A1:A20)) As array formulas, both return the same result, as they should. But as nonarray formulas, when A2 is negative, note that the SUMPRODUCT formula fails with #VALUE, whereas the SUM formula works (albeit not with the intended result). Use Evaluate Formula to see why (klunk!). Go figure! Change A2 to nonnegative, and you will see that both non array formulas return the same nonerroneous result (but again, not what was intended). 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Weighted Average of positive and negative %  Excel Worksheet Functions  
Formula to make Negative Values Positive & Positive Values Negative?  Excel Discussion (Misc queries)  
... Count, <<< Positive Values minus Negative Values >>> ...  Excel Worksheet Functions  
How do I average positive numbers only  Excel Discussion (Misc queries)  
average positive numbers  Excel Discussion (Misc queries) 