Average help

[catdawg88]

Hi I am currently trying to calculate an average for a set of data but unfortunately not all data cells contain numbers and some contain #N/A .I basically want excel to ignore these values and calculate the average for the cells which do contain data.

for example id like it to calculate as follows

number of cards

7
7
7
7
N/A
average=7


I have played around with averageif function and tried following formula but this did not work as i just obtain DIV/O ERROR

=AVERAGEIF(AO16:AO68,"<>*(#N/A)",A16:A68)

any help will be very useful

kind regards

Matt

[teylyn]

Hello,

One way to deal with this is to tackle the cells that contain the #N/A error. Why do they contain this error? What is the formula in those cells?

Change the formula in those cells to result in "" instead of #N/A and the Average will calculate correctly.

If that does not help, please attach a sample file that illustrates your problem.

cheers,

[catdawg88]

they dont contain an error I

[sweep]

Hi,

You can do this using an array, eg.,

=AVERAGE(IF(ISNUMBER(A1:A3),A1:A3,""))

entered with CTRL, SHIFT and ENTER.

[catdawg88]

ok attached is a sample column I inserted the n/a myself as didn't have data for these fields.

regards matt

[brokenbiscuits]

So long as your values are always positive, you could use:

=SUMIF(B2:B63,">0",B2:B63)/COUNTIF(B2:B63,">0")

[catdawg88]

thanks all for quick and helpful responses

how would this formula vary to calculate the standard deviation?

regards matt

[teylyn]

In your initial post you say

some contain #N/A

That's an error.

Then you say

they dont contain an error

That does not gel. Please make up your mind.

[daddylonglegs]

Hello Matt,

Yes, #N/A is still an error value, even if entered manually into cells. If you entered a text value like N/A (without the #) then that would be treated differently. AVERAGE ignores text so you could use a straightforward AVERAGE function, i.e.

=AVERAGE(B2:B63)

but with the data as shown you could use AVERAGEIF as you initially suggested, i.e.

=AVERAGEIF(B2:B63,"<>#N/A")

For STDEV try this array formula

=STDEV(IF(ISNUMBER(B2:B63),B2:B63))

confirmed with CTRL+SHIFT+ENTER