Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging separate cells in excel excluding 0 values
Is it possible to average separate cells (i.e. H18,H22,H28,H30 and only
those cells, not columns) And have it display the average of those in B30? mean while excluding all 0 values? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging separate cells in excel excluding 0 values
Hi,
Try this =AVERAGE(IF(ISNUMBER(MATCH(ROW(H1:H30),{18,22,28,3 0},0))*(H1:H300),H1:H30)) This an array which must be commited using Ctrl+Shift+Enter NOT by simply pressing Enter. If you do it correctly Excel will put curly brackets {} around the formula. you cannot type these yourself. Mike "Hopless & Challenged" wrote: Is it possible to average separate cells (i.e. H18,H22,H28,H30 and only those cells, not columns) And have it display the average of those in B30? mean while excluding all 0 values? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
averaging separate cells in excel excluding 0 values
Ah,
You said exceluding 0 and not greater than zero so you need this slight modification =AVERAGE(IF(ISNUMBER(MATCH(ROW(H1:H30),{18,22,28,3 0},0))*(H1:H30<0),H1:H30)) Mike "Mike H" wrote: Hi, Try this =AVERAGE(IF(ISNUMBER(MATCH(ROW(H1:H30),{18,22,28,3 0},0))*(H1:H300),H1:H30)) This an array which must be commited using Ctrl+Shift+Enter NOT by simply pressing Enter. If you do it correctly Excel will put curly brackets {} around the formula. you cannot type these yourself. Mike "Hopless & Challenged" wrote: Is it possible to average separate cells (i.e. H18,H22,H28,H30 and only those cells, not columns) And have it display the average of those in B30? mean while excluding all 0 values? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging excluding blanks--Multiple Tabs | Excel Worksheet Functions | |||
Excluding selected cells in excel | Excel Discussion (Misc queries) | |||
How do I use countif to count values excluding blank cells | Excel Worksheet Functions | |||
Averaging excluding min and max numbers | Excel Worksheet Functions | |||
excluding repeating values | Excel Discussion (Misc queries) |