diff options
| -rw-r--r-- | calc.go | 85 | ||||
| -rw-r--r-- | calc_test.go | 26 |
2 files changed, 109 insertions, 2 deletions
@@ -69,7 +69,7 @@ const ( searchModeDescBinary = -2 maxFinancialIterations = 128 - financialPercision = 1.0e-08 + financialPrecision = 1.0e-08 // Date and time format regular expressions monthRe = `((jan|january)|(feb|february)|(mar|march)|(apr|april)|(may)|(jun|june)|(jul|july)|(aug|august)|(sep|september)|(oct|october)|(nov|november)|(dec|december))` df1 = `(([0-9])+)/(([0-9])+)/(([0-9])+)` @@ -424,6 +424,8 @@ type formulaFuncs struct { // FACT // FACTDOUBLE // FALSE +// F.DIST +// FDIST // FIND // FINDB // F.INV @@ -7056,6 +7058,85 @@ func (fn *formulaFuncs) EXPONDIST(argsList *list.List) formulaArg { return newNumberFormulaArg(lambda.Number * math.Exp(-lambda.Number*x.Number)) } +// FdotDIST function calculates the Probability Density Function or the +// Cumulative Distribution Function for the F Distribution. This function is +// frequently used used to measure the degree of diversity between two data +// sets. The syntax of the function is: +// +// F.DIST(x,deg_freedom1,deg_freedom2,cumulative) +// +func (fn *formulaFuncs) FdotDIST(argsList *list.List) formulaArg { + if argsList.Len() != 4 { + return newErrorFormulaArg(formulaErrorVALUE, "F.DIST requires 4 arguments") + } + var x, deg1, deg2, cumulative formulaArg + if x = argsList.Front().Value.(formulaArg).ToNumber(); x.Type != ArgNumber { + return x + } + if deg1 = argsList.Front().Next().Value.(formulaArg).ToNumber(); deg1.Type != ArgNumber { + return deg1 + } + if deg2 = argsList.Front().Next().Next().Value.(formulaArg).ToNumber(); deg2.Type != ArgNumber { + return deg2 + } + if cumulative = argsList.Back().Value.(formulaArg).ToBool(); cumulative.Type == ArgError { + return cumulative + } + if x.Number < 0 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + maxDeg := math.Pow10(10) + if deg1.Number < 1 || deg1.Number >= maxDeg { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + if deg2.Number < 1 || deg2.Number >= maxDeg { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + if cumulative.Number == 1 { + return newNumberFormulaArg(1 - getBetaDist(deg2.Number/(deg2.Number+deg1.Number*x.Number), deg2.Number/2, deg1.Number/2)) + } + return newNumberFormulaArg(math.Gamma((deg2.Number+deg1.Number)/2) / (math.Gamma(deg1.Number/2) * math.Gamma(deg2.Number/2)) * math.Pow(deg1.Number/deg2.Number, deg1.Number/2) * (math.Pow(x.Number, (deg1.Number-2)/2) / math.Pow(1+(deg1.Number/deg2.Number)*x.Number, (deg1.Number+deg2.Number)/2))) +} + +// FDIST function calculates the (right-tailed) F Probability Distribution, +// which measures the degree of diversity between two data sets. The syntax +// of the function is: +// +// FDIST(x,deg_freedom1,deg_freedom2) +// +func (fn *formulaFuncs) FDIST(argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, "FDIST requires 3 arguments") + } + var x, deg1, deg2 formulaArg + if x = argsList.Front().Value.(formulaArg).ToNumber(); x.Type != ArgNumber { + return x + } + if deg1 = argsList.Front().Next().Value.(formulaArg).ToNumber(); deg1.Type != ArgNumber { + return deg1 + } + if deg2 = argsList.Back().Value.(formulaArg).ToNumber(); deg2.Type != ArgNumber { + return deg2 + } + if x.Number < 0 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + maxDeg := math.Pow10(10) + if deg1.Number < 1 || deg1.Number >= maxDeg { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + if deg2.Number < 1 || deg2.Number >= maxDeg { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + args := list.New() + args.PushBack(newNumberFormulaArg(deg1.Number * x.Number / (deg1.Number*x.Number + deg2.Number))) + args.PushBack(newNumberFormulaArg(0.5 * deg1.Number)) + args.PushBack(newNumberFormulaArg(0.5 * deg2.Number)) + args.PushBack(newNumberFormulaArg(0)) + args.PushBack(newNumberFormulaArg(1)) + return newNumberFormulaArg(1 - fn.BETADIST(args).Number) +} + // prepareFinvArgs checking and prepare arguments for the formula function // F.INV, F.INV.RT and FINV. func (fn *formulaFuncs) prepareFinvArgs(name string, argsList *list.List) formulaArg { @@ -12982,7 +13063,7 @@ func (fn *formulaFuncs) IRR(argsList *list.List) formulaArg { if fMid <= 0 { rtb = xMid } - if math.Abs(fMid) < financialPercision || math.Abs(dx) < financialPercision { + if math.Abs(fMid) < financialPrecision || math.Abs(dx) < financialPrecision { break } } diff --git a/calc_test.go b/calc_test.go index 6708cdb..1b59e78 100644 --- a/calc_test.go +++ b/calc_test.go @@ -935,6 +935,11 @@ func TestCalcCellValue(t *testing.T) { "=EXPONDIST(0.5,1,TRUE)": "0.393469340287367", "=EXPONDIST(0.5,1,FALSE)": "0.606530659712633", "=EXPONDIST(2,1,TRUE)": "0.864664716763387", + // FDIST + "=FDIST(5,1,2)": "0.154845745271483", + // F.DIST + "=F.DIST(1,2,5,TRUE)": "0.568798849628308", + "=F.DIST(1,2,5,FALSE)": "0.308000821694066", // F.INV "=F.INV(0.9,2,5)": "3.77971607877395", // FINV @@ -2631,6 +2636,27 @@ func TestCalcCellValue(t *testing.T) { "=EXPONDIST(0,1,\"\")": "strconv.ParseBool: parsing \"\": invalid syntax", "=EXPONDIST(-1,1,TRUE)": "#NUM!", "=EXPONDIST(1,0,TRUE)": "#NUM!", + // FDIST + "=FDIST()": "FDIST requires 3 arguments", + "=FDIST(\"\",1,2)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=FDIST(5,\"\",2)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=FDIST(5,1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=FDIST(-1,1,2)": "#NUM!", + "=FDIST(5,0,2)": "#NUM!", + "=FDIST(5,10000000000,2)": "#NUM!", + "=FDIST(5,1,0)": "#NUM!", + "=FDIST(5,1,10000000000)": "#NUM!", + // F.DIST + "=F.DIST()": "F.DIST requires 4 arguments", + "=F.DIST(\"\",2,5,TRUE)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=F.DIST(1,\"\",5,TRUE)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=F.DIST(1,2,\"\",TRUE)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=F.DIST(1,2,5,\"\")": "strconv.ParseBool: parsing \"\": invalid syntax", + "=F.DIST(-1,1,2,TRUE)": "#NUM!", + "=F.DIST(5,0,2,TRUE)": "#NUM!", + "=F.DIST(5,10000000000,2,TRUE)": "#NUM!", + "=F.DIST(5,1,0,TRUE)": "#NUM!", + "=F.DIST(5,1,10000000000,TRUE)": "#NUM!", // F.INV "=F.INV()": "F.INV requires 3 arguments", "=F.INV(\"\",1,2)": "strconv.ParseFloat: parsing \"\": invalid syntax", |