ref #65: new formula functions PERCENTRANK.EXC, PERCENTRANK.INC and PERCENTRANK

3 files changed, 137 insertions, 4 deletions

diff --git a/calc.go b/calc.go
index 580ecfb..a58a96d 100644
--- a/calc.go
+++ b/calc.go
@@ -501,6 +501,9 @@ type formulaFuncs struct {
 //    PERCENTILE.EXC
 //    PERCENTILE.INC
 //    PERCENTILE
+//    PERCENTRANK.EXC
+//    PERCENTRANK.INC
+//    PERCENTRANK
 //    PERMUT
 //    PERMUTATIONA
 //    PI
@@ -5859,6 +5862,88 @@ func (fn *formulaFuncs) PERCENTILE(argsList *list.List) formulaArg {
 	return newNumberFormulaArg(numbers[int(base)] + ((numbers[int(next)] - numbers[int(base)]) * proportion))
 }
 
+// percentrank is an implementation of the formula functions PERCENTRANK and
+// PERCENTRANK.INC.
+func (fn *formulaFuncs) percentrank(name string, argsList *list.List) formulaArg {
+	if argsList.Len() != 2 && argsList.Len() != 3 {
+		return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 2 or 3 arguments", name))
+	}
+	array := argsList.Front().Value.(formulaArg).ToList()
+	x := argsList.Front().Next().Value.(formulaArg).ToNumber()
+	if x.Type != ArgNumber {
+		return x
+	}
+	numbers := []float64{}
+	for _, arg := range array {
+		if arg.Type == ArgError {
+			return arg
+		}
+		num := arg.ToNumber()
+		if num.Type == ArgNumber {
+			numbers = append(numbers, num.Number)
+		}
+	}
+	cnt := len(numbers)
+	sort.Float64s(numbers)
+	if x.Number < numbers[0] || x.Number > numbers[cnt-1] {
+		return newErrorFormulaArg(formulaErrorNA, formulaErrorNA)
+	}
+	pos, significance := float64(inFloat64Slice(numbers, x.Number)), newNumberFormulaArg(3)
+	if argsList.Len() == 3 {
+		if significance = argsList.Back().Value.(formulaArg).ToNumber(); significance.Type != ArgNumber {
+			return significance
+		}
+		if significance.Number < 1 {
+			return newErrorFormulaArg(formulaErrorNUM, fmt.Sprintf("%s arguments significance should be > 1", name))
+		}
+	}
+	if pos == -1 {
+		pos = 0
+		cmp := numbers[0]
+		for cmp < x.Number {
+			pos++
+			cmp = numbers[int(pos)]
+		}
+		pos--
+		pos += (x.Number - numbers[int(pos)]) / (cmp - numbers[int(pos)])
+	}
+	pow := math.Pow(10, float64(significance.Number))
+	digit := pow * float64(pos) / (float64(cnt) - 1)
+	if name == "PERCENTRANK.EXC" {
+		digit = pow * float64(pos+1) / (float64(cnt) + 1)
+	}
+	return newNumberFormulaArg(math.Floor(digit) / pow)
+}
+
+// PERCENTRANKdotEXC function calculates the relative position, between 0 and
+// 1 (exclusive), of a specified value within a supplied array. The syntax of
+// the function is:
+//
+//    PERCENTRANK.EXC(array,x,[significance])
+//
+func (fn *formulaFuncs) PERCENTRANKdotEXC(argsList *list.List) formulaArg {
+	return fn.percentrank("PERCENTRANK.EXC", argsList)
+}
+
+// PERCENTRANKdotINC function calculates the relative position, between 0 and
+// 1 (inclusive), of a specified value within a supplied array.The syntax of
+// the function is:
+//
+//    PERCENTRANK.INC(array,x,[significance])
+//
+func (fn *formulaFuncs) PERCENTRANKdotINC(argsList *list.List) formulaArg {
+	return fn.percentrank("PERCENTRANK.INC", argsList)
+}
+
+// PERCENTRANK function calculates the relative position of a specified value,
+// within a set of values, as a percentage. The syntax of the function is:
+//
+//    PERCENTRANK(array,x,[significance])
+//
+func (fn *formulaFuncs) PERCENTRANK(argsList *list.List) formulaArg {
+	return fn.percentrank("PERCENTRANK", argsList)
+}
+
 // PERMUT function calculates the number of permutations of a specified number
 // of objects from a set of objects. The syntax of the function is:
 //
@@ -5993,10 +6078,8 @@ func (fn *formulaFuncs) rank(name string, argsList *list.List) formulaArg {
 	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))
-		}
+	if idx := inFloat64Slice(arr, num.Number); idx != -1 {
+		return newNumberFormulaArg(float64(idx + 1))
 	}
 	return newErrorFormulaArg(formulaErrorNA, formulaErrorNA)
 }
diff --git a/calc_test.go b/calc_test.go
index 26c1ca1..c621f3b 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -886,6 +886,24 @@ func TestCalcCellValue(t *testing.T) {
 		// PERCENTILE
 		"=PERCENTILE(A1:A4,0.2)": "0.6",
 		"=PERCENTILE(0,0)":       "0",
+		// PERCENTRANK.EXC
+		"=PERCENTRANK.EXC(A1:B4,0)":     "0.142",
+		"=PERCENTRANK.EXC(A1:B4,2)":     "0.428",
+		"=PERCENTRANK.EXC(A1:B4,2.5)":   "0.5",
+		"=PERCENTRANK.EXC(A1:B4,2.6,1)": "0.5",
+		"=PERCENTRANK.EXC(A1:B4,5)":     "0.857",
+		// PERCENTRANK.INC
+		"=PERCENTRANK.INC(A1:B4,0)":     "0",
+		"=PERCENTRANK.INC(A1:B4,2)":     "0.4",
+		"=PERCENTRANK.INC(A1:B4,2.5)":   "0.5",
+		"=PERCENTRANK.INC(A1:B4,2.6,1)": "0.5",
+		"=PERCENTRANK.INC(A1:B4,5)":     "1",
+		// PERCENTRANK
+		"=PERCENTRANK(A1:B4,0)":     "0",
+		"=PERCENTRANK(A1:B4,2)":     "0.4",
+		"=PERCENTRANK(A1:B4,2.5)":   "0.5",
+		"=PERCENTRANK(A1:B4,2.6,1)": "0.5",
+		"=PERCENTRANK(A1:B4,5)":     "1",
 		// PERMUT
 		"=PERMUT(6,6)":  "720",
 		"=PERMUT(7,6)":  "5040",
@@ -2176,6 +2194,27 @@ func TestCalcCellValue(t *testing.T) {
 		"=PERCENTILE(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
 		"=PERCENTILE(0,-1)":   "#N/A",
 		"=PERCENTILE(NA(),1)": "#N/A",
+		// PERCENTRANK.EXC
+		"=PERCENTRANK.EXC()":             "PERCENTRANK.EXC requires 2 or 3 arguments",
+		"=PERCENTRANK.EXC(A1:B4,\"\")":   "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=PERCENTRANK.EXC(A1:B4,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=PERCENTRANK.EXC(A1:B4,0,0)":    "PERCENTRANK.EXC arguments significance should be > 1",
+		"=PERCENTRANK.EXC(A1:B4,6)":      "#N/A",
+		"=PERCENTRANK.EXC(NA(),1)":       "#N/A",
+		// PERCENTRANK.INC
+		"=PERCENTRANK.INC()":             "PERCENTRANK.INC requires 2 or 3 arguments",
+		"=PERCENTRANK.INC(A1:B4,\"\")":   "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=PERCENTRANK.INC(A1:B4,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=PERCENTRANK.INC(A1:B4,0,0)":    "PERCENTRANK.INC arguments significance should be > 1",
+		"=PERCENTRANK.INC(A1:B4,6)":      "#N/A",
+		"=PERCENTRANK.INC(NA(),1)":       "#N/A",
+		// PERCENTRANK
+		"=PERCENTRANK()":             "PERCENTRANK requires 2 or 3 arguments",
+		"=PERCENTRANK(A1:B4,\"\")":   "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=PERCENTRANK(A1:B4,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=PERCENTRANK(A1:B4,0,0)":    "PERCENTRANK arguments significance should be > 1",
+		"=PERCENTRANK(A1:B4,6)":      "#N/A",
+		"=PERCENTRANK(NA(),1)":       "#N/A",
 		// PERMUT
 		"=PERMUT()":       "PERMUT requires 2 numeric arguments",
 		"=PERMUT(\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax",
diff --git a/lib.go b/lib.go
index c8e957c..f592fbe 100644
--- a/lib.go
+++ b/lib.go
@@ -381,6 +381,17 @@ func inStrSlice(a []string, x string) int {
 	return -1
 }
 
+// inFloat64Slice provides a method to check if an element is present in an
+// float64 array, and return the index of its location, otherwise return -1.
+func inFloat64Slice(a []float64, x float64) int {
+	for idx, n := range a {
+		if x == n {
+			return idx
+		}
+	}
+	return -1
+}
+
 // boolPtr returns a pointer to a bool with the given value.
 func boolPtr(b bool) *bool { return &b }