Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to copy formulas with clustered data?
Hi all,
I have a large dataset and 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to copy formulas with clustered data?
Two possibilities:
1) SUMIF/COUNTIF 2) AVERAGE IF as an array formula. To do 1, do: =SUMIF(A2:B16,14,B2:B16)/COUNTIF(A2:A16,14) where the criteria is 14. Replace with the appropriate value. I forget how to do 2, but I know it's possible. Dave -- Brevity is the soul of wit. "Ezra" wrote: Hi all, I have a large dataset and 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to copy formulas with clustered data?
Try this:
=AVERAGE(INDEX(A:A,5*ROWS($1:1)-4):INDEX(A:A,5*ROWS($1:1))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ezra" wrote in message ps.com... Hi all, I have a large dataset and 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to keep the formulas and change their source data? | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Keeping data without losing Formula's | Excel Discussion (Misc queries) | |||
Automatically pasting worksheet data to new worksheet with formulas | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |