Forum Discussion
Variable Range
I am trying to take the average of a range of cells, where the range is a variable. I have a long column of y-values vs. x-values. I would like to take the average of N-y values where N depends on x. For example, say there are 1000 (x,y) pairs, and I want to create y-average where initially, for x0, there are 20 values of y to be averaged, and by the end of the array, there are 80 y values to be averaged.
I have a column, C, that has the number of values to be averaged (going from 20 to 80 in this example)
I was hoping to do something like D1=AVERAGE(B1:ADDRESS(C1,2)) since ADDRESS(C1,2) returns the value of $B$20. But this syntax doesn't work.
Any ideas?
Hi,
You may use something like
=AVERAGE(OFFSET(A1,0,0,<height parameter>,<width parameter>))
where A1 is the first cell in your range to average, and the latest two are references on the cells which define the size of the range
2 Replies
Hi,
You may use something like
=AVERAGE(OFFSET(A1,0,0,<height parameter>,<width parameter>))
where A1 is the first cell in your range to average, and the latest two are references on the cells which define the size of the range
- Thank you very much. I would not have figured this one out despite years of using Excel.
