calculate average from an array with controlled length

**Christopher83** · 02-07-2012, 05:33 AM

Hi,

My problem:

=AVERAGE(H2:H25)

I want the row number for cell H25 to be controlled by a number from another cell. Lets say that the cell "A1" contains the value499, then I want the average calculation to be =AVERAGE(H2:H499). There is probably an easy way to solve this, but my excel-knowledge is not that good.

Thanks in advance!

**zbor** · 02-07-2012, 05:35 AM

Here, try this:

=AVERAGE(OFFSET(H2;0;0;A1))

(replace ; with , if needed)

**Christopher83** · 02-07-2012, 05:47 AM

Thank you zbor!!!

It works!

Thanks for the incredible fast reply!

**Marcol** · 02-07-2012, 05:48 AM

@ zbor
Should it not be?

Please Login or Register  to view this content.

[EDIT]
A non-volatile solution might be

Please Login or Register  to view this content.

**Christopher83** · 02-07-2012, 05:50 AM

Yes, I realized that, but I did not want to bother with an "unnessicary" question!
Thanks

**zbor** · 02-07-2012, 05:57 AM

If you put 1 in A1 it will take one cell. If you have A1-1 you will get #REF! error.

But I agree, =AVERAGE(H2:INDEX(H:H,A1,1)) is better way.

**bebo021999** · 02-07-2012, 06:01 AM

How about
=AVERAGE(INDIRECT("H2:H"&A1))

**zbor** · 02-07-2012, 06:06 AM

It will work too, but still above solution is better because INDIRECT is alo a Volatile function.

calculate average from an array with controlled length

LinkBack

Thread Tools

Rate This Thread

Display

calculate average from an array with controlled length

Re: calculate average from an array with controlled length

Re: calculate average from an array with controlled length

Re: calculate average from an array with controlled length

Re: calculate average from an array with controlled length

Re: calculate average from an array with controlled length

Re: calculate average from an array with controlled length

Re: calculate average from an array with controlled length

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions