Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging numbers in a column while excluding blank spaces and zer
I am using office 2003 and have the numbers 0, 0, 5, 0, 0, 5, 0, 0, 5, 0, 0,
5 in column G. How do I average only those blocks that contain a number greater than zero no matter what blocks in that column might contain a number greater than zero? Thank You |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging numbers in a column while excluding blank spaces and zer
Try one of these:
=IF(COUNTIF(G2:G13,"0"),SUMIF(G2:G13,"0",G2:G13)/COUNTIF(G2:G13,"0"),"") This is an array formula** : =IF(COUNTIF(G2:G13,"0"),AVERAGE(IF(G2:G130,G2:G1 3)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. This one will return a 0 if no entries are greater than 0. The other formulas will return a blank: =SUM(G2:G13)/MAX(1,COUNTIF(G2:G13,"0")) -- Biff Microsoft Excel MVP "c. murphy" <c. wrote in message ... I am using office 2003 and have the numbers 0, 0, 5, 0, 0, 5, 0, 0, 5, 0, 0, 5 in column G. How do I average only those blocks that contain a number greater than zero no matter what blocks in that column might contain a number greater than zero? Thank You |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging numbers in a column while excluding blank spaces and zer
Hi,
One way is =SUMIF(G:G,"0")/COUNTIF(G:G,"0") or use the array =AVERAGE(IF(G:G0,G:G,"")) to make it an array press Shift+Ctrl+Enter to enter it, not Enter. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "c. murphy" wrote: I am using office 2003 and have the numbers 0, 0, 5, 0, 0, 5, 0, 0, 5, 0, 0, 5 in column G. How do I average only those blocks that contain a number greater than zero no matter what blocks in that column might contain a number greater than zero? Thank You |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging numbers in a column while excluding blank spaces and zer
or use the array
=AVERAGE(IF(G:G0,G:G,"")) That will only work in Excel 2007. In all other versions you can't use entire columns as range references. Even if you do have Excel 2007 and if you aren't using all 1,048,576 rows, referencing the entire column in array formulas is *very* inefficient. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, One way is =SUMIF(G:G,"0")/COUNTIF(G:G,"0") or use the array =AVERAGE(IF(G:G0,G:G,"")) to make it an array press Shift+Ctrl+Enter to enter it, not Enter. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "c. murphy" wrote: I am using office 2003 and have the numbers 0, 0, 5, 0, 0, 5, 0, 0, 5, 0, 0, 5 in column G. How do I average only those blocks that contain a number greater than zero no matter what blocks in that column might contain a number greater than zero? Thank You |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging numbers in a column while excluding blank spaces and zer
=IF(COUNTIF(G2:G13,"0"),SUMIF(G2:G13,"0",G2:G13 )/COUNTIF(G2:G13,"0"),"")
No need to repeat the range in SUMIF. Can be reduced to: =IF(COUNTIF(G2:G13,"0"),SUMIF(G2:G13,"0")/COUNTIF(G2:G13,"0"),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try one of these: =IF(COUNTIF(G2:G13,"0"),SUMIF(G2:G13,"0",G2:G13)/COUNTIF(G2:G13,"0"),"") This is an array formula** : =IF(COUNTIF(G2:G13,"0"),AVERAGE(IF(G2:G130,G2:G1 3)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. This one will return a 0 if no entries are greater than 0. The other formulas will return a blank: =SUM(G2:G13)/MAX(1,COUNTIF(G2:G13,"0")) -- Biff Microsoft Excel MVP "c. murphy" <c. wrote in message ... I am using office 2003 and have the numbers 0, 0, 5, 0, 0, 5, 0, 0, 5, 0, 0, 5 in column G. How do I average only those blocks that contain a number greater than zero no matter what blocks in that column might contain a number greater than zero? Thank You |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
averaging separate cells in excel excluding 0 values | Excel Discussion (Misc queries) | |||
Averaging excluding blanks--Multiple Tabs | Excel Worksheet Functions | |||
Excel 2002: How to add blank spaces to text and numbers | Excel Discussion (Misc queries) | |||
Counting non-blank cells in a column, excluding hidden rows | Excel Worksheet Functions | |||
Averaging excluding min and max numbers | Excel Worksheet Functions |