Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with excluding 0s from code
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Min Excluding Value | Excel Discussion (Misc queries) | |||
Averages excluding #N/A | Excel Discussion (Misc queries) | |||
excluding #N/A | Excel Discussion (Misc queries) | |||
MIN excluding 0s | Excel Worksheet Functions | |||
Formula excluding #N/A | Excel Programming |