Summing and Averages with non-numeric cells
 

I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate the N/A
responses from the denominator and from the total sum. I currently have 1
cell for this operation - which doesn't work when the response is N/A since
it's a simple (sum)/7 equation.

Thanks for any help!

Summing and Averages with non-numeric cells
 

Hi,

Try it this way

=SUM(A1:A7)/COUNT(A1:A7)

Mike

"Erinayn" wrote:

I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate the N/A
responses from the denominator and from the total sum. I currently have 1
cell for this operation - which doesn't work when the response is N/A since
it's a simple (sum)/7 equation.

Thanks for any help!

Summing and Averages with non-numeric cells
 

I assumed you N/A is a text input and not an excel #N/A error. If it is the
Excel error then use this instead

=SUMIF(A1:A7,"<#N/A")/COUNT(A1:A7)

Mike

"Erinayn" wrote:

I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate the N/A
responses from the denominator and from the total sum. I currently have 1
cell for this operation - which doesn't work when the response is N/A since
it's a simple (sum)/7 equation.

Thanks for any help!

Summing and Averages with non-numeric cells
 

If you really have N/A (plain old text) in that cell, then
=average(a1:a6)
should work fine.

=average() ignores non-numeric (but not errors) values.

On the other hand, if you have the error #N/A (resulting from a formula???) in
that range, then =average() will return an error.

You could use:
=SUMIF(A1:A6,"<1e37")/COUNT(A1:A6)

1E37 in scientific notation for a giant number. 1 followed by 37 zeros.



Erinayn wrote:

I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate the N/A
responses from the denominator and from the total sum. I currently have 1
cell for this operation - which doesn't work when the response is N/A since
it's a simple (sum)/7 equation.

Thanks for any help!

--

Dave Peterson

Summing and Averages with non-numeric cells
 

If N/A has been entered as text (not from a formula), the =AVERAGE(A1:A7)
will ignore it

If you have NA() as part of a formula that is returning N/A:
Option 1
=AVERAGE(IF(ISNUMBER(C1:C7),C1:C7))
Array formula: you must commit it with Shift+Ctrl+Enter not just Enter
Option 2 Non array formula
=SUMIF(D1:D7,"0")/COUNTIF(D1:D7,"0")
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Erinayn" wrote in message
...
I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate the
N/A
responses from the denominator and from the total sum. I currently have 1
cell for this operation - which doesn't work when the response is N/A
since
it's a simple (sum)/7 equation.

Thanks for any help!

Summing and Averages with non-numeric cells
 

It is just an alpha N/A. Now that I'm looking at it only one person has
responded as such and the kicker is that no other responses for the area were
received so I'm getting an error since the average is of nothing.

Any thoughts on that?

"Dave Peterson" wrote:

If you really have N/A (plain old text) in that cell, then
=average(a1:a6)
should work fine.

=average() ignores non-numeric (but not errors) values.

On the other hand, if you have the error #N/A (resulting from a formula???) in
that range, then =average() will return an error.

You could use:
=SUMIF(A1:A6,"<1e37")/COUNT(A1:A6)

1E37 in scientific notation for a giant number. 1 followed by 37 zeros.



Erinayn wrote:

I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate the N/A
responses from the denominator and from the total sum. I currently have 1
cell for this operation - which doesn't work when the response is N/A since
it's a simple (sum)/7 equation.

Thanks for any help!

--

Dave Peterson
.

Summing and Averages with non-numeric cells
 

As you stated, you did get a response which addressed the "alpha N/A". The
response was that Average will ignore text. Now you have to explain why that
doesn't work for you, and what you mean by "the average is of nothing".

Regards,
Fred

"Erinayn" wrote in message
...
It is just an alpha N/A. Now that I'm looking at it only one person has
responded as such and the kicker is that no other responses for the area
were
received so I'm getting an error since the average is of nothing.

Any thoughts on that?

"Dave Peterson" wrote:

If you really have N/A (plain old text) in that cell, then
=average(a1:a6)
should work fine.

=average() ignores non-numeric (but not errors) values.

On the other hand, if you have the error #N/A (resulting from a
formula???) in
that range, then =average() will return an error.

You could use:
=SUMIF(A1:A6,"<1e37")/COUNT(A1:A6)

1E37 in scientific notation for a giant number. 1 followed by 37 zeros.



Erinayn wrote:

I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate the
N/A
responses from the denominator and from the total sum. I currently have
1
cell for this operation - which doesn't work when the response is N/A
since
it's a simple (sum)/7 equation.

Thanks for any help!

--

Dave Peterson
.

Summing and Averages with non-numeric cells
 

=if(count(a1:a7)=0,"No numbers",average(a1:a7))

Check for at least one number in that range first.

Erinayn wrote:

It is just an alpha N/A. Now that I'm looking at it only one person has
responded as such and the kicker is that no other responses for the area were
received so I'm getting an error since the average is of nothing.

Any thoughts on that?

"Dave Peterson" wrote:

If you really have N/A (plain old text) in that cell, then
=average(a1:a6)
should work fine.

=average() ignores non-numeric (but not errors) values.

On the other hand, if you have the error #N/A (resulting from a formula???) in
that range, then =average() will return an error.

You could use:
=SUMIF(A1:A6,"<1e37")/COUNT(A1:A6)

1E37 in scientific notation for a giant number. 1 followed by 37 zeros.



Erinayn wrote:

I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate the N/A
responses from the denominator and from the total sum. I currently have 1
cell for this operation - which doesn't work when the response is N/A since
it's a simple (sum)/7 equation.

Thanks for any help!

--

Dave Peterson
.


--

Dave Peterson

Summing and Averages with non-numeric cells
 

=IF(COUNT(A1:A6),AVERAGE(A1:A6),"")
--
David Biddulph

"Erinayn" wrote in message
...
It is just an alpha N/A. Now that I'm looking at it only one person has
responded as such and the kicker is that no other responses for the area
were
received so I'm getting an error since the average is of nothing.

Any thoughts on that?

"Dave Peterson" wrote:

If you really have N/A (plain old text) in that cell, then
=average(a1:a6)
should work fine.

=average() ignores non-numeric (but not errors) values.

On the other hand, if you have the error #N/A (resulting from a
formula???) in
that range, then =average() will return an error.

You could use:
=SUMIF(A1:A6,"<1e37")/COUNT(A1:A6)

1E37 in scientific notation for a giant number. 1 followed by 37 zeros.



Erinayn wrote:

I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate the
N/A
responses from the denominator and from the total sum. I currently have
1
cell for this operation - which doesn't work when the response is N/A
since
it's a simple (sum)/7 equation.

Thanks for any help!

--

Dave Peterson
.

Summing and Averages with non-numeric cells
 

We poll about 8 people total per event and 1 received only 1 survey total for
that event. The person answered N/A to the first 3 questions and 5s for the
rest.

I am trying to get an average across the survey - which would be 5 since
it's 20/4 and that worked. But we also get an average for each question
across all of the responses. So in this instance since I got only one reply
and that answer was N/A I am getting the #DIV! error as there is really
nothing to divide with as the denominator is 0.
HTH
"Fred Smith" wrote:

As you stated, you did get a response which addressed the "alpha N/A". The
response was that Average will ignore text. Now you have to explain why that
doesn't work for you, and what you mean by "the average is of nothing".

Regards,
Fred

"Erinayn" wrote in message
...
It is just an alpha N/A. Now that I'm looking at it only one person has
responded as such and the kicker is that no other responses for the area
were
received so I'm getting an error since the average is of nothing.

Any thoughts on that?

"Dave Peterson" wrote:

If you really have N/A (plain old text) in that cell, then
=average(a1:a6)
should work fine.

=average() ignores non-numeric (but not errors) values.

On the other hand, if you have the error #N/A (resulting from a
formula???) in
that range, then =average() will return an error.

You could use:
=SUMIF(A1:A6,"<1e37")/COUNT(A1:A6)

1E37 in scientific notation for a giant number. 1 followed by 37 zeros.



Erinayn wrote:

I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate the
N/A
responses from the denominator and from the total sum. I currently have
1
cell for this operation - which doesn't work when the response is N/A
since
it's a simple (sum)/7 equation.

Thanks for any help!

--

Dave Peterson
.


.

Summing and Averages with non-numeric cells
 

Then you want something like:
=if(count(a1:a7)=0,"N/A",average(a1:a7))

Regards
Fred

"Erinayn" wrote in message
...
We poll about 8 people total per event and 1 received only 1 survey total
for
that event. The person answered N/A to the first 3 questions and 5s for
the
rest.

I am trying to get an average across the survey - which would be 5 since
it's 20/4 and that worked. But we also get an average for each question
across all of the responses. So in this instance since I got only one
reply
and that answer was N/A I am getting the #DIV! error as there is really
nothing to divide with as the denominator is 0.
HTH
"Fred Smith" wrote:

As you stated, you did get a response which addressed the "alpha N/A".
The
response was that Average will ignore text. Now you have to explain why
that
doesn't work for you, and what you mean by "the average is of nothing".

Regards,
Fred

"Erinayn" wrote in message
...
It is just an alpha N/A. Now that I'm looking at it only one person has
responded as such and the kicker is that no other responses for the
area
were
received so I'm getting an error since the average is of nothing.

Any thoughts on that?

"Dave Peterson" wrote:

If you really have N/A (plain old text) in that cell, then
=average(a1:a6)
should work fine.

=average() ignores non-numeric (but not errors) values.

On the other hand, if you have the error #N/A (resulting from a
formula???) in
that range, then =average() will return an error.

You could use:
=SUMIF(A1:A6,"<1e37")/COUNT(A1:A6)

1E37 in scientific notation for a giant number. 1 followed by 37
zeros.



Erinayn wrote:

I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate
the
N/A
responses from the denominator and from the total sum. I currently
have
1
cell for this operation - which doesn't work when the response is
N/A
since
it's a simple (sum)/7 equation.

Thanks for any help!

--

Dave Peterson
.


.

Summing and Averages with non-numeric cells
 

PERFECT!! That worked just how I needed it. I knew I needed an if plus the
concept from before but didn't know how to make it work. Thanks to everyone
for their input.

"Dave Peterson" wrote:

=if(count(a1:a7)=0,"No numbers",average(a1:a7))

Check for at least one number in that range first.

Erinayn wrote:

It is just an alpha N/A. Now that I'm looking at it only one person has
responded as such and the kicker is that no other responses for the area were
received so I'm getting an error since the average is of nothing.

Any thoughts on that?

"Dave Peterson" wrote:

If you really have N/A (plain old text) in that cell, then
=average(a1:a6)
should work fine.

=average() ignores non-numeric (but not errors) values.

On the other hand, if you have the error #N/A (resulting from a formula???) in
that range, then =average() will return an error.

You could use:
=SUMIF(A1:A6,"<1e37")/COUNT(A1:A6)

1E37 in scientific notation for a giant number. 1 followed by 37 zeros.



Erinayn wrote:

I have a 7 question survery with the following possible responses
1
2
3
4
5
N/A
We then take ther responses and average them. I'd like to eliminate the N/A
responses from the denominator and from the total sum. I currently have 1
cell for this operation - which doesn't work when the response is N/A since
it's a simple (sum)/7 equation.

Thanks for any help!

--

Dave Peterson
.


--

Dave Peterson
.