A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Average the POSITIVE values only



 
 
Thread Tools Display Modes
  #1  
Old November 8th 07, 04:09 AM posted to microsoft.public.excel.worksheet.functions
John
external usenet poster
 
Posts: 2,069
Default Average the POSITIVE values only

How do I average all the numbers in a column but only the positive numbers in
that column?
Ads
  #2  
Old November 8th 07, 04:20 AM posted to microsoft.public.excel.worksheet.functions
JMB
external usenet poster
 
Posts: 2,062
Default Average the POSITIVE values only

for example:
=AVERAGE(IF(A1:A6>0,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  
Old November 8th 07, 04:26 AM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 2,061
Default 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:A20>0,A1:A20))

That is an array formula; commit with ctrl-shift-Enter.

2. =sumif(A1:A20,">=0") / countif(A1:A20,">=0")

That is not an array formula; commit with Enter, as usual


  #4  
Old November 8th 07, 05:24 AM posted to microsoft.public.excel.worksheet.functions
John
external usenet poster
 
Posts: 2,069
Default 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:A20>0,A1:A20))
>
> That is an array formula; commit with ctrl-shift-Enter.
>
> 2. =sumif(A1:A20,">=0") / countif(A1:A20,">=0")
>
> That is not an array formula; commit with Enter, as usual
>
>
>

  #5  
Old November 8th 07, 08:31 AM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 2,061
Default 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  
Old November 8th 07, 05:52 PM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 2,061
Default 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 (non-array 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 outer-most 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 non-array
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 non-array 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 non-negative, and you will see that both non-
array formulas return the same non-erroneous result (but again, not
what was intended).

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Weighted Average of positive and negative % [email protected] Excel Worksheet Functions 8 December 9th 06 05:10 AM
Formula to make Negative Values Positive & Positive Values Negative? mustard Excel Discussion (Misc queries) 4 September 26th 05 10:05 PM
... Count, <<< Positive Values minus Negative Values >>> ... Dr. Darrell Excel Worksheet Functions 4 September 8th 05 01:36 PM
How do I average positive numbers only Susannah Excel Discussion (Misc queries) 2 February 15th 05 02:35 AM
average positive numbers Susannah Excel Discussion (Misc queries) 3 February 11th 05 02:47 AM


All times are GMT +1. The time now is 06:07 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.