Can one get the average of a variable range determined by formula?

[JeffFinnan]

These two formulas get the proper addresses for two numbers.

=CELL("address",INDEX(E$2:E$200,MATCH(AW3,B$2:B$200,0))) correctly yields $E$61

=CELL("address",INDEX(E$2:E$200,MATCH(AX3,B$2:B$200,0))) correctly yields $E$131

AW3 and AX3 are dates. The E range are just numbers.

If I type in =AVERAGE($E$61:$E$131), I get the proper average.

My thinking is that using =AVERAGE(CELL("address",INDEX(E$2:E$200,MATCH(AW3,B$2:B$200,0))):CELL("address",INDEX(E$2:E$200,MATCH(AX3,B$2:B$200,0))) would give the average for the range of numbers too. Instead I get a DIV/0! error. What do I need to do to get the average of a variable range of cell values?

Thanks,
Jeff

 

[StephenCrump]

Try:

=AVERAGE(INDEX(E$2:E$200,MATCH(AW3,B$2:B$200,)):INDEX(E$2:E$200,MATCH(AX3,B$2:B$200,)))

 

[JeffFinnan]

Try:

=AVERAGE(INDEX(E$2:E$200,MATCH(AW3,B$2:B$200,)):INDEX(E$2:E$200,MATCH(AX3,B$2:B$200,)))

That works. Thanks much.

 

[JeffFinnan]

Try:

=AVERAGE(INDEX(E$2:E$200,MATCH(AW3,B$2:B$200,)):INDEX(E$2:E$200,MATCH(AX3,B$2:B$200,)))

Not sure why this works. I tested to make sure.

=AVERAGE($E$61:$E$131) yields 77.0 and your formula also yields 77.0.

Thanks again