Working out the averages in a cell and ignoring errors.

[Cmorgan]

Afternoon,

I'm having problems working out the average result.

In the table sheet W2 I want to work out the average of column x from the input sheet. The issue is I will never know in advance how many values will be in column x.

Any ideas on the formula please?

Many thanks

[MarvinP]

Hi,
Try in Cell Y2 the formula

Please Login or Register  to view this content.

[Cmorgan]

I've amended the formula to check the column in the input sheet, so it reads:

=Average('input sheet'!X:X)

But it comes back with #N/A

Any ideas? Thanks for the help

[Miraun]

The equation that MarvinP wrote was for the "Table" Page, as when you saved the spreadsheet to upload it, your activesheet was on the Table Page, yet the question was about the Input Sheet.

Since you're working off of 2010, you can use the Averageif function on the Input Sheet Page:
=AVERAGEIF(X:X,"<>#N/A",X:X)

[Cmorgan]

Thanks guys, works a treat.