| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Averaging data and rounding up | Excel Worksheet Functions | |||
| averaging data | Excel Discussion (Misc queries) | |||
| averaging particular blocks of data | Excel Discussion (Misc queries) | |||
| Averaging data | Excel Worksheet Functions | |||
| incremental averaging of data | Excel Discussion (Misc queries) | |||
Linear Mode
