Function Transpose(stroldname As String, strNewName As String, response As Variant, outputNumber As Single, blnNotMissing As Boolean, intTranspose As Long, intMW As Long, mw As Single, MVOL As Single, minData As Single, MaxData As Single, minMW As Single, maxMW As Single, MinVol As Single, MaxVol As Single) As Boolean 'Coded Sept 2017-March 2018 by James A. Quinn 'This code has been placed in the public domain and has a standard Open Source license. (Free to use, Use at your own risk.) 'stroldname = the old field name 'strNeName = the name of the transformed field 'response = the value to be transformed 'outputNumber = the transformed value 'blnNotMissing = false if response = null ' intTranspose - the transposition used. 1 = response, 2 = 1/response, 3 = log(response), 4 = 1/Log(response), 5 = sqrt(response), 6= 1/sqrt(response) ' 7= response^2, 8 = 1/response^2, 9 = logit(response), 10 = 1/logit(response) ' intmw = a number indicating whether the molecules should be multiplied or divided by molecular weight or molecular volume or both. ' mw = molecular weight ' mvol = van der Waals molecular volume ' mindata = minimum response value in the whole dataset ' maxdata = maximum response value in the whole dataset ' minMW, maxMW - minimum and maximum molecular weights in the whole dataset ' minVol, maxVol - minimum and maximum molecular volumes in the whole dataset Dim diff As Single On Error GoTo TransposeErr Dim tmpMindata As Single, tmpMaxData As Single If blnNotMissing = True Then Select Case intMW Case 1 outputNumber = response 'no modification by mw or mvol tmpMindata = minData tmpMaxData = MaxData strNewName = stroldname Case 2 If mw <> 0 Then outputNumber = response / mw tmpMindata = minData / minMW tmpMaxData = MaxData / maxMW If tmpMaxData < tmpMindata Then tmpMaxData = tmpMindata tmpMindata = MaxData / maxMW End If strNewName = stroldname & "_divided by mw" Else blnNotMissing = False End If Case 3 outputNumber = response * mw tmpMindata = minData * minMW tmpMaxData = MaxData * maxMW strNewName = stroldname & "_x_mw" Case 4 If MVOL <> 0 Then outputNumber = response / MVOL tmpMindata = minData / MinVol tmpMaxData = MaxData / MaxVol If tmpMaxData < tmpMindata Then tmpMaxData = tmpMindata tmpMindata = MaxData / MaxVol End If strNewName = stroldname & "_divided_by_mvol" Else blnNotMissing = False End If Case 5 outputNumber = response * MVOL tmpMindata = minData * MinVol tmpMaxData = MaxData * MaxVol strNewName = stroldname & " x mvol" Case 6 If MVOL <> 0 And mw <> 0 Then outputNumber = response * (mw / MVOL) tmpMindata = minData / (minMW / MinVol) tmpMaxData = MaxData / (maxMW / MaxVol) If tmpMaxData < tmpMindata Then tmpMaxData = tmpMindata tmpMindata = MaxData / (maxMW / MaxVol) End If strNewName = stroldname & "_x_(mw_divided_by_mvol)" Else blnNotMissing = False Exit Function End If Case 7 If MVOL <> 0 And mw <> 0 Then outputNumber = response * (MVOL / mw) tmpMindata = minData / (MinVol / minMW) tmpMaxData = MaxData / (MaxVol / maxMW) If tmpMaxData < tmpMindata Then tmpMaxData = tmpMindata tmpMindata = MaxData / (MaxVol / maxMW) End If strNewName = stroldname & "_x_(mvol_divided_by_mw)" Else blnNotMissing = False Exit Function End If End Select Select Case intTranspose Case 1 outputNumber = outputNumber Case 2 If outputNumber <> 0 Then outputNumber = 1 / outputNumber Else outputNumber = 0 End If strNewName = "1_divided_by_" & strNewName Case 3 If tmpMindata <= 0 Then diff = tmpMaxData - tmpMindata outputNumber = (outputNumber + (diff * 0.00001)) - tmpMindata End If If outputNumber > 0 Then outputNumber = Log(outputNumber) / Log(10) Else blnNotMissing = False End If strNewName = "log10_of_" & strNewName Case 4 If tmpMindata <= 0 Then diff = tmpMaxData - tmpMindata outputNumber = (outputNumber + (diff * 0.00001)) - tmpMindata End If If outputNumber > 0 Then outputNumber = 1 / (Log(outputNumber) / Log(10)) strNewName = "1_divided_by_log10_of_" & strNewName Else blnNotMissing = False End If Case 5 If outputNumber > 0 Then outputNumber = Sqr(outputNumber) strNewName = "Squareroot_of_" & strNewName Else blnNotMissing = False End If Case 6 If tmpMindata <= 0 Then diff = tmpMaxData - tmpMindata outputNumber = (outputNumber + (diff * 0.00001)) - tmpMindata End If If outputNumber > 0 Then outputNumber = 1 / (Sqr(outputNumber)) strNewName = "1_divided_by_Squareroot_of_" & strNewName Else blnNotMissing = False End If Case 7 outputNumber = outputNumber * outputNumber strNewName = strNewName & "_squared" Case 8 If outputNumber <> 0 Then outputNumber = 1 / (outputNumber * outputNumber) strNewName = "1_divided_by_" & strNewName & "_squared" Else blnNotMissing = False End If Case 9 If tmpMindata >= 0 And tmpMindata < 0.1 And tmpMaxData <= 1 And tmpMaxData > 0.9 Then tmpMindata = 0 tmpMaxData = 1 ElseIf tmpMindata >= 0 And tmpMindata < 10 And tmpMaxData <= 100 And tmpMaxData > 90 Then tmpMindata = 0 tmpMaxData = 100 End If diff = tmpMaxData - tmpMindata If diff = 0 Then blnNotMissing = False Else If tmpMindata < 0 Then outputNumber = outputNumber - tmpMindata End If outputNumber = outputNumber / diff If outputNumber <> 1 Then outputNumber = Log(outputNumber / (1 - outputNumber)) strNewName = "logit_of_" & strNewName Else blnNotMissing = False End If End If Case 10 If tmpMindata >= 0 And tmpMindata < 0.1 And tmpMaxData <= 1 And tmpMaxData > 0.9 Then tmpMindata = 0 tmpMaxData = 1 ElseIf tmpMindata >= 0 And tmpMindata < 10 And tmpMaxData <= 100 And tmpMaxData > 90 Then tmpMindata = 0 tmpMaxData = 100 End If diff = tmpMaxData - tmpMindata If diff = 0 Then blnNotMissing = False Else If tmpMindata < 0 Then outputNumber = outputNumber - tmpMindata End If outputNumber = outputNumber / diff If outputNumber = 0.5 Then outputNumber = 0 ElseIf outputNumber <> 1 Then outputNumber = 1 / (Log(outputNumber / (1 - outputNumber))) strNewName = "1_divided_by_logit_of_" & strNewName Else blnNotMissing = False End If End If End Select End If If blnNotMissing = True Then Transpose = True Else Transpose = False End If Exit Function TransposeErr: Transpose = False ' MsgBox Err.Description blnNotMissing = False End Function