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.

 averaging separate cells in excel excluding 0 values
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

averaging separate cells in excel excluding 0 values

#1
July 9th 08, 06:39 AM posted to microsoft.public.excel.misc
 Hopless & Challenged external usenet poster Posts: 1
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
July 9th 08, 09:33 AM posted to microsoft.public.excel.misc
 Mike H external usenet poster Posts: 11,501
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
 Mike H external usenet poster Posts: 11,501
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 Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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

All times are GMT +1. The time now is 03:37 PM.