I have profile data that I need to group into bins of one meter. Each
profile is going to be a variable length (about 1400 measurements per file).
I would like to automate the process of binning the data. For example, the
following columns of data would be grouped according to depth where all
measurements that occur at 7 meters would be averaged together and the ones
that occur at 8 meters would be done the same way.

Depth Photons
7.831 4.82E+05
7.995 3.30E+05
8.107 4.10E+05
8.209 5.47E+05
8.372 4.26E+05
8.617 4.83E+05
8.902 4.83E+05


In a file, there are 13 columns of data. Column A is empty, Column D has
the depth and all the other columns will be averaged together based on the
depth. Each file has one worksheet. I would like the macro to put the
binned data into another worksheet in the same file.
Any help or direction would be great.
-Ray

Ray wrote:
I have profile data that I need to group into bins of one meter. Each
profile is going to be a variable length (about 1400 measurements per file).
I would like to automate the process of binning the data. For example, the
following columns of data would be grouped according to depth where all
measurements that occur at 7 meters would be averaged together and the ones
that occur at 8 meters would be done the same way.

Depth Photons
7.831 4.82E+05
7.995 3.30E+05
8.107 4.10E+05
8.209 5.47E+05
8.372 4.26E+05
8.617 4.83E+05
8.902 4.83E+05


In a file, there are 13 columns of data. Column A is empty, Column D has
the depth and all the other columns will be averaged together based on the
depth. Each file has one worksheet. I would like the macro to put the
binned data into another worksheet in the same file.
Any help or direction would be great.
-Ray

If your bins are *consecutive* integers this will work:

In a spare column, say X, place numbers 7, 8, etc.

And in Y1 this array* formula, which can be filled down as needed:
=AVERAGE(IF(INT($D$2:$D$8)=X1,$E$2:$E$8))

*Commit the array formula by pressing Ctrl+Shift+Enter, not just Enter.

To place in another worksheet just change the sheet references
accordingly, or cut and paste the example from one sheet to another.

That's worked great. Thanks. What would the syntax be for Ctrl+Shift+Enter
so I can automate this process.

"smartin" wrote:

Ray wrote:
I have profile data that I need to group into bins of one meter. Each
profile is going to be a variable length (about 1400 measurements per file).
I would like to automate the process of binning the data. For example, the
following columns of data would be grouped according to depth where all
measurements that occur at 7 meters would be averaged together and the ones
that occur at 8 meters would be done the same way.

Depth Photons
7.831 4.82E+05
7.995 3.30E+05
8.107 4.10E+05
8.209 5.47E+05
8.372 4.26E+05
8.617 4.83E+05
8.902 4.83E+05


In a file, there are 13 columns of data. Column A is empty, Column D has
the depth and all the other columns will be averaged together based on the
depth. Each file has one worksheet. I would like the macro to put the
binned data into another worksheet in the same file.
Any help or direction would be great.
-Ray

If your bins are *consecutive* integers this will work:

In a spare column, say X, place numbers 7, 8, etc.

And in Y1 this array* formula, which can be filled down as needed:
=AVERAGE(IF(INT($D$2:$D$8)=X1,$E$2:$E$8))

*Commit the array formula by pressing Ctrl+Shift+Enter, not just Enter.

To place in another worksheet just change the sheet references
accordingly, or cut and paste the example from one sheet to another.

Ah yes, you did say macro..

Something like
Cells(R,C).FormulaArray = "=AVERAGE(IF(INT($D$2:$D$8)=X1,$E$2:$E$8))"

Ray wrote:
That's worked great. Thanks. What would the syntax be for Ctrl+Shift+Enter
so I can automate this process.

"smartin" wrote:

Ray wrote:
I have profile data that I need to group into bins of one meter. Each
profile is going to be a variable length (about 1400 measurements per file).
I would like to automate the process of binning the data. For example, the
following columns of data would be grouped according to depth where all
measurements that occur at 7 meters would be averaged together and the ones
that occur at 8 meters would be done the same way.

Depth Photons
7.831 4.82E+05
7.995 3.30E+05
8.107 4.10E+05
8.209 5.47E+05
8.372 4.26E+05
8.617 4.83E+05
8.902 4.83E+05


In a file, there are 13 columns of data. Column A is empty, Column D has
the depth and all the other columns will be averaged together based on the
depth. Each file has one worksheet. I would like the macro to put the
binned data into another worksheet in the same file.
Any help or direction would be great.
-Ray
If your bins are *consecutive* integers this will work:

In a spare column, say X, place numbers 7, 8, etc.

And in Y1 this array* formula, which can be filled down as needed:
=AVERAGE(IF(INT($D$2:$D$8)=X1,$E$2:$E$8))

*Commit the array formula by pressing Ctrl+Shift+Enter, not just Enter.

To place in another worksheet just change the sheet references
accordingly, or cut and paste the example from one sheet to another.