Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
HI
I am trying to average a row of numbers (F35:U35) that have numeric zeros in some of the cells. However, I would like to exclude them, and the cells from the calculation "=AVERAGE(F35:U35)". Is there a way to do that? Thanks -- Geo |
#2
![]() |
|||
|
|||
![]()
Hi Geo
This array formula will do the job: =AVERAGE(IF(F35:U35<0,F35:U35)) To be entered with <Shift<Ctrl<Enter instead of <Enter, also if edited later. -- Best Regards Leo Heuser Followup to newsgroup only please. "Geo" skrev i en meddelelse ... HI I am trying to average a row of numbers (F35:U35) that have numeric zeros in some of the cells. However, I would like to exclude them, and the cells from the calculation "=AVERAGE(F35:U35)". Is there a way to do that? Thanks -- Geo |
#3
![]() |
|||
|
|||
![]()
In addition to Leo's array formula, you also have the
option of using: =SUM(F35:U35)/COUNTIF(F35:U35,"<0") so long as your range does not contain any empty cells or non-numerical values. HTH Jason Atlanta, GA -----Original Message----- HI I am trying to average a row of numbers (F35:U35) that have numeric zeros in some of the cells. However, I would like to exclude them, and the cells from the calculation "=AVERAGE(F35:U35)". Is there a way to do that? Thanks -- Geo . |
#4
![]() |
|||
|
|||
![]()
Empty cells are not equal to zero.
=SUM(F35:U35)/(COUNTIF(F35:U35,"0")+COUNTIF(F35:U35,"<0")) is more bullet proof. Jerry Jason Morin wrote: In addition to Leo's array formula, you also have the option of using: =SUM(F35:U35)/COUNTIF(F35:U35,"<0") so long as your range does not contain any empty cells or non-numerical values. HTH Jason Atlanta, GA -----Original Message----- HI I am trying to average a row of numbers (F35:U35) that have numeric zeros in some of the cells. However, I would like to exclude them, and the cells from the calculation "=AVERAGE(F35:U35)". Is there a way to do that? Thanks -- Geo |
#5
![]() |
|||
|
|||
![]()
I know empty cells are not equal to zero. That's why I
stated this formula is limited to non-numerical values and and no empty cells. Your formula is definitely more bullet proof in that regard. Jason -----Original Message----- Empty cells are not equal to zero. =SUM(F35:U35)/(COUNTIF(F35:U35,"0")+COUNTIF (F35:U35,"<0")) is more bullet proof. Jerry Jason Morin wrote: In addition to Leo's array formula, you also have the option of using: =SUM(F35:U35)/COUNTIF(F35:U35,"<0") so long as your range does not contain any empty cells or non-numerical values. HTH Jason Atlanta, GA -----Original Message----- HI I am trying to average a row of numbers (F35:U35) that have numeric zeros in some of the cells. However, I would like to exclude them, and the cells from the calculation "=AVERAGE(F35:U35)". Is there a way to do that? Thanks -- Geo . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
is there a way to hide zeros in a line graph | Charts and Charting in Excel | |||
Formula to average ignoring negatives? | Excel Discussion (Misc queries) | |||
Average Macro | Excel Discussion (Misc queries) | |||
How to show zeros (0) as "slashed" to eliminate confusion with "O. | Excel Discussion (Misc queries) | |||
average on rows | Excel Discussion (Misc queries) |