Help! How do I ignore blanks cells in an array?

[jblanks7]

I'm compiling some stats and I want to figure out how to ignore blan
cells in a range within an array. I'm a rookie to all this, so spea
"idiot" to me.

Specifically, I'm trying to factor how consistent players are b
calculating how many weeks were spent in various ranges based on thei
overall average. The issue I'm having is that each player didn't pla
each week, which is messing up the results.

This is where I'm at right now:

=SUM((G38:U38>=(AC38-50))*(G38:U38<=(AC38-10)))

This shows a 16 week season (G38:U38 represents each week). AC3
represents the player's season average. The issue is that this playe
only played in 8 of the team's 16 weeks. The average is correct, but th
array is counting the 8 weeks that he didn't play as "0."

(This particular array is displaying the number of weeks the player wa
10-50 points under his season average.)

Can anybody out there help a stat-geek learn a new trick

 

[joeu2004]

=SUM((G38:U38>=(AC38-50))*(G38:U38<=(AC38-10)))

This shows a 16 week season (G38:U38 represents each week).
AC38 represents the player's season average. The issue is
that this player only played in 8 of the team's 16 weeks.
The average is correct, but the array is counting the 8
weeks that he didn't play as "0."

I think you want the following array-entered formula:

=SUM((G38:U38>=AC38-50)*(G38:U38<=AC38-10)*(G38:U38<>""))

I think you know that "array-entered" means press ctrl+shift+Enter instead
of just Enter.

I find that array-entered formulas are often difficult to maintain. And
sometimes they __appear__ to work (i.e. there is no error) when we make the
mistake of simply pressing Enter; but in fact, the result is incorrect.

For that reason, I prefer to use SUMPRODUCT for such formulas.
Normally-enter (just press Enter) the following formula:

=SUMPRODUCT((G38:U38>=AC38-50)*(G38:U38<=AC38-10)*(G38:U38<>""))

Also, if you are using Excel 2007 or later and you do not need Excel 2003
compatibility (e.g. to share the file with others who might have older Excel
versions), you could use COUNTIFS, to wit:

=COUNTIFS(G38:U38,">="&AC38-50,G38:U38,"<="&AC38-10,G38:U38,"<>")

 

[Spencer101]

I'm compiling some stats and I want to figure out how to ignore blan
cells in a range within an array. I'm a rookie to all this, so spea
"idiot" to me.

Specifically, I'm trying to factor how consistent players are b
calculating how many weeks were spent in various ranges based on thei
overall average. The issue I'm having is that each player didn't pla
each week, which is messing up the results.

This is where I'm at right now:

=SUM((G38:U38>=(AC38-50))*(G38:U38<=(AC38-10)))

This shows a 16 week season (G38:U38 represents each week). AC3
represents the player's season average. The issue is that this playe
only played in 8 of the team's 16 weeks. The average is correct, but th
array is counting the 8 weeks that he didn't play as "0."

(This particular array is displaying the number of weeks the player wa
10-50 points under his season average.)

Can anybody out there help a stat-geek learn a new trick?

Hi,

As a fellow "stat-geek" I'm happy to help, but perhaps if you poste
some example data it would make things easier.

S

 

[jblanks7]

=SUM((G38:U38>=(AC38-50))*(G38:U38<=(AC38-10)))

This shows a 16 week season (G38:U38 represents each week).
AC38 represents the player's season average. The issue is
that this player only played in 8 of the team's 16 weeks.
The average is correct, but the array is counting the 8
weeks that he didn't play as "0."-

I think you want the following array-entered formula:

=SUM((G38:U38>=AC38-50)*(G38:U38<=AC38-10)*(G38:U38<>""))

I think you know that "array-entered" means press ctrl+shift+Ente
instead
of just Enter.

I find that array-entered formulas are often difficult to maintain. An

sometimes they __appear__ to work (i.e. there is no error) when we mak
the
mistake of simply pressing Enter; but in fact, the result is incorrect.

For that reason, I prefer to use SUMPRODUCT for such formulas.
Normally-enter (just press Enter) the following formula:

=SUMPRODUCT((G38:U38>=AC38-50)*(G38:U38<=AC38-10)*(G38:U38<>""))

Also, if you are using Excel 2007 or later and you do not need Exce
2003
compatibility (e.g. to share the file with others who might have olde
Excel
versions), you could use COUNTIFS, to wit:

=COUNTIFS(G38:U38,">="&AC38-50,G38:U38,"<="&AC38-10,G38:U38,"<>")

Thanks so much! The "COUNTIFS" worked great for me