I figured it out. Although I doubt anyone else needs it, here's the open office macro code drop. JavaScript used to crash, so I had to use basic. Ugh.
Usage: DROPRATE(kills, drops, confidence)
e.g. =DROPRATE(100, 48, 95)
Syntax highlighting is slightly off because the forum doesn't seem to know vbasic syntax.
Language: basic
Dim fun as Object
Function DropRate_PValue(p, n, m)
Dim expected, offset as double
Dim high, low as double
Dim pval as double
Dim args(1 to 4) as Variant
Dim rargs(1 to 2) as Variant
expected = p*n
offset = abs(m - expected)
pval = 0
high = expected + offset
if (high <= n) then
rargs(1) = high
rargs(2) = 1
high = fun.CallFunction("ceiling", rargs() )
args(1) = n
args(2) = p
args(3) = high
args(4) = n
pval = pval + fun.CallFunction( "B", args() )
end if
low = expected - offset
if (low >= 0) then
rargs(1) = low
rargs(2) = 1
low = fun.CallFunction("floor", rargs() )
args(1) = n
args(2) = p
args(3) = 0
args(4) = low
pval = pval + fun.CallFunction( "B", args() )
end if
DropRate_PValue = pval
End Function
Function DropRate_BinarySearch(dir, low, high, n, m, target, iter)
Dim test, pval as double
test = (high+low)/2.0
if (high - low < 0.00001) then
DropRate_BinarySearch = test
Exit Function
end if
if (iter > 20) then
DropRate_BinarySearch = -1
Exit Function
end if
pval = DropRate_PValue(test, n, m)
if (dir * pval > dir * target) then
DropRate_BinarySearch = DropRate_BinarySearch(dir, test, high, n, m, target, iter+1)
else
DropRate_BinarySearch = DropRate_BinarySearch(dir, low, test, n, m, target, iter+1)
end if
End Function
Function DropRate(n, m, confidence)
Dim expected as double
Dim a, b as double
fun = createUnoService( "com.sun.star.sheet.FunctionAccess" )
if (m > n) then
DropRate = "#ERR n > m"
Exit Function
end if
expected = m / n
confidence = (100-confidence) / 100
a = DropRate_BinarySearch(-1, 0.0, expected, n, m, confidence, 0)
b = DropRate_BinarySearch( 1, expected, 1.0, n, m, confidence, 0)
DropRate = Format(a*100, "0.00") & "% - " & Format(b*100, "0.00") & "%"
End Function
Thanks for your help, everyone!
