I'm trying to think of a simple way to convert all values in an excel database temporarily to do a calculation with a conversion for one of the values. ie:
(The last cell is an average of A-E, red is an invalid average because..)
<5 would need to be converted to .0025 and <3 to .0015 but only in the background, not changing the actual visible cells to the end user. In VBA I was thinking if having variables for each column then just placing the coded average into the final column like so:
There's gotta be some better method to do this like a
(The last cell is an average of A-E, red is an invalid average because..)
<5 would need to be converted to .0025 and <3 to .0015 but only in the background, not changing the actual visible cells to the end user. In VBA I was thinking if having variables for each column then just placing the coded average into the final column like so:
Code:
For i = 1 to 5 (or length of database)
If Range("A" & CInt(i)).Value = "<5" Then
ColAVar = 0.0025
ElseIf Range("A" & CInt(i)).Value = "<3" Then
ColAVar = 0.0015
Else
ColAVar = Range("A" & CInt(i)).Value
End If
If Range("B" & CInt(i)).Value = "<5" Then
ColBVar = 0.0025
ElseIf Range("B" & CInt(i)).Value = "<3" Then
ColBVar = 0.0015
Else
ColBVar = Range("B" & CInt(i)).Value
End If
[B]ETC..........[/B]
End If
Range("F" & CInt(i)).Value = (ColAVar+ColBVar etc.) / 5
Next iThere's gotta be some better method to do this like a
Code:
Range("F" & CInt(i)).Value = Average(Range("A" & CInt(i) & ":E" & CInt(i)).Replace(<5,0.0025:<3,0.0015))
