Averageifs using cell reference rather than hard coded...

[Murphyk]

Hi,

I'm trying to calculate a bunch of averageifs on some data, but am finding it impossible to pass a cell reference into the formula.

If I use the following, it works fine

=AVERAGEIFS('CALCS 2'!U$821:U$1765,'CALCS 2'!$T$821:$T$1765,"<30",'CALCS 2'!$T$821:$T$1765,">28")/100

But if i try to swap out "<30" for <A2 (for example), it comes back with #div/0 error.

Can anyone assist on how to fix it?

Thanks.

 

[FormR]

Hi, if you want the cell value to just contain the 30 and not the less than symbol then:

=AVERAGEIFS('CALCS 2'!U$821:U$1765,'CALCS 2'!$T$821:$T$1765,"<"&A1,'CALCS 2'!$T$821:$T$1765,">28")/100

Else if you want the cell to literally contain <30 then:

=AVERAGEIFS('CALCS 2'!U$821:U$1765,'CALCS 2'!$T$821:$T$1765,A1,'CALCS 2'!$T$821:$T$1765,">28")/100

 

[Murphyk]

That's incredible. Thanks so much for your help. There's no way that I would ever have managed that.

Should save me loads of time.

Cheers!