An alternative to the solutions provided by Biff and Don would be to
change the formula to return a blank instead of #N/A...

1) Select NewSheet!B8:L8

2) Enter the following formula:

=IF(COLUMN()-COLUMN(B8)+1<COUNTIF(Range,A8)+(FirstRow=""),TRANS POSE(Array
2-Array1)-1,"")

3) Confirm with CONTROL+SHIFT+ENTER

In this case, there would be no need to use conditional formatting.

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi All,

I have a spreadsheet with an Array entered Formula (based on Rows using the
TRANSPOSE Function) that produces the #N/A error when no further data is
available to be calculated: this in itself is fine - the #N/A is disguised
with Conditional Formatting. However, if possible, I would like to find the
AVERAGE of the values in each Row excluding those cells with #N/A from the
calculation.

I've tried a few variations using either the ISERROR or ISNA Function nested
with the AVERAGE Function. I get either zero or #N/A returned.

The original Formulae providing the results that I now wish to Average was
brilliantly created by Dominic in this previous Post: "Count Intervals of
Filtered TEXT values in Column and Return Count across a Row"

http://www.officekb.com/Uwe/Forum.as...nt-Intervals-o
f-Filtered-TEXT-values-in-Column-and#51E8B7DD4D490%40OfficeKB.com


The values returned from the above Formulae is numeric and formatted as
GENERAL.

Thanks
Sam