making a more acurate mean

M

michael.leonard

I'm working with a spreadsheet where we are tracking a few variables
that are later calculated into a mean. Generally there are alwats 5
variables in each mean, but every so often on of the conditions is not
met so that cell returns "FALSE" or "#N/A". In those cases excel
figures them as zeros in the mean.

Is there a way to have excell recognise the occurance of a "FALSE" or
"#N/A" and not bring it into the mean calculation?

So if one of the 5 variables returns "FALSE" or "#N/A" I would like
excell to do the mean based on the other 4 using a devisor of 4.

Any ideas?
 
P

Peo Sjoblom

IF #N/A is in the range the result would be #N/A, if FALSE it would be
ignored

=AVERAGE(Range)

will ignore text and it will not be seen as zero


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
 
R

Rob Hick

I'm working with a spreadsheet where we are tracking a few variables
that are later calculated into a mean. Generally there are alwats 5
variables in each mean, but every so often on of the conditions is not
met so that cell returns "FALSE" or "#N/A". In those cases excel
figures them as zeros in the mean.
Click to expand...

If you are using the average() function then Excel should ignore
anything but numerical values. Although I think if it sees an error,
such as #N/A then it will return that same error.
Is there a way to have excell recognise the occurance of a "FALSE" or
"#N/A" and not bring it into the mean calculation?
Click to expand...

yes, it does this by default.
So if one of the 5 variables returns "FALSE" or "#N/A" I would like
excell to do the mean based on the other 4 using a devisor of 4.
Click to expand...

If you are really having problems, you could try calculating the mean
from first priniciples - i.e. add up the values and then divide by the
count you want. Use the COUNTIF function to get the divisor you're
after.
 
Top