Exclude "#VALUE!" from counting the average

[ilya49]

I have a list of always changing arrays. And next to the arrays, i have a formula which calculates the percentage change of the 2 numbers from array. I want to average those percentage returns, however some of the percentage change values are returned with #VALUE! and that prevents me from calculating average. Is there any way how to automatically exclude #VALUE! from counting from average???

my spreadsheet looks something like this:

Numbers
Generated
from Array...........Percent Change
49.09
48.03.................-2.16% (48.03-49.09)/49.09
53.32.................11.01%
52.93.................-0.73%
57.2..................8.07%
57.44 ................0.42%
......................#VALUE!
......................#VALUE!
......................#VALUE!
......................#VALUE!

...................... =AVERAGE() ????? <--I need to find how to average the numbers exluding #VALUE!.
PLEASE HELP!!!


THANK YOU

[DonkeyOte]

Better to adjust your Percent Change formula such that

B2: =IF(COUNT(A1:A2)=2,(A2-A1)/A1,"")
copied down for all rows

Your Average is then simply

=AVERAGE(B:B)

EDIT: note range changes in B2 formula - shoudl have been A not B (apologies)

[ilya49]

thank you, it worked perfectly!!!!