| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the COUNT funciton to count cells with values
I have a COUNTIF function that counts only non-zero values.
Here it is: SUMIF(E$10:E$114,"<0")/COUNTIF(E$10:E$114,"<0") The problem is that some values are negative while some values are positive. I also need to count 0 as a value. Obviously, a regular count function would be fine, except for the fact that some values for my data set are missing, which ends up counting empty cells and messing up my running average calculation. Any help would really be appreciated. Thanks, Will --- Message posted from http://www.ExcelForum.com/ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using the COUNT funciton to count cells with values
I believe the COUNT counts only numbers (ignores blank cells), so i
should work for you. -- Message posted from http://www.ExcelForum.com |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using the COUNT funciton to count cells with values
Hi Will
try for example the following array formula =AVERAGE(IF(ISNUMBER(E10:E114),E10:E114)) -- Regards Frank Kabel Frankfurt, Germany I have a COUNTIF function that counts only non-zero values. Here it is: SUMIF(E$10:E$114,"<0")/COUNTIF(E$10:E$114,"<0") The problem is that some values are negative while some values are positive. I also need to count 0 as a value. Obviously, a regular count function would be fine, except for the fact that some values for my data set are missing, which ends up counting empty cells and messing up my running average calculation. Any help would really be appreciated. Thanks, Will --- Message posted from http://www.ExcelForum.com/ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using the COUNT funciton to count cells with values
Consider using the AVERAGE function. Blanks are ignored.
In article , wjsubs wrote: I have a COUNTIF function that counts only non-zero values. Here it is: SUMIF(E$10:E$114,"<0")/COUNTIF(E$10:E$114,"<0") The problem is that some values are negative while some values are positive. I also need to count 0 as a value. Obviously, a regular count function would be fine, except for the fact that some values for my data set are missing, which ends up counting empty cells and messing up my running average calculation. Any help would really be appreciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using the COUNT funciton to count cells with values
Hi
forget this suggestion :-) Simply use =AVERAGE(E10:E114) -- Regards Frank Kabel Frankfurt, Germany Frank Kabel wrote: Hi Will try for example the following array formula =AVERAGE(IF(ISNUMBER(E10:E114),E10:E114)) I have a COUNTIF function that counts only non-zero values. Here it is: SUMIF(E$10:E$114,"<0")/COUNTIF(E$10:E$114,"<0") The problem is that some values are negative while some values are positive. I also need to count 0 as a value. Obviously, a regular count function would be fine, except for the fact that some values for my data set are missing, which ends up counting empty cells and messing up my running average calculation. Any help would really be appreciated. Thanks, Will --- Message posted from http://www.ExcelForum.com/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count cells with values x and <y | Excel Discussion (Misc queries) | |||
| Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| How do I count cells with non zero values? | Excel Discussion (Misc queries) | |||
| Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
![wjsubs[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
