Averaging data with errors
 

I am trying to take the average of some data but some of the cells contain
errors (as there is data missing for the function to calculate). In my
database there will always be some missing data so these errors will be
present. Is there any way of taking an average of data that contains errors?
eg. average(a1,a2,a3) equals 4 whe
a1=5
a2=#N/A
a3=3
Many thanks for your help
John

Averaging data with errors
 

One way

In B1, array entered*:
=AVERAGE(IF(ISNUMBER(A1:A3),A1:A3))
*press CTRL+SHIFT+ENTER to confirm the formula
instead of just pressing ENTER
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"John Mac" wrote:
I am trying to take the average of some data but some of the cells contain
errors (as there is data missing for the function to calculate). In my
database there will always be some missing data so these errors will be
present. Is there any way of taking an average of data that contains errors?
eg. average(a1,a2,a3) equals 4 whe
a1=5
a2=#N/A
a3=3
Many thanks for your help
John

Averaging data with errors
 

=AVERAGE(IF(NOT(ISNA(A1:A3)),A1:A3))

ctrl+shift+enter, not just enter


"John Mac" wrote:

I am trying to take the average of some data but some of the cells contain
errors (as there is data missing for the function to calculate). In my
database there will always be some missing data so these errors will be
present. Is there any way of taking an average of data that contains errors?
eg. average(a1,a2,a3) equals 4 whe
a1=5
a2=#N/A
a3=3
Many thanks for your help
John

Averaging data with errors
 

Max

Do you know how I would be able to fit your advice into the following formula

=INT(AVERAGE(P2,S2,V2))+CHOOSE((MOD(AVERAGE(P2,S2, V2),1)<0.3)+(MOD(AVERAGE(P2,S2,V2),1)<0.7)+1,0.9,0 .5,0.1)

as when I try I keep getting errors
Thanks for your help
John

"Max" wrote:

One way

In B1, array entered*:
=AVERAGE(IF(ISNUMBER(A1:A3),A1:A3))
*press CTRL+SHIFT+ENTER to confirm the formula
instead of just pressing ENTER
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"John Mac" wrote:
I am trying to take the average of some data but some of the cells contain
errors (as there is data missing for the function to calculate). In my
database there will always be some missing data so these errors will be
present. Is there any way of taking an average of data that contains errors?
eg. average(a1,a2,a3) equals 4 whe
a1=5
a2=#N/A
a3=3
Many thanks for your help
John

Averaging data with errors
 

Do you know how I would be able to fit your advice into the following formula

=INT(AVERAGE(P2,S2,V2))+CHOOSE((MOD(AVERAGE(P2,S2, V2),1)<0.3)+(MOD(AVERAGE(P2,S2,V2),1)<0.7)+1,0.9,0 .5,0.1)

as when I try I keep getting errors
Thanks for your help
John


"Teethless mama" wrote:

=AVERAGE(IF(NOT(ISNA(A1:A3)),A1:A3))

ctrl+shift+enter, not just enter


"John Mac" wrote:

I am trying to take the average of some data but some of the cells contain
errors (as there is data missing for the function to calculate). In my
database there will always be some missing data so these errors will be
present. Is there any way of taking an average of data that contains errors?
eg. average(a1,a2,a3) equals 4 whe
a1=5
a2=#N/A
a3=3
Many thanks for your help
John