Hi everyone,
I have to write a script that will take a two dimensional range entered by the user, sort the data into ascending numerical order, average any duplicates in the range and then perform linear interpolation (or extrapolation) on the data to find a y value for an x value entered by the user.
For averaging the duplicate values, I am struggling, I have written this:
For i = 1 To k
yrsum = yrsortav(i)
yrcount = 1 'yrcount is the number of y values who have the same x value (duplicates)
If xrsort(i) = xrsort(i + 1) Then
yrcount = yrcount + 1 'adds 1 to yrcount for every duplicate
yrsum = yrsum + yrsortav(i + 1) 'y values with same x value are added together
yrav = yrsum / yrcount ' y average = sum of duplicates/by number of duplicates
yrsortav(i) = yrav ' relevant cells in range overwritten with new average value of y
yrsortav(i + 1) = yrav
End If
Next i
k is equal to the number of rows in the range minus 1, xrsort is my sorted x range (the left-hand column of the 2D range), yrsort is the sorted y range (right hand column of 2D range). yrcount is the number of duplicates, for instance, if xrsort(1) = xrsort(2), yrcount will be 2, and the sum of their corresponding y values will be divided by two to produce an average - yrav
As it currently is, this code works if there are two values of y with the same x, it nearly works for 3 values, it does some kind of averaging, but does not give the right numbers.
Please can you advise me what I am doing wrong and how I might fix it
Thanks in Advance
Michael
I have to write a script that will take a two dimensional range entered by the user, sort the data into ascending numerical order, average any duplicates in the range and then perform linear interpolation (or extrapolation) on the data to find a y value for an x value entered by the user.
For averaging the duplicate values, I am struggling, I have written this:
For i = 1 To k
yrsum = yrsortav(i)
yrcount = 1 'yrcount is the number of y values who have the same x value (duplicates)
If xrsort(i) = xrsort(i + 1) Then
yrcount = yrcount + 1 'adds 1 to yrcount for every duplicate
yrsum = yrsum + yrsortav(i + 1) 'y values with same x value are added together
yrav = yrsum / yrcount ' y average = sum of duplicates/by number of duplicates
yrsortav(i) = yrav ' relevant cells in range overwritten with new average value of y
yrsortav(i + 1) = yrav
End If
Next i
k is equal to the number of rows in the range minus 1, xrsort is my sorted x range (the left-hand column of the 2D range), yrsort is the sorted y range (right hand column of 2D range). yrcount is the number of duplicates, for instance, if xrsort(1) = xrsort(2), yrcount will be 2, and the sum of their corresponding y values will be divided by two to produce an average - yrav
As it currently is, this code works if there are two values of y with the same x, it nearly works for 3 values, it does some kind of averaging, but does not give the right numbers.
Please can you advise me what I am doing wrong and how I might fix it
Thanks in Advance
Michael
