Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 20th 05, 07:18 PM
dave roth
 
Posts: n/a
Default average of visible cells in a filtered range

Good Afternoon:

I need to average only the visible cells in a range which has been filtered,
i.e.

=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<0)).

I now want to average the age of the victims. I've tried several functions,
including =Average(sumproduct....),
=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<0))/COUNTIF(Age_2005,"<0"), and some others.

TIA for your assistance.

  #2   Report Post  
Old May 20th 05, 07:21 PM
Ron Coderre
 
Posts: n/a
Default

If the range is filtered with either Autofilter or Advanced Filter, you
should check Excel Help for the SUBTOTAL function.

That function will Sum, Average, etc on only the visible items in a filtered
list.

See if that works.

--
Regards,
Ron

  #3   Report Post  
Old May 20th 05, 07:25 PM
Duke Carey
 
Posts: n/a
Default

=subtotal(1,age column)

will work on (average) only the visible cells

"dave roth" wrote:

Good Afternoon:

I need to average only the visible cells in a range which has been filtered,
i.e.

=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<0)).

I now want to average the age of the victims. I've tried several functions,
including =Average(sumproduct....),
=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<0))/COUNTIF(Age_2005,"<0"), and some others.

TIA for your assistance.

  #4   Report Post  
Old May 20th 05, 11:42 PM
Aladin Akyurek
 
Posts: n/a
Default

dave roth wrote:
Good Afternoon:

I need to average only the visible cells in a range which has been filtered,
i.e.

=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<0)).

I now want to average the age of the victims. I've tried several functions,
including =Average(sumproduct....),
=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<0))/COUNTIF(Age_2005,"<0"), and some others.

TIA for your assistance.


What are the real ranges and on which range is autofiltering is applied?
  #5   Report Post  
Old May 21st 05, 10:55 AM
Ola
 
Posts: n/a
Default

This is one option
=AVERAGE(IF((InjuryType1_2005="Gunshot")*(Age_2005 <0),Age_2005))

Confirm the Array-formula with Ctrl + Shift then hit Enter

Hope it helped
Ola Sandström



  #6   Report Post  
Old May 23rd 05, 12:56 PM
dave roth
 
Posts: n/a
Default

Many thanks.

"Duke Carey" wrote:

=subtotal(1,age column)

will work on (average) only the visible cells

"dave roth" wrote:

Good Afternoon:

I need to average only the visible cells in a range which has been filtered,
i.e.

=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<0)).

I now want to average the age of the victims. I've tried several functions,
including =Average(sumproduct....),
=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<0))/COUNTIF(Age_2005,"<0"), and some others.

TIA for your assistance.



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
Counting empty cells within a range of cells Rosehill - ExcelForums.com New Users to Excel 2 May 2nd 05 08:53 AM
Applying formula to only NON-EMPTY cells in range Tasi Excel Discussion (Misc queries) 5 March 29th 05 10:48 PM
Count cells in one range based on parameters in another range dave roth Excel Worksheet Functions 2 March 29th 05 05:33 PM
Visible cells Melissa Excel Worksheet Functions 0 February 15th 05 07:23 PM
Strange Problem with Chart and Plot Visible Cells Option Marcus Leon Charts and Charting in Excel 1 January 7th 05 03:52 AM


All times are GMT +1. The time now is 12:31 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017