How do I reference an entire column in an array formula?

[fbarbie]

Hi,

I know that in SUBTOTAL formulas in some other formulas you can reference an entire column in another worksheet as follows:

worksheet1B:B

However, this does not seem to work in array formulas (the ones where you use CTRL+SHFT+ENTER). I have an =AVERAGE(IF... formula and I have to reference like rows 2 to 5000.

Is there a way to reference an entire column in array formulas? Thanks.

[Domenic]

Assuming that Column B contains your numerical values to average, you can use a 'near' whole column reference...

=AVERAGE(IF(A2:A65536=Criteria,B2:B65536))

Or you can define dynamic ranges...

Insert > Name > Define

Name: ColumnA
Refers to:
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$B:$B))

Click Add

Name: ColumnB
Refers to:
=Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9.99999999999999E+307,Sheet1!$B:$B))

...and then use the following formula...

=AVERAGE(IF(RangeA=Criteria,RangeB))

Now the range will automatically adjust as you enter new data or delete old ones.

Hope this helps!

[fbarbie]

Domenic,

Thank you again for sharing your expertise. Two questions about the solutions you suggested.

First, does it change the processing time to tell it 2 to 65k as opposed to any of the other methods you suggest, or since it means the same thing, it makes no difference when formulas are being calculated?

Second, when you define a dynamic range, can i reference a column in a worksheet and use that in all the worksheets in the workbook?

Thanks again.

[Domenic]

First, does it change the processing time to tell it 2 to 65k as opposed to any of the other methods you suggest, or since it means the same thing, it makes no difference when formulas are being calculated?

Yes, I believe using dynamic ranges is faster, but I don't know how much faster nor can I point you to any data that would support it. Nevertheless, try it, you'll likely notice the difference.

Second, when you define a dynamic range, can i reference a column in a worksheet and use that in all the worksheets in the workbook?

Yes you can.