Excluding cells from average on Excel workbook
 

I am trying to average a column of percentages but need to exclude some cells
because they are N/A. The problem is that the cell may be empty but it has a
function associated with it so it is not being excluded from the average. I
have tried
=SUM(F10,F13,F16,F19,F22,F26,F30)/COUNTA(F10,F13,F16,F19,F22,F26,F30)but this
only works if I delete the function from the cell that is N/A. For example,
I may need F13 to be excluded this time because the numerator and denominator
were 0 but next time I might need it so I don't want to delete the function
from the cell. Is there a way to do this? Is it possible to hide the
function so that the cell appears to be empty if there is no number or
letters in it but the function is still being used?
Thanks much

Excluding cells from average on Excel workbook
 

I posted this to your original post but you chose to got with another
person's solution that doesn't work


=SUM(F10,F13,F16,F19,F22,F26,F30)/SUMPRODUCT(COUNTIF(INDIRECT({"F10","F13","F16","F1 9","F22","F26","F30"}),"0"))


this will exclude all values zero or less since I assume they cannot be
negative.

However now you seem to have changed the criteria and are using formulas
that can return N/A, so why not fixed those formula so they return 0 or
blank instead of N/A

example


=IF(ISNA(VLOOKUP()),0,VLOOKUP())


if you insist on having formulas that can return N/A then this might work


=SUMPRODUCT(SUMIF(INDIRECT({"F10","F13","F16","F19 ","F22","F26","F30"}),"<"&99^99))/SUMPRODUCT(COUNTIF(INDIRECT({"F10","F13","F16","F1 9","F22","F26","F30"}),"0"))

--


Regards,


Peo Sjoblom


"Beth Gaines" wrote in message
...
I am trying to average a column of percentages but need to exclude some
cells
because they are N/A. The problem is that the cell may be empty but it
has a
function associated with it so it is not being excluded from the average.
I
have tried
=SUM(F10,F13,F16,F19,F22,F26,F30)/COUNTA(F10,F13,F16,F19,F22,F26,F30)but
this
only works if I delete the function from the cell that is N/A. For
example,
I may need F13 to be excluded this time because the numerator and
denominator
were 0 but next time I might need it so I don't want to delete the
function
from the cell. Is there a way to do this? Is it possible to hide the
function so that the cell appears to be empty if there is no number or
letters in it but the function is still being used?
Thanks much

Excluding cells from average on Excel workbook
 

/COUNTA(F10,F13,F16,F19,F22,F26,F30)

Try replacing the COUNTA function with the COUNT function:

=SUM(...)/COUNT(...)

--
Biff
Microsoft Excel MVP


"Beth Gaines" wrote in message
...
I am trying to average a column of percentages but need to exclude some
cells
because they are N/A. The problem is that the cell may be empty but it
has a
function associated with it so it is not being excluded from the average.
I
have tried
=SUM(F10,F13,F16,F19,F22,F26,F30)/COUNTA(F10,F13,F16,F19,F22,F26,F30)but
this
only works if I delete the function from the cell that is N/A. For
example,
I may need F13 to be excluded this time because the numerator and
denominator
were 0 but next time I might need it so I don't want to delete the
function
from the cell. Is there a way to do this? Is it possible to hide the
function so that the cell appears to be empty if there is no number or
letters in it but the function is still being used?
Thanks much

Excluding cells from average on Excel workbook
 

Hi Peo!

They may not have seen your original reply. The ngs are broken and have been
for about a week.

If the question is posted through the MS web interface, which it was, and
you reply to that question using an application other than the web
interface, which you are doing, then your reply doesn't get posted. MS does
know about this.

The OP won't see my reply either until the problem is corrected.

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
I posted this to your original post but you chose to got with another
person's solution that doesn't work


=SUM(F10,F13,F16,F19,F22,F26,F30)/SUMPRODUCT(COUNTIF(INDIRECT({"F10","F13","F16","F1 9","F22","F26","F30"}),"0"))


this will exclude all values zero or less since I assume they cannot be
negative.

However now you seem to have changed the criteria and are using formulas
that can return N/A, so why not fixed those formula so they return 0 or
blank instead of N/A

example


=IF(ISNA(VLOOKUP()),0,VLOOKUP())


if you insist on having formulas that can return N/A then this might work


=SUMPRODUCT(SUMIF(INDIRECT({"F10","F13","F16","F19 ","F22","F26","F30"}),"<"&99^99))/SUMPRODUCT(COUNTIF(INDIRECT({"F10","F13","F16","F1 9","F22","F26","F30"}),"0"))

--


Regards,


Peo Sjoblom


"Beth Gaines" wrote in message
...
I am trying to average a column of percentages but need to exclude some
cells
because they are N/A. The problem is that the cell may be empty but it
has a
function associated with it so it is not being excluded from the average.
I
have tried
=SUM(F10,F13,F16,F19,F22,F26,F30)/COUNTA(F10,F13,F16,F19,F22,F26,F30)but
this
only works if I delete the function from the cell that is N/A. For
example,
I may need F13 to be excluded this time because the numerator and
denominator
were 0 but next time I might need it so I don't want to delete the
function
from the cell. Is there a way to do this? Is it possible to hide the
function so that the cell appears to be empty if there is no number or
letters in it but the function is still being used?
Thanks much

Excluding cells from average on Excel workbook
 

OK. That makes sense

Thanks

--


Regards,


Peo Sjoblom


"T. Valko" wrote in message
...
Hi Peo!

They may not have seen your original reply. The ngs are broken and have
been for about a week.

If the question is posted through the MS web interface, which it was, and
you reply to that question using an application other than the web
interface, which you are doing, then your reply doesn't get posted. MS
does know about this.

The OP won't see my reply either until the problem is corrected.

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
I posted this to your original post but you chose to got with another
person's solution that doesn't work


=SUM(F10,F13,F16,F19,F22,F26,F30)/SUMPRODUCT(COUNTIF(INDIRECT({"F10","F13","F16","F1 9","F22","F26","F30"}),"0"))


this will exclude all values zero or less since I assume they cannot be
negative.

However now you seem to have changed the criteria and are using formulas
that can return N/A, so why not fixed those formula so they return 0 or
blank instead of N/A

example


=IF(ISNA(VLOOKUP()),0,VLOOKUP())


if you insist on having formulas that can return N/A then this might work


=SUMPRODUCT(SUMIF(INDIRECT({"F10","F13","F16","F19 ","F22","F26","F30"}),"<"&99^99))/SUMPRODUCT(COUNTIF(INDIRECT({"F10","F13","F16","F1 9","F22","F26","F30"}),"0"))

--


Regards,


Peo Sjoblom


"Beth Gaines" wrote in message
...
I am trying to average a column of percentages but need to exclude some
cells
because they are N/A. The problem is that the cell may be empty but it
has a
function associated with it so it is not being excluded from the
average. I
have tried
=SUM(F10,F13,F16,F19,F22,F26,F30)/COUNTA(F10,F13,F16,F19,F22,F26,F30)but
this
only works if I delete the function from the cell that is N/A. For
example,
I may need F13 to be excluded this time because the numerator and
denominator
were 0 but next time I might need it so I don't want to delete the
function
from the cell. Is there a way to do this? Is it possible to hide the
function so that the cell appears to be empty if there is no number or
letters in it but the function is still being used?
Thanks much

Excluding cells from average on Excel workbook
 

We can use an array* formula to exclude errors.

=AVERAGE(IF(ISNUMBER(F10:F30),F10:F30))

If you need additional conditions, such as greater than 0, we can add an
additional IF check

=AVERAGE(IF(ISNUMBER(F10:F30),IF(F10:F300,F10:F30 )))

*Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Beth Gaines" wrote:

I am trying to average a column of percentages but need to exclude some cells
because they are N/A. The problem is that the cell may be empty but it has a
function associated with it so it is not being excluded from the average. I
have tried
=SUM(F10,F13,F16,F19,F22,F26,F30)/COUNTA(F10,F13,F16,F19,F22,F26,F30)but this
only works if I delete the function from the cell that is N/A. For example,
I may need F13 to be excluded this time because the numerator and denominator
were 0 but next time I might need it so I don't want to delete the function
from the cell. Is there a way to do this? Is it possible to hide the
function so that the cell appears to be empty if there is no number or
letters in it but the function is still being used?
Thanks much

Excluding cells from average on Excel workbook
 

Except that you would include all cells in the range as opposed to F10, F13,
F16, F19, F22, F26 and F30 only

--


Regards,


Peo Sjoblom


"Luke M" wrote in message
...
We can use an array* formula to exclude errors.

=AVERAGE(IF(ISNUMBER(F10:F30),F10:F30))

If you need additional conditions, such as greater than 0, we can add an
additional IF check

=AVERAGE(IF(ISNUMBER(F10:F30),IF(F10:F300,F10:F30 )))

*Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Beth Gaines" wrote:

I am trying to average a column of percentages but need to exclude some
cells
because they are N/A. The problem is that the cell may be empty but it
has a
function associated with it so it is not being excluded from the average.
I
have tried
=SUM(F10,F13,F16,F19,F22,F26,F30)/COUNTA(F10,F13,F16,F19,F22,F26,F30)but
this
only works if I delete the function from the cell that is N/A. For
example,
I may need F13 to be excluded this time because the numerator and
denominator
were 0 but next time I might need it so I don't want to delete the
function
from the cell. Is there a way to do this? Is it possible to hide the
function so that the cell appears to be empty if there is no number or
letters in it but the function is still being used?
Thanks much

Excluding cells from average on Excel workbook
 

"T. Valko" wrote:
MS does know about this.

Good to know that it's been reported. The problem has persisted since some
time on Nov 2.


The OP won't see my reply either until the problem is corrected.

Unless you take the trouble of posting it using the MS Discussion Groups web
interface.

MSDG postings seem to be disseminated to all NG servers. But postings
through other NG servers do not seem to be disseminated to or (more likely)
received on the MSDG server.


----- original message -----

"T. Valko" wrote in message
...
Hi Peo!

They may not have seen your original reply. The ngs are broken and have
been for about a week.

If the question is posted through the MS web interface, which it was, and
you reply to that question using an application other than the web
interface, which you are doing, then your reply doesn't get posted. MS
does know about this.

The OP won't see my reply either until the problem is corrected.

--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
I posted this to your original post but you chose to got with another
person's solution that doesn't work


=SUM(F10,F13,F16,F19,F22,F26,F30)/SUMPRODUCT(COUNTIF(INDIRECT({"F10","F13","F16","F1 9","F22","F26","F30"}),"0"))


this will exclude all values zero or less since I assume they cannot be
negative.

However now you seem to have changed the criteria and are using formulas
that can return N/A, so why not fixed those formula so they return 0 or
blank instead of N/A

example


=IF(ISNA(VLOOKUP()),0,VLOOKUP())


if you insist on having formulas that can return N/A then this might work


=SUMPRODUCT(SUMIF(INDIRECT({"F10","F13","F16","F19 ","F22","F26","F30"}),"<"&99^99))/SUMPRODUCT(COUNTIF(INDIRECT({"F10","F13","F16","F1 9","F22","F26","F30"}),"0"))

--


Regards,


Peo Sjoblom


"Beth Gaines" wrote in message
...
I am trying to average a column of percentages but need to exclude some
cells
because they are N/A. The problem is that the cell may be empty but it
has a
function associated with it so it is not being excluded from the
average. I
have tried
=SUM(F10,F13,F16,F19,F22,F26,F30)/COUNTA(F10,F13,F16,F19,F22,F26,F30)but
this
only works if I delete the function from the cell that is N/A. For
example,
I may need F13 to be excluded this time because the numerator and
denominator
were 0 but next time I might need it so I don't want to delete the
function
from the cell. Is there a way to do this? Is it possible to hide the
function so that the cell appears to be empty if there is no number or
letters in it but the function is still being used?
Thanks much