| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Average Cells above 0
Hi Everone! I'm trying to average a group of cells, but only the cells over 0.
I tried this - =average(if(i2515:i5043)0,i2515:i5043)). I also entered it as an array but it still didn't work. Please help! -- Thanks! Stephen |
|
#2
|
|||
|
|||
|
Try...
=AVERAGE(IF(I2515:I50430,I2515:I5043)) Hope this helps! In article , "Stephen" wrote: Hi Everone! I'm trying to average a group of cells, but only the cells over 0. I tried this - =average(if(i2515:i5043)0,i2515:i5043)). I also entered it as an array but it still didn't work. Please help! |
|
#3
|
|||
|
|||
|
"Stephen" wrote in message
... Hi Everone! I'm trying to average a group of cells, but only the cells over 0. I tried this - =average(if(i2515:i5043)0,i2515:i5043)). I also entered it as an array but it still didn't work. Please help! -- Thanks! Stephen Try this =sumif(i2515:i5043,"0",i2515:i5043)/countif(i2515:i5043,"0") |
|
#4
|
|||
|
|||
|
Hi,
Use the following formula, =SUM(I2515:I5043)/COUNTIF(I2515:I5043,"0") Regards, B. R. Ramachandran "Stephen" wrote: Hi Everone! I'm trying to average a group of cells, but only the cells over 0. I tried this - =average(if(i2515:i5043)0,i2515:i5043)). I also entered it as an array but it still didn't work. Please help! -- Thanks! Stephen |
|
#5
|
|||
|
|||
|
The formula needs to be confirmed with CONTROL+SHIFT+ENTER.
In article , Domenic wrote: Try... =AVERAGE(IF(I2515:I50430,I2515:I5043)) Hope this helps! In article , "Stephen" wrote: Hi Everone! I'm trying to average a group of cells, but only the cells over 0. I tried this - =average(if(i2515:i5043)0,i2515:i5043)). I also entered it as an array but it still didn't work. Please help! |
|
#6
|
|||
|
|||
|
Thanks everone...it worked!
-- Thanks! Stephen "Domenic" wrote: The formula needs to be confirmed with CONTROL+SHIFT+ENTER. In article , Domenic wrote: Try... =AVERAGE(IF(I2515:I50430,I2515:I5043)) Hope this helps! In article , "Stephen" wrote: Hi Everone! I'm trying to average a group of cells, but only the cells over 0. I tried this - =average(if(i2515:i5043)0,i2515:i5043)). I also entered it as an array but it still didn't work. Please help! |
|
#7
|
|||
|
|||
|
On Fri, 23 Sep 2005 07:53:01 -0700, "B. R.Ramachandran"
wrote: Hi, Use the following formula, =SUM(I2515:I5043)/COUNTIF(I2515:I5043,"0") Regards, B. R. Ramachandran "Stephen" wrote: Hi Everone! I'm trying to average a group of cells, but only the cells over 0. I tried this - =average(if(i2515:i5043)0,i2515:i5043)). I also entered it as an array but it still didn't work. Please help! -- Thanks! Stephen Note that your formula does not meet the OP's request for "cells over 0". Had he asked for "cells not equal to 0" then your formula would be appropriate. However, your formula does not exclude those values "less than 0". --ron |
|
#8
|
|||
|
|||
|
Hi Ron,
Thanks for pointing out the error in the formula. I mistook that the OP wanted to average nonzero values, and realized the error later after posting my reply! Regards, B. R. Ramachandran "Ron Rosenfeld" wrote: On Fri, 23 Sep 2005 07:53:01 -0700, "B. R.Ramachandran" wrote: Hi, Use the following formula, =SUM(I2515:I5043)/COUNTIF(I2515:I5043,"0") Regards, B. R. Ramachandran "Stephen" wrote: Hi Everone! I'm trying to average a group of cells, but only the cells over 0. I tried this - =average(if(i2515:i5043)0,i2515:i5043)). I also entered it as an array but it still didn't work. Please help! -- Thanks! Stephen Note that your formula does not meet the OP's request for "cells over 0". Had he asked for "cells not equal to 0" then your formula would be appropriate. However, your formula does not exclude those values "less than 0". --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| locking a formula to cells | Excel Discussion (Misc queries) | |||
| Limit or Exclude cells in Average and Sum formula | Excel Worksheet Functions | |||
| Average form cells containing formulas? | Excel Worksheet Functions | |||
| How do i get an average that ignores blanks in the range of cells. | Excel Worksheet Functions | |||
| copy group of cells to another group of cells using "IF" in third | Excel Worksheet Functions | |||
Linear Mode
