JeffFinnan
Board Regular
- Joined
- Aug 12, 2020
- Messages
- 60
- Office Version
- 2019
- Platform
- Windows
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
=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