complex: non-contiguous array formula - need syntax help!

  • Thread starter Thread starter Keith R
  • Start date Start date
K

Keith R

I have an array of non-contiguous cells, which are actually on different
pages (Sheet1!C3, Sheet2!C3, etc)

When I use an array formula on one page, it works fine:

=Average(if(iserror(C3:C12),"",C3:C12) [ctrl-Shift-Enter]

but when I use the same formula across my non-contiguous range, it gives an
error- any idea how to make it work?

=Average(if(iserror(Sheet1!C3, Sheet2!C3),"",Sheet1!C3, Sheet2!C3)

Thanks!!
Keith R
 
Insert 2 blank worksheets, name them First and Last, and put all the
relevant sheets like Sheet1 and Sheet2 between these two, and try...

=AVERAGE(First:Last!C3)
 
What kind of error(s) are you filtering with iserror()?

If you can eliminate the errrors at the source, use a 3D reference like:

=AVERAGE(Sheet1:Sheet2!C3)

To work around the apparent limitation of iserror() in accepting 3D
references, you might wish to average your averages like so:

=AVERAGE(AVERAGE(IF(ISERROR(Sheet1!C3),"",Sheet1!C3)),AVERAGE(IF(ISERROR(She
et2!C3),"",Sheet2!C3)))

/i.
 
Back
Top