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? |
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? |
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? |
All times are GMT +1. The time now is 09:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com