Ignoring errors when calculating average

Ok so I've attached an example here. My issue is that when pulling data from a database I use at work in some cases the data may not be available for a specific company in a specific quarter but is for the other companies. My issue is that when calculating an average based off of the group if one group members number comes back out of the database as "NA" it throws off my average and relative calc. I need a formula that if the cell in the group that I am averaging comes back as "NA" will ignore it and re-calc the correct average without the cell with the "NA" in it.

Again I've attached a sample spreadsheet and if someone could help me out and show me a fix I would greatly appreciate it!

Cheers!

You can use this array* formula in I2:

=AVERAGE(IF(ISNUMBER(A2:G2),A2:G2))

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

Then you can copy the formula down in the usual way(s).

Hope this helps.

Pete

Yes that works Great!!

Thanks

Pete one other question for you if you don't mind. If the average is a negative number but the company in column "A" has a positive number How can I insert a formula into the relative column saying that if the average is negative to use addition instead of subtraction and vise versa?

Cheers!

I think you might mean this:

=A2-SIGN(I2)*I2

or you could look into using the ABS function.

Hope this helps.

Pete