Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 99
Default Non-contiguous range and Sumproduct to average

Hi,

I'm using the following to average data in K if value in F = value in E5.

=SUMPRODUCT(($F$52:$F$107=$E$5)*$K$52:$K$107)/SUMPRODUCT(--($F$52:$F$107=$E$5))

I'm intending to use the same formula to calculate averages in columns I to
N in about 45 sheets all with the same layout and different data.

Column F is gender and I to N contain a series of datapoints (some of which
are missing).
I've tried as far as possible to sort the data so that the missing points
are at the bottom of rows and then I alter the range in the formula to suit.

I'm on sheet 5 now and I'm stumped, L109 to N111 and K108 have no data but
there is data in K109 to K111.

I've tried adding a second range to the formula but not having much luck
(although I'm guessing I'm lucky to have got to sheet 5 without this problem).

Hoping some kind soul can help :-)

Cheers
Mifty

--
Mifty
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Non-contiguous range and Sumproduct to average

Try this :

=SUMPRODUCT( ($F$52:$F$111=$E$5) * ($K$52:$K$111) ) / SUMPRODUCT(
($F$52:$F$111=$E$5) * (ISNUMBER($K$52:$K$111) ) )





"Mifty" wrote:

Hi,

I'm using the following to average data in K if value in F = value in E5.

=SUMPRODUCT(($F$52:$F$107=$E$5)*$K$52:$K$107)/SUMPRODUCT(--($F$52:$F$107=$E$5))

I'm intending to use the same formula to calculate averages in columns I to
N in about 45 sheets all with the same layout and different data.

Column F is gender and I to N contain a series of datapoints (some of which
are missing).
I've tried as far as possible to sort the data so that the missing points
are at the bottom of rows and then I alter the range in the formula to suit.

I'm on sheet 5 now and I'm stumped, L109 to N111 and K108 have no data but
there is data in K109 to K111.

I've tried adding a second range to the formula but not having much luck
(although I'm guessing I'm lucky to have got to sheet 5 without this problem).

Hoping some kind soul can help :-)

Cheers
Mifty

--
Mifty

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Non-contiguous range and Sumproduct to average

By missing data points I assume you numbers to average.

Try this array formula** :


=AVERAGE(IF(F52:F107=E5,IF(ISNUMBER(K52:K107),K52: K107)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Mifty" wrote in message
...
Hi,

I'm using the following to average data in K if value in F = value in E5.

=SUMPRODUCT(($F$52:$F$107=$E$5)*$K$52:$K$107)/SUMPRODUCT(--($F$52:$F$107=$E$5))

I'm intending to use the same formula to calculate averages in columns I
to
N in about 45 sheets all with the same layout and different data.

Column F is gender and I to N contain a series of datapoints (some of
which
are missing).
I've tried as far as possible to sort the data so that the missing points
are at the bottom of rows and then I alter the range in the formula to
suit.

I'm on sheet 5 now and I'm stumped, L109 to N111 and K108 have no data but
there is data in K109 to K111.

I've tried adding a second range to the formula but not having much luck
(although I'm guessing I'm lucky to have got to sheet 5 without this
problem).

Hoping some kind soul can help :-)

Cheers
Mifty

--
Mifty



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default Non-contiguous range and Sumproduct to average

"T. Valko" wrote...
By missing data points I assume you numbers to average.

....
=AVERAGE(IF(F52:F107=E5,IF(ISNUMBER(K52:K107),K52 :K107)))

....

Why the IF call? With the following data in A1:B8,

F 1
F <blank
M 3
M 4
F 5
F 6
F 7
M 8

and F in A10, the array formula

=AVERAGE(IF(A1:A8=A10,B1:B8))

returns 4.75 (as per specs) rather than 3.8. The AVERAGE function
ignores cells which don't contain numbers.


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Non-contiguous range and Sumproduct to average

Why the IF call?
F <blank


Define blank.

If blank is an EMPTY cell then ISNUMBER prevents the empty cell from being
included in the average as numeric 0. If the logical test is TRUE the
corresponding cell in the value_if_true argument is included in the average
and if that cell is empty it's evaluated as numeric 0.

If BLANK is a formula blank then it would be ignored as a text entry in an
array reference.

Are you sure you just didn't have a "senior moment"? It's ok, I have them
sometimes and I'm not technically a senior just yet!

--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"T. Valko" wrote...
By missing data points I assume you numbers to average.

...
=AVERAGE(IF(F52:F107=E5,IF(ISNUMBER(K52:K107),K5 2:K107)))

...

Why the IF call? With the following data in A1:B8,

F 1
F <blank
M 3
M 4
F 5
F 6
F 7
M 8

and F in A10, the array formula

=AVERAGE(IF(A1:A8=A10,B1:B8))

returns 4.75 (as per specs) rather than 3.8. The AVERAGE function
ignores cells which don't contain numbers.



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
calculate average from non contiguous column, ignoring (0) values Jesse Excel Worksheet Functions 3 June 30th 09 10:41 PM
average of non-contiguous range based on criteria gtslabs Excel Worksheet Functions 3 October 14th 08 11:47 AM
Average non contiguous row Scott Kieta Excel Discussion (Misc queries) 5 July 10th 07 08:56 PM
how can i use SUMPRODUCT with a non-contiguous set of cells? Wade Wilson Excel Discussion (Misc queries) 1 May 9th 05 07:24 PM
Average Non-Contiguous numbers Teri Excel Worksheet Functions 1 January 20th 05 09:33 PM


All times are GMT +1. The time now is 11:24 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"