Array formula help

[Loonytoons]

Hi guys,

I have created the following formula:

=ROUND(AVERAGE(IF(('2013 valuations'!$B$10:$B$150000={333,1106,1036,1006,1082,1090,1067,1065,1080,1062,1028})*('2013 valuations'!$D$10:$D$150000=F$2),'2013 valuations'!$O$10:$O$150000)),0)

It works exactly as it should.

However, this is being used across a number of fields referencing different columns.
Is there a way I can change the lookup array to reference a range or a sequence of cells instead of hard coding the values?

So, in essence, this:

=ROUND(AVERAGE(IF(('2013 valuations'!$B$10:$B$150000={C70:C80})*('2013 valuations'!$D$10:$D$150000=F$2),'2013 valuations'!$O$10:$O$150000)),0)

I'm not sure if this is possible - I haven't found any examples that use anything other than hardcoding.

Any help would be very much appreciated

[lecxe]

Hi

2 problems with your formula

1 -
{C70:C80}

This is invalid syntax, an array can only have elements that are constants

2 -

If you want to use a range to write the lookup values, like in the case you post, the vector with the data values and the vector with the lookup values must be orthogonal.

Since your vector with the data values ($B$10:$B$150000) is vertical this means that you have 2 options


Option 1

You use a horizontal vector to write the lookup values, for ex. E70:O70. In that case it's direct:

=ROUND(AVERAGE(IF(('2013 valuations'!$B$10:$B$150000=E70:O70)*('2013 valuations'!$D$10:$D$150000=F$2),'2013 valuations'!$O$10:$O$150000)),0)


Option 2

If you want to use a vertical vector to write the lookup values you have to transpose it. For ex., in your formula you wanted to use C70:C80, you have to use:

=ROUND(AVERAGE(IF(('2013 valuations'!$B$10:$B$150000=TRANSPOSE(C70:C80))*('2013 valuations'!$D$10:$D$150000=F$2),'2013 valuations'!$O$10:$O$150000)),0)


... both are array formulas, like yours.