How to copy formulas with clustered data?

  • Thread starter Thread starter Ezra
  • Start date Start date
E

Ezra

Hi all,

I want to plot the averages from a large dataset. I want to obtain the
average of a group of values grouped in 5 rows at the time, but when I
copy it down, it skips the data one row down, instead of five. For
instance I use
=Average(A1:A5), and when I copy it down I want it to calculate
=Average(A6:A10), but instead it shows =Average(A2:A6). Does anybody
know how to do this?

My data looks like this (without the spaces between the lines):

Day Value
14 0.04
14 0.04
14 0.79
14 0.27
14 0.05

21 0.35
21 0.42
21 15.18
21 15.77
21 12.23

28 25.0
28 11.6
28 89.0
28 36.7
28 60.2

I need the average of the five values of day 14, 21, and 28

Thank you.
Gerardo
 
Firstly if column A has DAY and B has VALUE, it seems you want average of
five b-values
In C2 (assume row 1 has headers) =AVERAGE(B2:B6)
In C3 =NA() (so you can chart all of column C)
Copy C3 to C4 : C6
Select C2:C6 and copy down the column

Day Value Average
14 0.04 =AVERAGE(B2:B6)
14 0.04 =NA()
14 0.79 =NA()
14 0.27 =NA()
14 0.05 =NA()
remove this blank row
21 0.35 =AVERAGE(B7:B11)
21 0.42 =NA()
21 15.18 ....etc
21 15.77
21 12.23
remove blank row
28 25.0
28 11.6
28 89.0
28 36.7
28 60.2
Click to expand...
best wishes
 
Use subtotals under the data menu. The wizard that pops up lets you
average too. Then just shrink the view ( click on the little '2' in
the box at the upper left of the window that shows up when you subtotal
) and you have your data.

Horst
 
Bernard, and Horst

Thank you for your input. I was able to get the averages with both
methods and worked great. After I got the results I had to do some data
sorting in order to make a table and plot the resuls.

I have done these calculations manually over and over, but now you have
saved me endless hours of work!

best,
Gerardo




flyswiftly ha escrito:
 
Back
Top