MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Each data grouping needs to be averaged

  Asked By: Aubrey    Date: Jan 07    Category: MS Office    Views: 859

I recently lost a Basic program that I used to run on Excel spreadsheets to
create averages, and then insert a new blank line between the sets of data.
Each data grouping needs to be averaged.

Here is what it looks like:

Identifier Data 1 Data 2 Data 3 Data 4 Data 5 .... up to
max of Data 14

56902 1.2 3.5 22.6 12.2 26.6
56903 1.5 3.2 21.6 11.2 25.5
56904 1.6 3.0 22.5 10.2 22.4
Averages go here (under each data column) and blank line is inserted by
program to separate from next line.

56905 1.3 3.2 22.1 10.2 22.3
56906 1.2 3.1 22.0 11.2 23.5
56907 1.1 3.2 20.0 10.2 22.5
56908 1.0 3.2 20.3 11.2 22.6

56909 1.2 3.3 24.5 10.2 23.6

56910 1.0 3.5 25.5 11.2 26.9
56911 1.2 3.6 26.6 11.1 26.8

This goes on for 10,000 rows or more per spreadsheet. The Identifier (first
number) is just a sample number and does not get averaged. Just the Data 1
through Data 14 (some spreadsheets have Data 1 through Data 9, etc. - not
consistent). Some samples have several data points to be averaged, other times
there is just one sample (such as 56909 above) or two (56910 and 56911 above).
Is there a way to do this that isn't too difficult?



1 Answer Found

Answer #1    Answered By: Ellen Simpson     Answered On: Jan 07

Is there a way to do this that isn't too difficult?

I suppose that that depends on your definition of too
difficult. Break the problem down into smaller parts and
solve for each part.

Store the current row in a variable (startRow).

Figure out how to determine a row is empty. You will
probably be safe testing for a value in the first column.
Store this value in a variable (currentRow).

If you have only one row (currentRow == startRow + 1),
you can just insert  its data  again for the average. For
more than one row (currentRow > startRow + 1), you will
need to insert an average for each column thus for.

I am new to this list, but I assume you are not here
for someone to write a script for you. Some of the tasks
involved in this solution can be recorded macros. You can,
for example, record a macro to insert a row and then look
at the macro source to create general code to insert rows.

Didn't find what you were looking for? Find more on Each data grouping needs to be averaged Or get search suggestion and latest updates.