diff options
| -rw-r--r-- | calc.go | 105 | ||||
| -rw-r--r-- | calc_test.go | 32 |
2 files changed, 137 insertions, 0 deletions
@@ -498,12 +498,15 @@ type formulaFuncs struct { // PRICEDISC // PRODUCT // PROPER +// PV // QUARTILE // QUARTILE.INC // QUOTIENT // RADIANS // RAND // RANDBETWEEN +// RANK +// RANK.EQ // REPLACE // REPLACEB // REPT @@ -5700,6 +5703,63 @@ func (fn *formulaFuncs) QUARTILEdotINC(argsList *list.List) formulaArg { return fn.QUARTILE(argsList) } +// rank is an implementation of the formula functions RANK and RANK.EQ. +func (fn *formulaFuncs) rank(name string, argsList *list.List) formulaArg { + if argsList.Len() < 2 { + return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires at least 2 arguments", name)) + } + if argsList.Len() > 3 { + return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires at most 3 arguments", name)) + } + num := argsList.Front().Value.(formulaArg).ToNumber() + if num.Type != ArgNumber { + return num + } + arr := []float64{} + for _, arg := range argsList.Front().Next().Value.(formulaArg).ToList() { + n := arg.ToNumber() + if n.Type == ArgNumber { + arr = append(arr, n.Number) + } + } + sort.Float64s(arr) + order := newNumberFormulaArg(0) + if argsList.Len() == 3 { + if order = argsList.Back().Value.(formulaArg).ToNumber(); order.Type != ArgNumber { + return order + } + } + if order.Number == 0 { + sort.Sort(sort.Reverse(sort.Float64Slice(arr))) + } + for idx, n := range arr { + if num.Number == n { + return newNumberFormulaArg(float64(idx + 1)) + } + } + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) +} + +// RANK.EQ function returns the statistical rank of a given value, within a +// supplied array of values. If there are duplicate values in the list, these +// are given the same rank. The syntax of the function is: +// +// RANK.EQ(number,ref,[order]) +// +func (fn *formulaFuncs) RANKdotEQ(argsList *list.List) formulaArg { + return fn.rank("RANK.EQ", argsList) +} + +// RANK function returns the statistical rank of a given value, within a +// supplied array of values. If there are duplicate values in the list, these +// are given the same rank. The syntax of the function is: +// +// RANK(number,ref,[order]) +// +func (fn *formulaFuncs) RANK(argsList *list.List) formulaArg { + return fn.rank("RANK", argsList) +} + // SKEW function calculates the skewness of the distribution of a supplied set // of values. The syntax of the function is: // @@ -9863,6 +9923,51 @@ func (fn *formulaFuncs) PRICEDISC(argsList *list.List) formulaArg { return newNumberFormulaArg(redemption.Number * (1 - discount.Number*frac.Number)) } +// PV function calculates the Present Value of an investment, based on a +// series of future payments. The syntax of the function is: +// +// PV(rate,nper,pmt,[fv],[type]) +// +func (fn *formulaFuncs) PV(argsList *list.List) formulaArg { + if argsList.Len() < 3 { + return newErrorFormulaArg(formulaErrorVALUE, "PV requires at least 3 arguments") + } + if argsList.Len() > 5 { + return newErrorFormulaArg(formulaErrorVALUE, "PV allows at most 5 arguments") + } + rate := argsList.Front().Value.(formulaArg).ToNumber() + if rate.Type != ArgNumber { + return rate + } + nper := argsList.Front().Next().Value.(formulaArg).ToNumber() + if nper.Type != ArgNumber { + return nper + } + pmt := argsList.Front().Next().Next().Value.(formulaArg).ToNumber() + if pmt.Type != ArgNumber { + return pmt + } + fv := newNumberFormulaArg(0) + if argsList.Len() >= 4 { + if fv = argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber(); fv.Type != ArgNumber { + return fv + } + } + t := newNumberFormulaArg(0) + if argsList.Len() == 5 { + if t = argsList.Back().Value.(formulaArg).ToNumber(); t.Type != ArgNumber { + return t + } + if t.Number != 0 { + t.Number = 1 + } + } + if rate.Number == 0 { + return newNumberFormulaArg(-pmt.Number*nper.Number - fv.Number) + } + return newNumberFormulaArg((((1-math.Pow(1+rate.Number, nper.Number))/rate.Number)*pmt.Number*(1+rate.Number*t.Number) - fv.Number) / math.Pow(1+rate.Number, nper.Number)) +} + // RRI function calculates the equivalent interest rate for an investment with // specified present value, future value and duration. The syntax of the // function is: diff --git a/calc_test.go b/calc_test.go index af27079..1545c76 100644 --- a/calc_test.go +++ b/calc_test.go @@ -863,6 +863,14 @@ func TestCalcCellValue(t *testing.T) { "=QUARTILE(A1:A4,2)": "1.5", // QUARTILE.INC "=QUARTILE.INC(A1:A4,0)": "0", + // RANK + "=RANK(1,A1:B5)": "5", + "=RANK(1,A1:B5,0)": "5", + "=RANK(1,A1:B5,1)": "2", + // RANK.EQ + "=RANK.EQ(1,A1:B5)": "5", + "=RANK.EQ(1,A1:B5,0)": "5", + "=RANK.EQ(1,A1:B5,1)": "2", // SKEW "=SKEW(1,2,3,4,3)": "-0.404796008910937", "=SKEW(A1:B2)": "0", @@ -1381,6 +1389,10 @@ func TestCalcCellValue(t *testing.T) { // PRICEDISC "=PRICEDISC(\"04/01/2017\",\"03/31/2021\",2.5%,100)": "90", "=PRICEDISC(\"04/01/2017\",\"03/31/2021\",2.5%,100,3)": "90", + // PV + "=PV(0,60,1000)": "-60000", + "=PV(5%/12,60,1000)": "-52990.70632392748", + "=PV(10%/4,16,2000,0,1)": "-26762.75545288113", // RRI "=RRI(10,10000,15000)": "0.0413797439924106", // SLN @@ -2056,6 +2068,18 @@ func TestCalcCellValue(t *testing.T) { "=QUARTILE(A1:A4,5)": "#NUM!", // QUARTILE.INC "=QUARTILE.INC()": "QUARTILE.INC requires 2 arguments", + // RANK + "=RANK()": "RANK requires at least 2 arguments", + "=RANK(1,A1:B5,0,0)": "RANK requires at most 3 arguments", + "=RANK(-1,A1:B5)": "#N/A", + "=RANK(\"\",A1:B5)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=RANK(1,A1:B5,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // RANK.EQ + "=RANK.EQ()": "RANK.EQ requires at least 2 arguments", + "=RANK.EQ(1,A1:B5,0,0)": "RANK.EQ requires at most 3 arguments", + "=RANK.EQ(-1,A1:B5)": "#N/A", + "=RANK.EQ(\"\",A1:B5)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=RANK.EQ(1,A1:B5,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", // SKEW "=SKEW()": "SKEW requires at least 1 argument", "=SKEW(\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", @@ -2671,6 +2695,14 @@ func TestCalcCellValue(t *testing.T) { "=PRICEDISC(\"04/01/2016\",\"03/31/2021\",0,100)": "PRICEDISC requires discount > 0", "=PRICEDISC(\"04/01/2016\",\"03/31/2021\",95,0)": "PRICEDISC requires redemption > 0", "=PRICEDISC(\"04/01/2016\",\"03/31/2021\",95,100,5)": "invalid basis", + // PV + "=PV()": "PV requires at least 3 arguments", + "=PV(10%/4,16,2000,0,1,0)": "PV allows at most 5 arguments", + "=PV(\"\",16,2000,0,1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=PV(10%/4,\"\",2000,0,1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=PV(10%/4,16,\"\",0,1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=PV(10%/4,16,2000,\"\",1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=PV(10%/4,16,2000,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", // RRI "=RRI()": "RRI requires 3 arguments", "=RRI(\"\",\"\",\"\")": "#NUM!", |