Is it possible to use address function in average function

[timothy.pi]

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

 

[Dave F]

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

 

[Ron Coderre]

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)

 

[Dave F]

I believe, actually, that you don't need an array formula. You can
just enter the formula I give above as a regular formula...

Dave

 

[RagDyeR]

With these values in:
C1 = 5
C2 = 6
C3 = 10

Try this:

=AVERAGE(INDIRECT(ADDRESS(C1,C2)):INDIRECT(ADDRESS(C3,C2)))
OR this:
=AVERAGE(INDIRECT(ADDRESS(C1,C2)&":"&ADDRESS(C3,C2)))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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

 

[timothy.pi]

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?

 

[Ron Coderre]

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)

 

[Ron Coderre]

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)

 

[RagDyer]

Give this a try:

=AVERAGE(INDEX(Sheet2!C:C,MATCH($A2,Sheet2!$A$2:$A$2000,0)):INDEX(Sheet2!C:C,MATCH($A2,Sheet2!$A$2:$A$2000,0)-19))

*However*, I'm confused by the way you're referencing your ranges.
Your match range starts at A2.
What happens if Match() finds a match in say, A10?
That makes the *ending* cell of the Average range to be C9.
SO, you then subtract *19 rows* from C9 to reference your *starting*
cell!?!?
Since that doesn't exist (C-10), you'll get a #Value! error.

It sounds to me that perhaps you have to re-figure your logic.