Averaging a long list of numbers question

  • Thread starter Thread starter qcrob
  • Start date Start date
Q

qcrob

I have a column of numbers running relatively sequentially with larger
gaps in between
(1345,1352,1359,1376,1421,1427,1433,1656,1664,1721,1724,1727 etc).
I would like to separate and average each sequentially running group
(1345,1352,1359,1376)=avg,(1421,1427,1433)=avg,(1656,1664)=avg,
(1721,1724,1727)=avg etc. and hide all rows with data leaving only one
row for each grouping displaying the group average. Can I accomplish
this with a combination of basic auto filter and conditional
formatting or the like, not VBA, is this possible? Thanks in advance.
 
If your data is in A2:Axxx (headers in A1), then I'd use a helper column (column
B?) with a formula like:

=int(a2/100)
Then drag this down the column.
Add a header to B1

Then use Data|subtotals and for each change in the column B data, use Average
for the data in column B.

Then you can use the outlining symbols to the left to hide the details. Make
sure you try the 1 and 2 at the top of those outlining symbols, too.
 
After creating the subtotals in column B the data from column B will be in
column C with average titles in column B. A2:Axxx has to be copied and
pasted special in C2 with Skip Blanks checked to get the original data into
column C since column C is the original data divided by 100. If satisfied
with that, column A could be deleted

Tyro
 
If the original data is in column A and the helper column is in column B, then
the labels for each of the categories will still be in column B. And the
averages for each group will be in column A.

Or am I missing something?
 
Back
Top