microbebrew
05-07-2015, 08:50 AM
Hello!
Long time reader, first time poster. I have been able to write some very time-saving functions and macros from advice in this forum, and I really appreciate it. Now, I have a question that I don't know how to search for, and I hope someone can give me a hand.
I wrote a (very inelegant!) function to calculate the log difference plus/minus standard deviation for sets of triplicate samples. As it is, the function works perfectly, as long as I have exactly six arguments for it. I don't know enough about the terminology to search for exactly what I am trying to do. I would like to set up my function to accept maybe 5 arguments, or maybe 20, depending on the situation. I just don't know how to make it more dynamic like, say, the built in AVERAGE function: =Average(number1, [number2],...)
Like I said, the logic and the math all work the way they are supposed to. If anyone could point me in the right direction, I would really appreciate it!
Here is my existing function:
Function logred(U1, U2, U3, T1, T2, T3)
Dim logU1
Dim logU2
Dim logU3
Dim logT1
Dim logT2
Dim logT3
Dim geoU
Dim geoT
Dim varU
Dim varT
Dim SD
Dim logdiff
Dim rd
If T1 = "<10" And T2 <> "<10" And T3 <> "<10" Then
T1 = 10
ElseIf T1 <> "<10" And T2 = "<10" And T3 <> "<10" Then
T2 = 10
ElseIf T1 <> "<10" And T2 <> "<10" And T3 = "<10" Then
T3 = 10
ElseIf T1 = "<10" And T2 = "<10" And T3 <> "<10" Then
T1 = 10
T2 = 10
ElseIf T1 = "<10" And T2 <> "<10" And T3 = "<10" Then
T1 = 10
T3 = 10
ElseIf T1 <> "<10" And T2 = "<10" And T3 = "<10" Then
T2 = 10
T3 = 10
ElseIf T1 = "<10" And T2 = "<10" And T3 = "<10" Then
T1 = 10
T2 = 10
T3 = 10
End If
If T1 = "<1" And T2 <> "<1" And T3 <> "<1" Then
T1 = 1
ElseIf T1 <> "<1" And T2 = "<1" And T3 <> "<1" Then
T2 = 1
ElseIf T1 <> "<1" And T2 <> "<1" And T3 = "<1" Then
T3 = 1
ElseIf T1 = "<1" And T2 = "<1" And T3 <> "<1" Then
T1 = 1
T2 = 1
ElseIf T1 = "<1" And T2 <> "<1" And T3 = "<1" Then
T1 = 1
T3 = 1
ElseIf T1 <> "<1" And T2 = "<1" And T3 = "<1" Then
T2 = 1
T3 = 1
ElseIf T1 = "<1" And T2 = "<1" And T3 = "<1" Then
T1 = 1
T2 = 1
T3 = 1
End If
logU1 = Application.WorksheetFunction.Log(U1)
logU2 = Application.WorksheetFunction.Log(U2)
logU3 = Application.WorksheetFunction.Log(U3)
logT1 = Application.WorksheetFunction.Log(T1)
logT2 = Application.WorksheetFunction.Log(T2)
logT3 = Application.WorksheetFunction.Log(T3)
If logT1 = 0 Then logT1 = 0.0001
If logT2 = 0 Then logT2 = 0.0001
If logT3 = 0 Then logT3 = 0.0001
geoU = Application.WorksheetFunction.GeoMean(logU1, logU2, logU3)
geoT = Application.WorksheetFunction.GeoMean(logT1, logT2, logT3)
varU = Application.WorksheetFunction.VarP(logU1, logU2, logU3)
varT = Application.WorksheetFunction.VarP(logT1, logT2, logT3)
SD = Sqr((varU / 3) + (varT / 3))
logdiff = geoU - geoT
rd = 3 - Len(Int(logdiff))
logdiff = Application.WorksheetFunction.Round(logdiff, rd)
SD = Application.WorksheetFunction.Round(SD, rd)
logred = Application.WorksheetFunction.Text(logdiff & " ± " & SD, "#,##")
End Function
Thanks in advance for any advice!
Mat
Long time reader, first time poster. I have been able to write some very time-saving functions and macros from advice in this forum, and I really appreciate it. Now, I have a question that I don't know how to search for, and I hope someone can give me a hand.
I wrote a (very inelegant!) function to calculate the log difference plus/minus standard deviation for sets of triplicate samples. As it is, the function works perfectly, as long as I have exactly six arguments for it. I don't know enough about the terminology to search for exactly what I am trying to do. I would like to set up my function to accept maybe 5 arguments, or maybe 20, depending on the situation. I just don't know how to make it more dynamic like, say, the built in AVERAGE function: =Average(number1, [number2],...)
Like I said, the logic and the math all work the way they are supposed to. If anyone could point me in the right direction, I would really appreciate it!
Here is my existing function:
Function logred(U1, U2, U3, T1, T2, T3)
Dim logU1
Dim logU2
Dim logU3
Dim logT1
Dim logT2
Dim logT3
Dim geoU
Dim geoT
Dim varU
Dim varT
Dim SD
Dim logdiff
Dim rd
If T1 = "<10" And T2 <> "<10" And T3 <> "<10" Then
T1 = 10
ElseIf T1 <> "<10" And T2 = "<10" And T3 <> "<10" Then
T2 = 10
ElseIf T1 <> "<10" And T2 <> "<10" And T3 = "<10" Then
T3 = 10
ElseIf T1 = "<10" And T2 = "<10" And T3 <> "<10" Then
T1 = 10
T2 = 10
ElseIf T1 = "<10" And T2 <> "<10" And T3 = "<10" Then
T1 = 10
T3 = 10
ElseIf T1 <> "<10" And T2 = "<10" And T3 = "<10" Then
T2 = 10
T3 = 10
ElseIf T1 = "<10" And T2 = "<10" And T3 = "<10" Then
T1 = 10
T2 = 10
T3 = 10
End If
If T1 = "<1" And T2 <> "<1" And T3 <> "<1" Then
T1 = 1
ElseIf T1 <> "<1" And T2 = "<1" And T3 <> "<1" Then
T2 = 1
ElseIf T1 <> "<1" And T2 <> "<1" And T3 = "<1" Then
T3 = 1
ElseIf T1 = "<1" And T2 = "<1" And T3 <> "<1" Then
T1 = 1
T2 = 1
ElseIf T1 = "<1" And T2 <> "<1" And T3 = "<1" Then
T1 = 1
T3 = 1
ElseIf T1 <> "<1" And T2 = "<1" And T3 = "<1" Then
T2 = 1
T3 = 1
ElseIf T1 = "<1" And T2 = "<1" And T3 = "<1" Then
T1 = 1
T2 = 1
T3 = 1
End If
logU1 = Application.WorksheetFunction.Log(U1)
logU2 = Application.WorksheetFunction.Log(U2)
logU3 = Application.WorksheetFunction.Log(U3)
logT1 = Application.WorksheetFunction.Log(T1)
logT2 = Application.WorksheetFunction.Log(T2)
logT3 = Application.WorksheetFunction.Log(T3)
If logT1 = 0 Then logT1 = 0.0001
If logT2 = 0 Then logT2 = 0.0001
If logT3 = 0 Then logT3 = 0.0001
geoU = Application.WorksheetFunction.GeoMean(logU1, logU2, logU3)
geoT = Application.WorksheetFunction.GeoMean(logT1, logT2, logT3)
varU = Application.WorksheetFunction.VarP(logU1, logU2, logU3)
varT = Application.WorksheetFunction.VarP(logT1, logT2, logT3)
SD = Sqr((varU / 3) + (varT / 3))
logdiff = geoU - geoT
rd = 3 - Len(Int(logdiff))
logdiff = Application.WorksheetFunction.Round(logdiff, rd)
SD = Application.WorksheetFunction.Round(SD, rd)
logred = Application.WorksheetFunction.Text(logdiff & " ± " & SD, "#,##")
End Function
Thanks in advance for any advice!
Mat