Hi there all!

I need help with writing a macro that can sort out an X
column of various numbers (to several decimal points) into
a column (X") of closely related whole numbers (eg, 1.1
becomes 1.0).

Eg, I would like -0.5 to 0.49 to become 0
0.5 to 1.49 to become 1
1.5 to 2.49 to become 2
2.5 to 3.49 to become 3 and so on...up to n+1

Next to the original column there is another 3 columns
(Y1, Y2 & Y3) of data which I do not want to change the
values of. However, I would like them to line up with
there new corresponding whole number.

What I am trying to do is take an average of n rows for
each of the Y columns where the X values are approximately
the same. Because the data are from a physiological
model, no 2 X values are ever the same, but are very
similar in value. I have no control over how many of the
X values will fall into each of the ranges (ie 1.5 to 1.49
etc).

A truncated example of the data is below where I have
reduced the numbers in each range to only a few. The data
is arranged into column X = Diastolic Pressure, Y1 = APD
20, Y2 = APD 50, Y3 = APD 80 (APD stands for action
potential duration! -for all those interested)

Diastolic
Pressure APD20 APD50 APD80
0.159 0.0598 0.0893 0.1048
0.7672 0.0593 0.0883 0.1023
1.5387 0.0623 0.0913 0.1043
1.1363 0.0627 0.0892 0.1042
2.5277 0.06 0.087 0.101
3.3912 0.0623 0.0883 0.1033
3.0863 0.0604 0.0864 0.1014
4.5328 0.0435 0.07 0.086
5.4996 0.0609 0.0884 0.1034
5.4128 0.0624 0.0884 0.1034
6.8217 0.0607 0.0882 0.1032
6.5135 0.0525 0.0815 0.0945
7.0555 0.0464 0.0749 0.0894
7.0427 0.0613 0.0878 0.1048
7.0949 0.0621 0.0886 0.1051
7.8278 0.0611 0.0881 0.1031
8.0798 0.059 0.088 0.101
8.7448 0.0633 0.0903 0.1063
9.4875 0.0656 0.0931 0.1086
9.607 0.0469 0.0754 0.0909
9.3066 0.0479 0.0769 0.0924
8.9614 0.0494 0.0789 0.0939
8.959 0.0491 0.0776 0.0936
10.4847 0.0505 0.079 0.0945
10.167 0.0501 0.0781 0.0936
10.167 0.0469 0.0794 0.0914
11.5381 0.0476 0.0726 0.0886
12.4123 0.0528 0.0778 0.0943
11.6682 0.048 0.075 0.0915
11.6558 0.0515 0.0805 0.0945

Janet,

Enter this formula in E2, then fill down the column:

=FLOOR(A2+0.5,1)

To get the averages, take a lok at the SUMIF and COUNTIF
functions, e.g.

Average of column B where column E = 1:
=SUMIF(E2:E100,1,B2:B100)/COUNTIF(E2:E100,1)

Cheers,
Dave.
-----Original Message-----
Hi there all!

I need help with writing a macro that can sort out an X
column of various numbers (to several decimal points)
into
a column (X") of closely related whole numbers (eg, 1.1
becomes 1.0).

Eg, I would like -0.5 to 0.49 to become 0
0.5 to 1.49 to become 1
1.5 to 2.49 to become 2
2.5 to 3.49 to become 3 and so on...up to n+1

Next to the original column there is another 3 columns
(Y1, Y2 & Y3) of data which I do not want to change the
values of. However, I would like them to line up with
there new corresponding whole number.

What I am trying to do is take an average of n rows for
each of the Y columns where the X values are
approximately
the same. Because the data are from a physiological
model, no 2 X values are ever the same, but are very
similar in value. I have no control over how many of the
X values will fall into each of the ranges (ie 1.5 to
1.49
etc).

A truncated example of the data is below where I have
reduced the numbers in each range to only a few. The
data
is arranged into column X = Diastolic Pressure, Y1 = APD
20, Y2 = APD 50, Y3 = APD 80 (APD stands for action
potential duration! -for all those interested)

Diastolic
Pressure APD20 APD50 APD80
0.159 0.0598 0.0893 0.1048
0.7672 0.0593 0.0883 0.1023
1.5387 0.0623 0.0913 0.1043
1.1363 0.0627 0.0892 0.1042
2.5277 0.06 0.087 0.101
3.3912 0.0623 0.0883 0.1033
3.0863 0.0604 0.0864 0.1014
4.5328 0.0435 0.07 0.086
5.4996 0.0609 0.0884 0.1034
5.4128 0.0624 0.0884 0.1034
6.8217 0.0607 0.0882 0.1032
6.5135 0.0525 0.0815 0.0945
7.0555 0.0464 0.0749 0.0894
7.0427 0.0613 0.0878 0.1048
7.0949 0.0621 0.0886 0.1051
7.8278 0.0611 0.0881 0.1031
8.0798 0.059 0.088 0.101
8.7448 0.0633 0.0903 0.1063
9.4875 0.0656 0.0931 0.1086
9.607 0.0469 0.0754 0.0909
9.3066 0.0479 0.0769 0.0924
8.9614 0.0494 0.0789 0.0939
8.959 0.0491 0.0776 0.0936
10.4847 0.0505 0.079 0.0945
10.167 0.0501 0.0781 0.0936
10.167 0.0469 0.0794 0.0914
11.5381 0.0476 0.0726 0.0886
12.4123 0.0528 0.0778 0.0943
11.6682 0.048 0.075 0.0915
11.6558 0.0515 0.0805 0.0945






.

Hi Dave,
Thank you very much for your help.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!