Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
maryj
 
Posts: n/a
Default Averaging cells which contain #DIV/0!

This sounds like what I need, also. Except in my situation, the cells I need
to find the average of are not contiguous.

"Domenic" wrote:


Try...

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

...entered using CONTROL+SHIFT+ENTER.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=271242


  #2   Report Post  
Domenic
 
Posts: n/a
Default


maryj Wrote:
This sounds like what I need, also. Except in my situation, the cells I
need
to find the average of are not contiguous.


Which cells do you want to average?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=271242

  #3   Report Post  
maryj
 
Posts: n/a
Default

E10,E13,E16,E19,E22. These values were all derived from a sumif.


"Domenic" wrote:


maryj Wrote:
This sounds like what I need, also. Except in my situation, the cells I
need
to find the average of are not contiguous.


Which cells do you want to average?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=271242


  #4   Report Post  
Domenic
 
Posts: n/a
Default


Try the following array formulas, entered using CONTROL+SHIFT+ENTER...

=AVERAGE(IF((MOD(ROW(E10:E22),3)=1)*(ISNUMBER(E10: E22)),E10:E22))

OR

=IF(COUNT(E10,E13,E16,E19,E22),AVERAGE(IF((MOD(ROW (E10:E22),3)=1)*(ISNUMBER(E10:E22)),E10:E22)),"")

Hope this helps!

maryj Wrote:
E10,E13,E16,E19,E22. These values were all derived from a sumif.


"Domenic" wrote:


maryj Wrote:
This sounds like what I need, also. Except in my situation, the

cells I
need
to find the average of are not contiguous.


Which cells do you want to average?


--
Domenic

------------------------------------------------------------------------
Domenic's Profile:

http://www.excelforum.com/member.php...o&userid=10785
View this thread:

http://www.excelforum.com/showthread...hreadid=271242




--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=271242

  #5   Report Post  
maryj
 
Posts: n/a
Default

That did it! Thanks!

"Domenic" wrote:


Try the following array formulas, entered using CONTROL+SHIFT+ENTER...

=AVERAGE(IF((MOD(ROW(E10:E22),3)=1)*(ISNUMBER(E10: E22)),E10:E22))

OR

=IF(COUNT(E10,E13,E16,E19,E22),AVERAGE(IF((MOD(ROW (E10:E22),3)=1)*(ISNUMBER(E10:E22)),E10:E22)),"")

Hope this helps!

maryj Wrote:
E10,E13,E16,E19,E22. These values were all derived from a sumif.


"Domenic" wrote:


maryj Wrote:
This sounds like what I need, also. Except in my situation, the

cells I
need
to find the average of are not contiguous.

Which cells do you want to average?


--
Domenic

------------------------------------------------------------------------
Domenic's Profile:

http://www.excelforum.com/member.php...o&userid=10785
View this thread:

http://www.excelforum.com/showthread...hreadid=271242




--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=271242


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
To safety merge cells without data destroyed, and smart unmerge! Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:17 AM
Heps to design Locked/Unlocked cells in protected worksheet Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:09 AM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM
Averaging only cells with data Randy Lefferts Excel Discussion (Misc queries) 9 November 30th 04 09:02 PM


All times are GMT +1. The time now is 12:34 PM.

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"