Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Keithlearn
 
Posts: n/a
Default Average non continguous cells, excluding zero's

I am trying to average cells that are non contingous, and also need to
exlcude any zero's in the mix. All the posts I have read count on either
sumproduct or countif both of which don't seem to be able to accept non
contingous cells. Is there something that might be able to help.

An example of the simple average formula is

=AVERAGE(E92,E71,E50,E29,E8)

Thanks,

Keith
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

You can't, it takes an array formula or a combination of sum and countif but
an array formula demands a range so does countif, there is one way using a
workaround, here's an example using different ranges


=SUMPRODUCT(--(LARGE((B1:B4,D1:E4,G1:G4),ROW(INDIRECT("1:"&COUNT (B1:B4,D1:E4,G1:G4))))<0))

if they all are single cells just put a comma between them, so to get
average of the above range

=SUM(B1:B4,D1:E4,G1:G4)/SUMPRODUCT(--(LARGE((B1:B4,D1:E4,G1:G4),ROW(INDIRECT("1:"&COUNT (B1:B4,D1:E4,G1:G4))))<0))



--
Regards,

Peo Sjoblom


"Keithlearn" wrote in message
...
I am trying to average cells that are non contingous, and also need to
exlcude any zero's in the mix. All the posts I have read count on either
sumproduct or countif both of which don't seem to be able to accept non
contingous cells. Is there something that might be able to help.

An example of the simple average formula is

=AVERAGE(E92,E71,E50,E29,E8)

Thanks,

Keith


  #3   Report Post  
bj
 
Posts: n/a
Default

A brute force work around is to set up a column (say Y) as a dummy column and
enter
=E92 in Y1,=E71in Y2,etc.
you then have the contiguous cells necessary for Sumif or sum product methods.

"Keithlearn" wrote:

I am trying to average cells that are non contingous, and also need to
exlcude any zero's in the mix. All the posts I have read count on either
sumproduct or countif both of which don't seem to be able to accept non
contingous cells. Is there something that might be able to help.

An example of the simple average formula is

=AVERAGE(E92,E71,E50,E29,E8)

Thanks,

Keith

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Average the Last Five Cells in a Column Warrior Princess Excel Worksheet Functions 3 March 16th 05 03:12 PM
How do I use an average function, not counting cells containing a Ryder Excel Worksheet Functions 2 March 16th 05 01:05 PM
average of spaced cells Blessingspoint Excel Worksheet Functions 1 January 14th 05 10:03 PM
EXcluding Zeros from the average in a row Geo Excel Discussion (Misc queries) 4 December 31st 04 05:07 PM
Automatic copying data excluding blank cells Wesley Excel Worksheet Functions 6 November 30th 04 02:17 AM


All times are GMT +1. The time now is 06:16 AM.

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

About Us

"It's about Microsoft Excel"