A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

averaging separate cells in excel excluding 0 values



 
 
Thread Tools Display Modes
  #1  
Old July 9th 08, 06:39 AM posted to microsoft.public.excel.misc
Hopless & Challenged
external usenet poster
 
Posts: 1
Default 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  
Old July 9th 08, 09:33 AM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 11,501
Default 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  
Old July 9th 08, 10:13 AM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 11,501
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 05:22 PM
Averaging excluding min and max numbers n_gineer Excel Worksheet Functions 3 January 12th 06 04:32 PM
excluding repeating values neda5 Excel Discussion (Misc queries) 2 July 10th 05 11:59 PM


All times are GMT +1. The time now is 08:50 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.