Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I am looking to write an AVG function that doesn't count
cells with zero's in it. The information is all in one column. Some cells just habve no info. Can anyone help? Thanks, Steve |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Steve:
does this work for you? =SUMIF(A2:A5,"<0")/COUNTIF(A2:A5,"<0") keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Steve" wrote: I am looking to write an AVG function that doesn't count cells with zero's in it. The information is all in one column. Some cells just habve no info. Can anyone help? Thanks, Steve |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
my number come out way low.
-----Original Message----- Steve: does this work for you? =SUMIF(A2:A5,"<0")/COUNTIF(A2:A5,"<0") keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Steve" wrote: I am looking to write an AVG function that doesn't count cells with zero's in it. The information is all in one column. Some cells just habve no info. Can anyone help? Thanks, Steve . |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
hmm.. doesnt work great with empty cells, does it?
try: {=SUM(A$1:$A$5)/COUNT(IF(A$1:$A$5=0,"",A$1:$A$5))} it's an array formula: enter without the {} but close with ctrl-shift-enter you'll see the {} in the formula bar. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Steve" wrote: my number come out way low. -----Original Message----- Steve: does this work for you? =SUMIF(A2:A5,"<0")/COUNTIF(A2:A5,"<0") keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Steve" wrote: I am looking to write an AVG function that doesn't count cells with zero's in it. The information is all in one column. Some cells just habve no info. Can anyone help? Thanks, Steve . |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Or .../(COUTIF(range,"0")+COUNTIF(range,"<0"))
Jerry keepITcool wrote: hmm.. doesnt work great with empty cells, does it? try: {=SUM(A$1:$A$5)/COUNT(IF(A$1:$A$5=0,"",A$1:$A$5))} it's an array formula: enter without the {} but close with ctrl-shift-enter you'll see the {} in the formula bar. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Steve" wrote: my number come out way low. -----Original Message----- Steve: does this work for you? =SUMIF(A2:A5,"<0")/COUNTIF(A2:A5,"<0") keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Steve" wrote: I am looking to write an AVG function that doesn't count cells with zero's in it. The information is all in one column. Some cells just habve no info. Can anyone help? Thanks, Steve . |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
On Thu, 1 Jul 2004 16:53:41 -0700, "Steve" wrote:
I am looking to write an AVG function that doesn't count cells with zero's in it. The information is all in one column. Some cells just habve no info. Can anyone help? Thanks, Steve Use the array formula: =AVERAGE(IF(rng<0,rng)) To enter an array formula, hold down <ctrl<shift while hitting <enter. XL will place brackets {...} around the formula. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |