Indirect reference

[Victor]

I want to calculate an average.
The formula is: AVERAGE(C4:Cx)
X represents the value of: COUNT(C4:C16)and is therefore
variable.
How do I input X value in my AVERAGE formula?

I think it has something to do with INDIRECT but I
couldn't find an example I could apply to my specific
problem.

Thanks in avance

 

[papou]

Hello Victor
=AVERAGE(INDIRECT("C4:C" & COUNT(C4:C16))

HTH
Regards
Pascal

 

[Guest]

Your formula returns the value of C4 ...?

If I do an independent "count(C4:C16)" in a separate cell
I get "3" which is the right value. Your formula seems to
return "1" as the value.

 

[Bob Phillips]

Why not just use

=AVERAGE(C4:C16)

The count just returns the number of non-blank cells, and average only works
on non-blanks

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Your formula returns the value of C4 ...?

If I do an independent "count(C4:C16)" in a separate cell
I get "3" which is the right value. Your formula seems to
return "1" as the value.