3

I've been trying to use AVERAGE with INDIRECT but keeps giving me errors.

Now I am using Average like this: AVERAGE(Results!C2:C51) I need to get data from another sheet "Results". But in my current sheet I got the range of the rows set in two cells. 

+-------------------+
|    ...  E   F     |
|   +-------+-------+
| 2 |...| 2 | 51|   |
|   +---------------+
| 3 |   | 52|101|   |
|   +---------------+
| 4 |   |   |   |   |
+---+---+---+---+---+

I've tried like this, but it's not working:

AVERAGE(Results!INDIRECT("C"&E2):INDIRECT("C"&F2))
Danick
  • 135
  • 2
  • 9

2 Answers2

4

This should do it:

=AVERAGE(INDIRECT("Results!C"&E2&":C"&F2))
zipa
  • 27,316
  • 6
  • 40
  • 58
1

The answer posted by zipa is correct. Here is an alternaive that will allow you to avoid INDIRECT() entirely:

=AVERAGE(INDEX(Results!C:C,E2):INDEX(Results!C:C,F2))

enter image description here

This is based on Scott Craner's Answer to a question I asked previously.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99