Entering the following as an array formula works:

=AVERAGE(INDIRECT(ADDRESS(4,2)&":"&ADDRESS(4,6)))

To enter an array formula, hit CTRL + SHIFT + ENTER instead of just
ENTER.

Dave

On Dec 17, 11:09 am, wrote:
Hi help needed

I understand how to manipulate indirect & address function to get a
value of a cell.

However, I would like to get average value of a range instead of
the value of a cell.

I can use address(....) & ":" & address(...) to produce something
like $F$5:$F$10

However, average function does not take $F$5:$F$10 and calculate
the average value.

Is it possible to do so in excel without writing a VB macro?

Thanks

Tim