compare adjacent cells and calculate averages

[Guest]

Hello All,

I have three columns of raw data which are x,y and z coordinates
("observations") for several different points, say points A to Z. The number
of points is variable as is the number of observations of any given point.

I want to group the data into blocks seperated by blank rows by comparing
each observation with the previous one and then assuming that if the
difference in x,y or z is more than say 0.1, then the next observation is of
a new point.

I then would like to calculate the average value of each blockand display
these.

The ideal result would be to have the original data in columns A to C, the
grouped data in coumns E to G, and the averages in I to K.

This is beyond my meagre VBA abilities. Please help!

Thanks in advance

BTW I'm using Excel 2003

 

[Guest]

You'll have to give an example of what you are trying to achieve. I'm afraid
your explanation isn't very clear to me.

Ian

 

[Guest]

Okay, I'll see what I can do.

Let's say I've got 70 GPS readings from 5 different locations. Each reading
is in the form of x,y,z coordinates. Let's say I've taken 15 readings at
point A, 10 at B, 12 at C, 18 at D, and 15 at E. I want to average these
readings to get more accurate value for the coordinates of the five points.

In Excel, I have 70 rows, 3 columns. Now, I happen to know that the first 15
rows were taken at point A, the next 10 at B and so on. For excel to be able
to identify which points are where, it must compare each row with the
previous one until such time as one or more of the 3 coords differs from the
previous row by more than 0.1.

I envisage something along the lines of:

start at row 1. Copy a1:c1 to e1:g1

row 2
compare with row 1. conclusion: same point. Action: copy to e2:g2

and so on until

row 19
compare with row 18. conclusion: new point. Action: insert blank row. copy
a19:c19 to e20:g20

The idea being to seperate the readings into blocks so that they can then
be averaged and the averages displayed.

I said in the first post that

The ideal result would be to have the original data in columns A to C, the
grouped data in coumns E to G, and the averages in I to K.

but it's not crucial to keep the original data on the worksheet as this will
still exist elsewhere.

Anyway, hope this clarifies.

Marcel

 

[Ardus Petus]

Try this:
'----------
Sub group()
Dim iRow As Long
Dim iCol As Long
Dim iLastRow As Long
Dim iCurrRow As Long
iLastRow = Range("A1").End(xlDown).Row
iCurrRow = iLastRow
For iRow = iLastRow To 2 Step -1
For iCol = 1 To 3
If Abs(Cells(iRow, iCol).Value - _
Cells(iRow - 1, iCol).Value) >= 0.1 Then

Cells(iRow, "E").Resize(, 3).FormulaR1C1 = _
"=AVERAGE(RC[-4]:R[" & iCurrRow - iRow & "]C[-4])"
iCurrRow = iRow
Rows(iRow).Insert
Exit For
End If
Next iCol
Next iRow
iRow = 1
Cells(iRow, "E").Resize(, 3).FormulaR1C1 = _
"=AVERAGE(RC[-4]:R[" & iCurrRow - iRow & "]C[-4])"

End Sub
'------------

 

[Guest]

Thanks Ardus Petus, I appreciate it.

I tried it with some sample data. The macro runs ok but doesn't generate any
new output. If I hover the cursor over parts of code, it tells me that iRow =
1, iCurrRow = 65536. Is that right? Also, does the Abs(Cells(iRow,
iCol).Value part look at each cell's value?

I'm guessing that there is no output because the data doesn't satisfy the If
statement.

Any thoughts?

Try this:
'----------
Sub group()
Dim iRow As Long
Dim iCol As Long
Dim iLastRow As Long
Dim iCurrRow As Long
iLastRow = Range("A1").End(xlDown).Row
iCurrRow = iLastRow
For iRow = iLastRow To 2 Step -1
For iCol = 1 To 3
If Abs(Cells(iRow, iCol).Value - _
Cells(iRow - 1, iCol).Value) >= 0.1 Then

Cells(iRow, "E").Resize(, 3).FormulaR1C1 = _
"=AVERAGE(RC[-4]:R[" & iCurrRow - iRow & "]C[-4])"
iCurrRow = iRow
Rows(iRow).Insert
Exit For
End If
Next iCol
Next iRow
iRow = 1
Cells(iRow, "E").Resize(, 3).FormulaR1C1 = _
"=AVERAGE(RC[-4]:R[" & iCurrRow - iRow & "]C[-4])"

End Sub
'------------

Okay, I'll see what I can do.

Let's say I've got 70 GPS readings from 5 different locations. Each
reading
is in the form of x,y,z coordinates. Let's say I've taken 15 readings at
point A, 10 at B, 12 at C, 18 at D, and 15 at E. I want to average these
readings to get more accurate value for the coordinates of the five
points.

In Excel, I have 70 rows, 3 columns. Now, I happen to know that the first
15
rows were taken at point A, the next 10 at B and so on. For excel to be
able
to identify which points are where, it must compare each row with the
previous one until such time as one or more of the 3 coords differs from
the
previous row by more than 0.1.

I envisage something along the lines of:

start at row 1. Copy a1:c1 to e1:g1

row 2
compare with row 1. conclusion: same point. Action: copy to e2:g2

and so on until

row 19
compare with row 18. conclusion: new point. Action: insert blank row. copy
a19:c19 to e20:g20

The idea being to seperate the readings into blocks so that they can then
be averaged and the averages displayed.

I said in the first post that



but it's not crucial to keep the original data on the worksheet as this
will
still exist elsewhere.

Anyway, hope this clarifies.

Marcel

 

[Guest]

Tried it again today. It works! Dunno what I did wrong yesterday...

Thanks very much for your assistance.

Thanks Ardus Petus, I appreciate it.

I tried it with some sample data. The macro runs ok but doesn't generate any
new output. If I hover the cursor over parts of code, it tells me that iRow =
1, iCurrRow = 65536. Is that right? Also, does the Abs(Cells(iRow,
iCol).Value part look at each cell's value?

I'm guessing that there is no output because the data doesn't satisfy the If
statement.

Any thoughts?

Try this:
'----------
Sub group()
Dim iRow As Long
Dim iCol As Long
Dim iLastRow As Long
Dim iCurrRow As Long
iLastRow = Range("A1").End(xlDown).Row
iCurrRow = iLastRow
For iRow = iLastRow To 2 Step -1
For iCol = 1 To 3
If Abs(Cells(iRow, iCol).Value - _
Cells(iRow - 1, iCol).Value) >= 0.1 Then

Cells(iRow, "E").Resize(, 3).FormulaR1C1 = _
"=AVERAGE(RC[-4]:R[" & iCurrRow - iRow & "]C[-4])"
iCurrRow = iRow
Rows(iRow).Insert
Exit For
End If
Next iCol
Next iRow
iRow = 1
Cells(iRow, "E").Resize(, 3).FormulaR1C1 = _
"=AVERAGE(RC[-4]:R[" & iCurrRow - iRow & "]C[-4])"

End Sub
'------------

Okay, I'll see what I can do.

Let's say I've got 70 GPS readings from 5 different locations. Each
reading
is in the form of x,y,z coordinates. Let's say I've taken 15 readings at
point A, 10 at B, 12 at C, 18 at D, and 15 at E. I want to average these
readings to get more accurate value for the coordinates of the five
points.

In Excel, I have 70 rows, 3 columns. Now, I happen to know that the first
15
rows were taken at point A, the next 10 at B and so on. For excel to be
able
to identify which points are where, it must compare each row with the
previous one until such time as one or more of the 3 coords differs from
the
previous row by more than 0.1.

I envisage something along the lines of:

start at row 1. Copy a1:c1 to e1:g1

row 2
compare with row 1. conclusion: same point. Action: copy to e2:g2

and so on until

row 19
compare with row 18. conclusion: new point. Action: insert blank row. copy
a19:c19 to e20:g20

The idea being to seperate the readings into blocks so that they can then
be averaged and the averages displayed.

I said in the first post that

The ideal result would be to have the original data in columns A to C,
the
grouped data in coumns E to G, and the averages in I to K.


but it's not crucial to keep the original data on the worksheet as this
will
still exist elsewhere.

Anyway, hope this clarifies.

Marcel

:

You'll have to give an example of what you are trying to achieve. I'm
afraid
your explanation isn't very clear to me.

Ian

:

Hello All,

I have three columns of raw data which are x,y and z coordinates
("observations") for several different points, say points A to Z. The
number
of points is variable as is the number of observations of any given
point.

I want to group the data into blocks seperated by blank rows by
comparing
each observation with the previous one and then assuming that if the
difference in x,y or z is more than say 0.1, then the next observation
is of
a new point.

I then would like to calculate the average value of each blockand
display
these.

The ideal result would be to have the original data in columns A to C,
the
grouped data in coumns E to G, and the averages in I to K.

This is beyond my meagre VBA abilities. Please help!

Thanks in advance

BTW I'm using Excel 2003