|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 9th 08, 06:39 AM
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? 
|
| Ads |
|
#2
July 9th 08, 09:33 AM
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:H30>0),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
July 9th 08, 10:13 AM
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:H30>0),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?
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Averaging excluding blanks--Multiple Tabs | DaS | Excel Worksheet Functions | 2 | June 17th 08 05:03 PM |
| Excluding selected cells in excel | Farhad | Excel Discussion (Misc queries) | 3 | May 29th 06 08:45 PM |
| How do I use countif to count values excluding blank cells | Glenda | Excel Worksheet Functions | 4 | January 30th 06 04:22 PM |
| Averaging excluding min and max numbers | n_gineer | Excel Worksheet Functions | 3 | January 12th 06 03:32 PM |
| excluding repeating values | neda5 | Excel Discussion (Misc queries) | 2 | July 10th 05 11:59 PM |
Linear Mode
