Using ADDRESS function in AVERAGE

I need to use the ADDRESS function to define a range from which an average can be determined. My current function is as follows:

Please Login or Register  to view this content.

I am wondering why this keeps giving me the VALUE error...

Because ADDRESS returns a String and thus to be converted to a Range you would need to encase within INDIRECT - therefore making function Volatile - better to use INDEX but before we can demo can you elaborate in terms of how row/column flags are identified / sourced ?

Also to check, ADDRESS(4,363) would be $MY$4 ... not sure if you meant $B$4:$B$363 or $B$4:$MY$4 ?

Try:

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

or better:

=AVERAGE(INDEX(4:4,,2):INDEX(4:4,,363))