diff options
| -rw-r--r-- | calc.go | 49 | ||||
| -rw-r--r-- | calc_test.go | 20 | ||||
| -rw-r--r-- | cell.go | 7 |
3 files changed, 74 insertions, 2 deletions
@@ -505,6 +505,8 @@ type formulaFuncs struct { // LN // LOG // LOG10 +// LOGINV +// LOGNORM.INV // LOOKUP // LOWER // MATCH @@ -6424,6 +6426,53 @@ func (fn *formulaFuncs) FINV(argsList *list.List) formulaArg { return newNumberFormulaArg((1/calcBetainv(1-(1-probability.Number), d2.Number/2, d1.Number/2, 0, 1) - 1) * (d2.Number / d1.Number)) } +// LOGINV function calculates the inverse of the Cumulative Log-Normal +// Distribution Function of x, for a supplied probability. The syntax of the +// function is: +// +// LOGINV(probability,mean,standard_dev) +// +func (fn *formulaFuncs) LOGINV(argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, "LOGINV requires 3 arguments") + } + var probability, mean, stdDev formulaArg + if probability = argsList.Front().Value.(formulaArg).ToNumber(); probability.Type != ArgNumber { + return probability + } + if mean = argsList.Front().Next().Value.(formulaArg).ToNumber(); mean.Type != ArgNumber { + return mean + } + if stdDev = argsList.Back().Value.(formulaArg).ToNumber(); stdDev.Type != ArgNumber { + return stdDev + } + if probability.Number <= 0 || probability.Number >= 1 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + if stdDev.Number <= 0 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + args := list.New() + args.PushBack(probability) + args.PushBack(newNumberFormulaArg(0)) + args.PushBack(newNumberFormulaArg(1)) + norminv := fn.NORMINV(args) + return newNumberFormulaArg(math.Exp(mean.Number + stdDev.Number*norminv.Number)) +} + +// LOGNORMdotINV function calculates the inverse of the Cumulative Log-Normal +// Distribution Function of x, for a supplied probability. The syntax of the +// function is: +// +// LOGNORM.INV(probability,mean,standard_dev) +// +func (fn *formulaFuncs) LOGNORMdotINV(argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, "LOGNORM.INV requires 3 arguments") + } + return fn.LOGINV(argsList) +} + // NORMdotDIST function calculates the Normal Probability Density Function or // the Cumulative Normal Distribution. Function for a supplied set of // parameters. The syntax of the function is: diff --git a/calc_test.go b/calc_test.go index 6407685..8f1c1e5 100644 --- a/calc_test.go +++ b/calc_test.go @@ -890,6 +890,10 @@ func TestCalcCellValue(t *testing.T) { "=F.INV.RT(0.5,4,8)": "0.914645355977072", "=F.INV.RT(0.1,79,86)": "1.32646097270444", "=F.INV.RT(1,40,5)": "0", + // LOGINV + "=LOGINV(0.3,2,0.2)": "6.6533460753367", + // LOGINV + "=LOGNORM.INV(0.3,2,0.2)": "6.6533460753367", // NORM.DIST "=NORM.DIST(0.8,1,0.3,TRUE)": "0.252492537546923", "=NORM.DIST(50,40,20,FALSE)": "0.017603266338215", @@ -2487,6 +2491,22 @@ func TestCalcCellValue(t *testing.T) { "=F.INV.RT(0,1,2)": "#NUM!", "=F.INV.RT(0.2,0.5,2)": "#NUM!", "=F.INV.RT(0.2,1,0.5)": "#NUM!", + // LOGINV + "=LOGINV()": "LOGINV requires 3 arguments", + "=LOGINV(\"\",2,0.2)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=LOGINV(0.3,\"\",0.2)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=LOGINV(0.3,2,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=LOGINV(0,2,0.2)": "#NUM!", + "=LOGINV(1,2,0.2)": "#NUM!", + "=LOGINV(0.3,2,0)": "#NUM!", + // LOGNORM.INV + "=LOGNORM.INV()": "LOGNORM.INV requires 3 arguments", + "=LOGNORM.INV(\"\",2,0.2)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=LOGNORM.INV(0.3,\"\",0.2)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=LOGNORM.INV(0.3,2,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=LOGNORM.INV(0,2,0.2)": "#NUM!", + "=LOGNORM.INV(1,2,0.2)": "#NUM!", + "=LOGNORM.INV(0.3,2,0)": "#NUM!", // NORM.DIST "=NORM.DIST()": "NORM.DIST requires 4 arguments", // NORMDIST @@ -673,8 +673,11 @@ type HyperlinkOpts struct { // SetCellHyperLink provides a function to set cell hyperlink by given // worksheet name and link URL address. LinkType defines two types of // hyperlink "External" for web site or "Location" for moving to one of cell -// in this workbook. Maximum limit hyperlinks in a worksheet is 65530. The -// below is example for external link. +// in this workbook. Maximum limit hyperlinks in a worksheet is 65530. This +// function is only used to set the hyperlink of the cell and doesn't affect +// the value of the cell. If you need to set the value of the cell, please use +// the other functions such as `SetCellStyle` or `SetSheetRow`. The below is +// example for external link. // // if err := f.SetCellHyperLink("Sheet1", "A3", // "https://github.com/xuri/excelize", "External"); err != nil { |