Non-contiguous range and Sumproduct to average
in [Functions]
|
From: Mifty on 11 Apr 2010 16:27 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
From: Charabeuh on 11 Apr 2010 18:11 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" <Mifty(a)discussions.microsoft.com> a écrit dans le message de groupe de discussion : 0295AB6B-CB99-4214-9F53-B3D7E5718512(a)microsoft.com... > 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
From: Charabeuh on 11 Apr 2010 18:29 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
From: T. Valko on 11 Apr 2010 21:29 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" <Mifty(a)discussions.microsoft.com> wrote in message news:0295AB6B-CB99-4214-9F53-B3D7E5718512(a)microsoft.com... > 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
From: Harlan Grove on 12 Apr 2010 01:00
"T. Valko" <biffinp...(a)comcast.net> 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. |