help with excluding 0s from code
 

Hello everyone, I have the following vba code, which takes the value i
column D and column C and calculates the difference.

Then for each occurance of a certain value in column V it calculate
the average difference between D anc C.

What I want to add to the code is a line which says that where th
difference between D and C is 0 (zero) the line is excluded from an
calculation of the average.

Hope this makes sense. All help greatfully received.

Sub avgtime()
Range("B9:G20000").ClearContents
a = 3
b = 3
c = (Worksheets("data").Cells(a, 4) - Worksheets("data").Cells(a
3).Value)
Do Until Worksheets("data").Cells(a, 19) = Empty
If Worksheets("data").Cells(a, 19) = Cells(3, 3) Or Cells(3, 3)
Empty Then
If Worksheets("data").Cells(a, 36) = Cells(4, 3) Or Cells(4, 3
= Empty Then
b = 9
Do Until Cells(b, 2) = Empty
If Cells(b, 2) = Worksheets("data").Cells(a, 22) The
GoTo 99
b = b + 1
Loop
Cells(b, 2) = Worksheets("data").Cells(a, 22)
End If
End If
99 a = a + 1
Loop
b = 9
Do Until Cells(b, 2) = Empty
a = 3
Do Until Worksheets("data").Cells(a, 19) = Empty
If Worksheets("data").Cells(a, 19) = Cells(3, 3) Or Cells(3, 3)
Empty Then
If Worksheets("data").Cells(a, 36) = Cells(4, 3) Or Cells(4, 3
= Empty Then
If Worksheets("data").Cells(a, 22) = Cells(b, 2) Then
timesum = timesum + (Worksheets("data").Cells(a
4).Value - Worksheets("data").Cells(a, 3).Value)
items = items + 1
End If
End If
End If
a = a + 1
Loop
If items = Empty Then items = 1
Cells(b, 6) = items
Cells(b, 3) = timesum / items
Cells(b, 7) = items * (timesum / items)
timesum = 0
items = 0
b = b + 1
Loop
Call SortValues
Call TrimCalc
End Su

--
Message posted from http://www.ExcelForum.com

help with excluding 0s from code
 

dim diff as double
Const zero As Double = 0.00001

If Worksheets("data").Cells(a, 22) = Cells(b, 2) Then
diff = Worksheets("data").Cells(a,4).Value - _
Worksheets("data").Cells(a, 3).Value
if abs(diff) zero then
timesum = timesum + diff
items = items + 1
End if
End If


if you are just working with integers, then you could use

dim diff as Long


if diff < 0 then


--
Regards,
Tom Ogilvy

"scottwilsonx " wrote in
message ...
Hello everyone, I have the following vba code, which takes the value in
column D and column C and calculates the difference.

Then for each occurance of a certain value in column V it calculates
the average difference between D anc C.

What I want to add to the code is a line which says that where the
difference between D and C is 0 (zero) the line is excluded from any
calculation of the average.

Hope this makes sense. All help greatfully received.

Sub avgtime()
Range("B9:G20000").ClearContents
a = 3
b = 3
c = (Worksheets("data").Cells(a, 4) - Worksheets("data").Cells(a,
3).Value)
Do Until Worksheets("data").Cells(a, 19) = Empty
If Worksheets("data").Cells(a, 19) = Cells(3, 3) Or Cells(3, 3) =
Empty Then
If Worksheets("data").Cells(a, 36) = Cells(4, 3) Or Cells(4, 3)
= Empty Then
b = 9
Do Until Cells(b, 2) = Empty
If Cells(b, 2) = Worksheets("data").Cells(a, 22) Then
GoTo 99
b = b + 1
Loop
Cells(b, 2) = Worksheets("data").Cells(a, 22)
End If
End If
99 a = a + 1
Loop
b = 9
Do Until Cells(b, 2) = Empty
a = 3
Do Until Worksheets("data").Cells(a, 19) = Empty
If Worksheets("data").Cells(a, 19) = Cells(3, 3) Or Cells(3, 3) =
Empty Then
If Worksheets("data").Cells(a, 36) = Cells(4, 3) Or Cells(4, 3)
= Empty Then
If Worksheets("data").Cells(a, 22) = Cells(b, 2) Then
timesum = timesum + (Worksheets("data").Cells(a,
4).Value - Worksheets("data").Cells(a, 3).Value)
items = items + 1
End If
End If
End If
a = a + 1
Loop
If items = Empty Then items = 1
Cells(b, 6) = items
Cells(b, 3) = timesum / items
Cells(b, 7) = items * (timesum / items)
timesum = 0
items = 0
b = b + 1
Loop
Call SortValues
Call TrimCalc
End Sub


---
Message posted from http://www.ExcelForum.com/

help with excluding 0s from code
 

Tom, thank you very much.

Scott.


---
Message posted from http://www.ExcelForum.com/