Reference cell value in AVERAGEIFS formula

0
votes

In excel, I am trying to average a group of cells less than a value (contained within a cell) and greater than another value (contained within another cell). Is there a way to write a formula (I have been using AVERAGEIFS) which can automatically reference a particular cell. It is very time consuming and error prone to manually alter the formula over several workbook sheets.

I have written the following formula by manually entering in the values calculated in another cell.

=AVERAGEIFS(Q2:Q97,Q2:Q97,"<790",Q2:Q97,">394")

Is there a way to change this formula so that it automatically reference, for example (this throws an error in excel).

=AVERAGEIFS(Q2:Q97,Q2:Q97,C5,Q2:Q97,C7)

1
excel-formula

1 Answers

1
votes

AVERAGEIFS use strings as the criteria: "<790"

So you can concatenate a string with the Cell reference: "<" & C5

So:

=AVERAGEIFS(Q2:Q97,Q2:Q97,"<" & C5,Q2:Q97,">" & C7)