Averaging a long list of numbers question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

qcrob - 24 Jan 2008 14:23 GMT

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.

Reply to this Message

Dave Peterson - 24 Jan 2008 14:52 GMT

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.

> I have a column of numbers running relatively sequentially with larger
> gaps in between
[quoted text clipped - 5 lines]
> this with a combination of basic auto filter and conditional
> formatting or the like, not VBA, is this possible? Thanks in advance.

Signature

Dave Peterson

Reply to this Message

Tyro - 24 Jan 2008 20:42 GMT

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 your data is in A2:Axxx (headers in A1), then I'd use a helper column
> (column
[quoted text clipped - 21 lines]
>> this with a combination of basic auto filter and conditional
>> formatting or the like, not VBA, is this possible? Thanks in advance.

Reply to this Message

Dave Peterson - 24 Jan 2008 22:34 GMT

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?

> 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
[quoted text clipped - 33 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message