Saladsamurai
08-27-2009, 12:16 PM
is that I suck at them.
I have attached file.
I have done a CountIf worksheet function to see how many cells contain data in WorkSheets("Best CI CFD"). It is about 384.
Now, in my code, in the first block (the only part UN commented-out) I have a nested For Loop / Nested If statements that check to see whether
WorkSheets("Best CI CFD").Cells(i, j) meets certain criteria.
If WorkSheets("Best CI CFD").Cells(i, j) >= .9 Then G = G+1
If WorkSheets("Best CI CFD").Cells(i, j) from .8 to .9 Then Y = Y+1
If WorkSheets("Best CI CFD").Cells(i, j) < .8 Then R = R+1
When I MsgBox R and Y, the results seem reasonable, though I cannot be sure. At least they are both < 384
However, when I MsgBox G, I get something like 465, which is greater then the number of cells that contain data!
Here is the code. Like I said, I commented out the superfluous stuff (most of it at least):
Option Explicit
Private Sub CommandButton1_Click()
Dim i, j, nRow, nCol, G, Y, R, TotalRacks As Long
Dim xgg, xgy, xgr, xyg, xyy, xyr, xrg, xry, xrr As Long
Dim OverPredicted, MyError, Percent, WithInTenPercent As Double
Dim MyMax, ErrorSquare As Double
' *********************************************************************
' *********************************************************************
' Count # of times ISX is conservative or not
nRow = 25
nCol = 32
xgg = 0
xgy = 0
xgr = 0
xyg = 0
xyy = 0
xyr = 0
xrg = 0
xry = 0
xrr = 0
G = 0
Y = 0
R = 0
MyMax = 0
Percent = 0
OverPredicted = 0
WithInTenPercent = 0
ErrorSquare = 0
' Check # times CFD predicts Green and ISX predicts Green, Yellow, or Red
For i = 1 To nRow
For j = 1 To nCol
If Worksheets("Best CI CFD").Cells(i, j) >= 0.9 Then
If Worksheets("Best CI ISX").Cells(i, j) >= 0.9 Then
xgg = xgg + 1
ElseIf Worksheets("Best CI ISX").Cells(i, j) < 0.9 And Worksheets("Best CI ISX").Cells(i, j) > 0.8 Then
xgy = xgy + 1
ElseIf Worksheets("Best CI ISX").Cells(i, j) <= 0.8 And Worksheets("Best CI ISX").Cells(i, j) <> "" Then
xgr = xgr + 1
End If
G = G + 1
' ElseIf Worksheets("Best CI CFD").Cells(i, j) < 9 And Worksheets("Best CI CFD").Cells(i, j) > 0.8 Then
' If Worksheets("Best CI ISX").Cells(i, j) >= 0.9 Then
' xyg = xyg + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) < 0.9 And Worksheets("Best CI ISX").Cells(i, j) > 0.8 Then
' xyy = xyy + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) <= 0.8 And Worksheets("Best CI ISX").Cells(i, j) <> "" Then
' xyr = xyr + 1
' End If
'
' Y = Y + 1
'
' ElseIf Worksheets("Best CI CFD").Cells(i, j) <= 0.8 And Worksheets("Best CI CFD").Cells(i, j) <> "" Then
' If Worksheets("Best CI ISX").Cells(i, j) >= 0.9 Then
' xrg = xrg + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) < 0.9 And Worksheets("Best CI ISX").Cells(i, j) > 0.8 Then
' xry = xry + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) <= 0.8 And Worksheets("Best CI ISX").Cells(i, j) <> "" Then
' xrr = xrr + 1
' End If
'
' R = R + 1
'
End If
Next j
Next i
'' *********************************************************************
'' *********************************************************************
'' Count # times ISX Overpredicted; Is w/in 10 %; max error; etc
'
' For i = 1 To nRow
' For j = 1 To nCol
' If Worksheets("Best CI CFD").Cells(i, j) <> "" Then
'' Compute Difference: ISX_CI -CFD_CI
' MyError = Worksheets("Best CI ISX").Cells(i, j) - Worksheets("Best CI CFD").Cells(i, j)
'
'
' If MyError > 0 Then
' OverPredicted = OverPredicted + 1 'Overpredicted means ISX predicted better cooling
' End If 'performance than actual (i.e. CFD)
'
'' Compute % Error by dividing by actual (i.e. CFD)
' Percent = Percent + Abs(MyError) / (Worksheets("Best CI CFD").Cells(i, j) + 0.0000000001) 'Prevents a div by 0 error
'
' If Abs(MyError) > MyMax Then
' MyMax = Abs(MyError)
' End If
'' Count #times ISX is w/in 10 % of CFD
' If Abs(MyError) <= 0.1 Then
' WithInTenPercent = WithInTenPercent + 1
' End If
'
'' Calculate error-squared-sum for RMS calculation
' ErrorSquare = ErrorSquare + MyError ^ 2
' End If
' Next j
' Next i
'
'
' For i = 1 To nRow
' For j = 1 To nCol
' If Worksheets("Best CI CFD").Cells(i, j) <> "" Then
' TotalRacks = TotalRacks + 1
' End If
' Next j
' Next i
'
MsgBox G
End Sub
I have attached file.
I have done a CountIf worksheet function to see how many cells contain data in WorkSheets("Best CI CFD"). It is about 384.
Now, in my code, in the first block (the only part UN commented-out) I have a nested For Loop / Nested If statements that check to see whether
WorkSheets("Best CI CFD").Cells(i, j) meets certain criteria.
If WorkSheets("Best CI CFD").Cells(i, j) >= .9 Then G = G+1
If WorkSheets("Best CI CFD").Cells(i, j) from .8 to .9 Then Y = Y+1
If WorkSheets("Best CI CFD").Cells(i, j) < .8 Then R = R+1
When I MsgBox R and Y, the results seem reasonable, though I cannot be sure. At least they are both < 384
However, when I MsgBox G, I get something like 465, which is greater then the number of cells that contain data!
Here is the code. Like I said, I commented out the superfluous stuff (most of it at least):
Option Explicit
Private Sub CommandButton1_Click()
Dim i, j, nRow, nCol, G, Y, R, TotalRacks As Long
Dim xgg, xgy, xgr, xyg, xyy, xyr, xrg, xry, xrr As Long
Dim OverPredicted, MyError, Percent, WithInTenPercent As Double
Dim MyMax, ErrorSquare As Double
' *********************************************************************
' *********************************************************************
' Count # of times ISX is conservative or not
nRow = 25
nCol = 32
xgg = 0
xgy = 0
xgr = 0
xyg = 0
xyy = 0
xyr = 0
xrg = 0
xry = 0
xrr = 0
G = 0
Y = 0
R = 0
MyMax = 0
Percent = 0
OverPredicted = 0
WithInTenPercent = 0
ErrorSquare = 0
' Check # times CFD predicts Green and ISX predicts Green, Yellow, or Red
For i = 1 To nRow
For j = 1 To nCol
If Worksheets("Best CI CFD").Cells(i, j) >= 0.9 Then
If Worksheets("Best CI ISX").Cells(i, j) >= 0.9 Then
xgg = xgg + 1
ElseIf Worksheets("Best CI ISX").Cells(i, j) < 0.9 And Worksheets("Best CI ISX").Cells(i, j) > 0.8 Then
xgy = xgy + 1
ElseIf Worksheets("Best CI ISX").Cells(i, j) <= 0.8 And Worksheets("Best CI ISX").Cells(i, j) <> "" Then
xgr = xgr + 1
End If
G = G + 1
' ElseIf Worksheets("Best CI CFD").Cells(i, j) < 9 And Worksheets("Best CI CFD").Cells(i, j) > 0.8 Then
' If Worksheets("Best CI ISX").Cells(i, j) >= 0.9 Then
' xyg = xyg + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) < 0.9 And Worksheets("Best CI ISX").Cells(i, j) > 0.8 Then
' xyy = xyy + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) <= 0.8 And Worksheets("Best CI ISX").Cells(i, j) <> "" Then
' xyr = xyr + 1
' End If
'
' Y = Y + 1
'
' ElseIf Worksheets("Best CI CFD").Cells(i, j) <= 0.8 And Worksheets("Best CI CFD").Cells(i, j) <> "" Then
' If Worksheets("Best CI ISX").Cells(i, j) >= 0.9 Then
' xrg = xrg + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) < 0.9 And Worksheets("Best CI ISX").Cells(i, j) > 0.8 Then
' xry = xry + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) <= 0.8 And Worksheets("Best CI ISX").Cells(i, j) <> "" Then
' xrr = xrr + 1
' End If
'
' R = R + 1
'
End If
Next j
Next i
'' *********************************************************************
'' *********************************************************************
'' Count # times ISX Overpredicted; Is w/in 10 %; max error; etc
'
' For i = 1 To nRow
' For j = 1 To nCol
' If Worksheets("Best CI CFD").Cells(i, j) <> "" Then
'' Compute Difference: ISX_CI -CFD_CI
' MyError = Worksheets("Best CI ISX").Cells(i, j) - Worksheets("Best CI CFD").Cells(i, j)
'
'
' If MyError > 0 Then
' OverPredicted = OverPredicted + 1 'Overpredicted means ISX predicted better cooling
' End If 'performance than actual (i.e. CFD)
'
'' Compute % Error by dividing by actual (i.e. CFD)
' Percent = Percent + Abs(MyError) / (Worksheets("Best CI CFD").Cells(i, j) + 0.0000000001) 'Prevents a div by 0 error
'
' If Abs(MyError) > MyMax Then
' MyMax = Abs(MyError)
' End If
'' Count #times ISX is w/in 10 % of CFD
' If Abs(MyError) <= 0.1 Then
' WithInTenPercent = WithInTenPercent + 1
' End If
'
'' Calculate error-squared-sum for RMS calculation
' ErrorSquare = ErrorSquare + MyError ^ 2
' End If
' Next j
' Next i
'
'
' For i = 1 To nRow
' For j = 1 To nCol
' If Worksheets("Best CI CFD").Cells(i, j) <> "" Then
' TotalRacks = TotalRacks + 1
' End If
' Next j
' Next i
'
MsgBox G
End Sub