Typo......"SUM" should be "AVERAGE":

=AVERAGE(INDIRECT(
ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0),3,1,TRUE, "Sheet2")&":"&
ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0)-19,3,1,TRUE)))

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Ron Coderre" wrote in message
...
You can't use the sheet reference in both sections of the formula.

Try this:
=SUM(INDIRECT(
ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0),3,1,TRUE, "Sheet2")&":"&
ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0)-19,3,1,TRUE)))

A sample of the interal section would be: "Sheet2!$C$65:$C$46"

Does that help?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


wrote in message
...
Ron,

It does not work. I got #REF

here is the exact code I use:

ADDRESS(MATCH($A2,Sheet2!$A$2:$A$2000,0),
3,1,TRUE,"Sheet2")&":"&ADDRESS(MATCH($A2,Sheet2!$A $2:$A
$2000,0)-19,3,1,TRUE,"Sheet2")

above code gives me : Sheet2!$C$65:Sheet2!$C$46

However, average(indirect(above code)) give me #REF

any idea?


On Dec 17, 11:14 am, "Ron Coderre"
wrote:
With
A1: F5
A2: F10

This formula returns the average of F5:F10
A3: =AVERAGE(INDIRECT(A1&":"&A2))

Is that something you can work with?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

wrote in message

...

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