Remember Me?

#1
May 20th 05, 07:18 PM
 dave roth Posts: n/a
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.

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

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
May 20th 05, 07:25 PM
 Duke Carey Posts: n/a

=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.

#4
May 20th 05, 11:42 PM

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.

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

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
May 23rd 05, 12:56 PM
 dave roth Posts: n/a

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Rosehill - ExcelForums.com New Users to Excel 2 May 2nd 05 08:53 AM Tasi Excel Discussion (Misc queries) 5 March 29th 05 10:48 PM dave roth Excel Worksheet Functions 2 March 29th 05 05:33 PM Melissa Excel Worksheet Functions 0 February 15th 05 08:23 PM Marcus Leon Charts and Charting in Excel 1 January 7th 05 04:52 AM

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