// Copyright 2016 - 2021 The excelize Authors. All rights reserved. Use of
// this source code is governed by a BSD-style license that can be found in
// the LICENSE file.
//
// Package excelize providing a set of functions that allow you to write to
// and read from XLSX / XLSM / XLTM files. Supports reading and writing
// spreadsheet documents generated by Microsoft Exce™ 2007 and later. Supports
// complex components by high compatibility, and provided streaming API for
// generating or reading data from a worksheet with huge amounts of data. This
// library needs Go version 1.10 or later.

package excelize

import (
	"bytes"
	"container/list"
	"errors"
	"fmt"
	"math"
	"math/rand"
	"reflect"
	"regexp"
	"sort"
	"strconv"
	"strings"
	"time"
	"unicode"

	"github.com/xuri/efp"
)

// Excel formula errors
const (
	formulaErrorDIV         = "#DIV/0!"
	formulaErrorNAME        = "#NAME?"
	formulaErrorNA          = "#N/A"
	formulaErrorNUM         = "#NUM!"
	formulaErrorVALUE       = "#VALUE!"
	formulaErrorREF         = "#REF!"
	formulaErrorNULL        = "#NULL"
	formulaErrorSPILL       = "#SPILL!"
	formulaErrorCALC        = "#CALC!"
	formulaErrorGETTINGDATA = "#GETTING_DATA"
)

// Numeric precision correct numeric values as legacy Excel application
// https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel In the
// top figure the fraction 1/9000 in Excel is displayed. Although this number
// has a decimal representation that is an infinite string of ones, Excel
// displays only the leading 15 figures. In the second line, the number one
// is added to the fraction, and again Excel displays only 15 figures.
const numericPrecision = 1000000000000000

// cellRef defines the structure of a cell reference.
type cellRef struct {
	Col   int
	Row   int
	Sheet string
}

// cellRef defines the structure of a cell range.
type cellRange struct {
	From cellRef
	To   cellRef
}

// formula criteria condition enumeration.
const (
	_ byte = iota
	criteriaEq
	criteriaLe
	criteriaGe
	criteriaL
	criteriaG
	criteriaBeg
	criteriaEnd
	criteriaErr
)

// formulaCriteria defined formula criteria parser result.
type formulaCriteria struct {
	Type      byte
	Condition string
}

// ArgType is the type if formula argument type.
type ArgType byte

// Formula argument types enumeration.
const (
	ArgUnknown ArgType = iota
	ArgNumber
	ArgString
	ArgList
	ArgMatrix
	ArgError
	ArgEmpty
)

// formulaArg is the argument of a formula or function.
type formulaArg struct {
	Number  float64
	String  string
	List    []formulaArg
	Matrix  [][]formulaArg
	Boolean bool
	Error   string
	Type    ArgType
}

// Value returns a string data type of the formula argument.
func (fa formulaArg) Value() (value string) {
	switch fa.Type {
	case ArgNumber:
		if fa.Boolean {
			if fa.Number == 0 {
				return "FALSE"
			}
			return "TRUE"
		}
		return fmt.Sprintf("%g", fa.Number)
	case ArgString:
		return fa.String
	case ArgError:
		return fa.Error
	}
	return
}

// ToNumber returns a formula argument with number data type.
func (fa formulaArg) ToNumber() formulaArg {
	var n float64
	var err error
	switch fa.Type {
	case ArgString:
		n, err = strconv.ParseFloat(fa.String, 64)
		if err != nil {
			return newErrorFormulaArg(formulaErrorVALUE, err.Error())
		}
	case ArgNumber:
		n = fa.Number
	}
	return newNumberFormulaArg(n)
}

// ToBool returns a formula argument with boolean data type.
func (fa formulaArg) ToBool() formulaArg {
	var b bool
	var err error
	switch fa.Type {
	case ArgString:
		b, err = strconv.ParseBool(fa.String)
		if err != nil {
			return newErrorFormulaArg(formulaErrorVALUE, err.Error())
		}
	case ArgNumber:
		if fa.Boolean && fa.Number == 1 {
			b = true
		}
	}
	return newBoolFormulaArg(b)
}

// formulaFuncs is the type of the formula functions.
type formulaFuncs struct{}

// tokenPriority defined basic arithmetic operator priority.
var tokenPriority = map[string]int{
	"^":  5,
	"*":  4,
	"/":  4,
	"+":  3,
	"-":  3,
	"=":  2,
	"<>": 2,
	"<":  2,
	"<=": 2,
	">":  2,
	">=": 2,
	"&":  1,
}

// CalcCellValue provides a function to get calculated cell value. This
// feature is currently in working processing. Array formula, table formula
// and some other formulas are not supported currently.
//
// Supported formulas:
//
//    ABS
//    ACOS
//    ACOSH
//    ACOT
//    ACOTH
//    AND
//    ARABIC
//    ASIN
//    ASINH
//    ATAN2
//    ATANH
//    BASE
//    CEILING
//    CEILING.MATH
//    CEILING.PRECISE
//    CHOOSE
//    CLEAN
//    COMBIN
//    COMBINA
//    COS
//    COSH
//    COT
//    COTH
//    COUNTA
//    CSC
//    CSCH
//    DATE
//    DECIMAL
//    DEGREES
//    EVEN
//    EXP
//    FACT
//    FACTDOUBLE
//    FLOOR
//    FLOOR.MATH
//    FLOOR.PRECISE
//    GCD
//    HLOOKUP
//    IF
//    INT
//    ISBLANK
//    ISERR
//    ISERROR
//    ISEVEN
//    ISNA
//    ISNONTEXT
//    ISNUMBER
//    ISODD
//    ISO.CEILING
//    LCM
//    LEN
//    LN
//    LOG
//    LOG10
//    LOOKUP
//    LOWER
//    MDETERM
//    MEDIAN
//    MOD
//    MROUND
//    MULTINOMIAL
//    MUNIT
//    NA
//    ODD
//    OR
//    PI
//    POWER
//    PRODUCT
//    PROPER
//    QUOTIENT
//    RADIANS
//    RAND
//    RANDBETWEEN
//    ROUND
//    ROUNDDOWN
//    ROUNDUP
//    SEC
//    SECH
//    SIGN
//    SIN
//    SINH
//    SQRT
//    SQRTPI
//    SUM
//    SUMIF
//    SUMSQ
//    TAN
//    TANH
//    TRIM
//    TRUNC
//    UPPER
//    VLOOKUP
//
func (f *File) CalcCellValue(sheet, cell string) (result string, err error) {
	var (
		formula string
		token   efp.Token
	)
	if formula, err = f.GetCellFormula(sheet, cell); err != nil {
		return
	}
	ps := efp.ExcelParser()
	tokens := ps.Parse(formula)
	if tokens == nil {
		return
	}
	if token, err = f.evalInfixExp(sheet, tokens); err != nil {
		return
	}
	result = token.TValue
	isNum, precision := isNumeric(result)
	if isNum && precision > 15 {
		num, _ := roundPrecision(result)
		result = strings.ToUpper(num)
	}
	return
}

// getPriority calculate arithmetic operator priority.
func getPriority(token efp.Token) (pri int) {
	pri, _ = tokenPriority[token.TValue]
	if token.TValue == "-" && token.TType == efp.TokenTypeOperatorPrefix {
		pri = 6
	}
	if isBeginParenthesesToken(token) { // (
		pri = 0
	}
	return
}

// newNumberFormulaArg constructs a number formula argument.
func newNumberFormulaArg(n float64) formulaArg {
	if math.IsNaN(n) {
		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
	}
	return formulaArg{Type: ArgNumber, Number: n}
}

// newStringFormulaArg constructs a string formula argument.
func newStringFormulaArg(s string) formulaArg {
	return formulaArg{Type: ArgString, String: s}
}

// newMatrixFormulaArg constructs a matrix formula argument.
func newMatrixFormulaArg(m [][]formulaArg) formulaArg {
	return formulaArg{Type: ArgMatrix, Matrix: m}
}

// newListFormulaArg create a list formula argument.
func newListFormulaArg(l []formulaArg) formulaArg {
	return formulaArg{Type: ArgList, List: l}
}

// newBoolFormulaArg constructs a boolean formula argument.
func newBoolFormulaArg(b bool) formulaArg {
	var n float64
	if b {
		n = 1
	}
	return formulaArg{Type: ArgNumber, Number: n, Boolean: true}
}

// newErrorFormulaArg create an error formula argument of a given type with a
// specified error message.
func newErrorFormulaArg(formulaError, msg string) formulaArg {
	return formulaArg{Type: ArgError, String: formulaError, Error: msg}
}

// newEmptyFormulaArg create an empty formula argument.
func newEmptyFormulaArg() formulaArg {
	return formulaArg{Type: ArgEmpty}
}

// evalInfixExp evaluate syntax analysis by given infix expression after
// lexical analysis. Evaluate an infix expression containing formulas by
// stacks:
//
//    opd  - Operand
//    opt  - Operator
//    opf  - Operation formula
//    opfd - Operand of the operation formula
//    opft - Operator of the operation formula
//
// Evaluate arguments of the operation formula by list:
//
//    args - Arguments of the operation formula
//
// TODO: handle subtypes: Nothing, Text, Logical, Error, Concatenation, Intersection, Union
//
func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error) {
	var err error
	opdStack, optStack, opfStack, opfdStack, opftStack, argsStack := NewStack(), NewStack(), NewStack(), NewStack(), NewStack(), NewStack()
	for i := 0; i < len(tokens); i++ {
		token := tokens[i]

		// out of function stack
		if opfStack.Len() == 0 {
			if err = f.parseToken(sheet, token, opdStack, optStack); err != nil {
				return efp.Token{}, err
			}
		}

		// function start
		if isFunctionStartToken(token) {
			opfStack.Push(token)
			argsStack.Push(list.New().Init())
			continue
		}

		// in function stack, walk 2 token at once
		if opfStack.Len() > 0 {
			var nextToken efp.Token
			if i+1 < len(tokens) {
				nextToken = tokens[i+1]
			}

			// current token is args or range, skip next token, order required: parse reference first
			if token.TSubType == efp.TokenSubTypeRange {
				if !opftStack.Empty() {
					// parse reference: must reference at here
					result, err := f.parseReference(sheet, token.TValue)
					if err != nil {
						return efp.Token{TValue: formulaErrorNAME}, err
					}
					if result.Type != ArgString {
						return efp.Token{}, errors.New(formulaErrorVALUE)
					}
					opfdStack.Push(efp.Token{
						TType:    efp.TokenTypeOperand,
						TSubType: efp.TokenSubTypeNumber,
						TValue:   result.String,
					})
					continue
				}
				if nextToken.TType == efp.TokenTypeArgument || nextToken.TType == efp.TokenTypeFunction {
					// parse reference: reference or range at here
					result, err := f.parseReference(sheet, token.TValue)
					if err != nil {
						return efp.Token{TValue: formulaErrorNAME}, err
					}
					if result.Type == ArgUnknown {
						return efp.Token{}, errors.New(formulaErrorVALUE)
					}
					argsStack.Peek().(*list.List).PushBack(result)
					continue
				}
			}

			// check current token is opft
			if err = f.parseToken(sheet, token, opfdStack, opftStack); err != nil {
				return efp.Token{}, err
			}

			// current token is arg
			if token.TType == efp.TokenTypeArgument {
				for !opftStack.Empty() {
					// calculate trigger
					topOpt := opftStack.Peek().(efp.Token)
					if err := calculate(opfdStack, topOpt); err != nil {
						return efp.Token{}, err
					}
					opftStack.Pop()
				}
				if !opfdStack.Empty() {
					argsStack.Peek().(*list.List).PushBack(formulaArg{
						String: opfdStack.Pop().(efp.Token).TValue,
						Type:   ArgString,
					})
				}
				continue
			}

			// current token is logical
			if token.TType == efp.OperatorsInfix && token.TSubType == efp.TokenSubTypeLogical {
			}
			if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeLogical {
				argsStack.Peek().(*list.List).PushBack(formulaArg{
					String: token.TValue,
					Type:   ArgString,
				})
			}

			// current token is text
			if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText {
				argsStack.Peek().(*list.List).PushBack(formulaArg{
					String: token.TValue,
					Type:   ArgString,
				})
			}
			if err = evalInfixExpFunc(token, nextToken, opfStack, opdStack, opftStack, opfdStack, argsStack); err != nil {
				return efp.Token{}, err
			}
		}
	}
	for optStack.Len() != 0 {
		topOpt := optStack.Peek().(efp.Token)
		if err = calculate(opdStack, topOpt); err != nil {
			return efp.Token{}, err
		}
		optStack.Pop()
	}
	if opdStack.Len() == 0 {
		return efp.Token{}, errors.New("formula not valid")
	}
	return opdStack.Peek().(efp.Token), err
}

// evalInfixExpFunc evaluate formula function in the infix expression.
func evalInfixExpFunc(token, nextToken efp.Token, opfStack, opdStack, opftStack, opfdStack, argsStack *Stack) error {
	if !isFunctionStopToken(token) {
		return nil
	}
	// current token is function stop
	for !opftStack.Empty() {
		// calculate trigger
		topOpt := opftStack.Peek().(efp.Token)
		if err := calculate(opfdStack, topOpt); err != nil {
			return err
		}
		opftStack.Pop()
	}

	// push opfd to args
	if opfdStack.Len() > 0 {
		argsStack.Peek().(*list.List).PushBack(formulaArg{
			String: opfdStack.Pop().(efp.Token).TValue,
			Type:   ArgString,
		})
	}
	// call formula function to evaluate
	arg := callFuncByName(&formulaFuncs{}, strings.NewReplacer(
		"_xlfn", "", ".", "").Replace(opfStack.Peek().(efp.Token).TValue),
		[]reflect.Value{reflect.ValueOf(argsStack.Peek().(*list.List))})
	if arg.Type == ArgError {
		return errors.New(arg.Value())
	}
	argsStack.Pop()
	opfStack.Pop()
	if opfStack.Len() > 0 { // still in function stack
		if nextToken.TType == efp.TokenTypeOperatorInfix {
			// mathematics calculate in formula function
			opfdStack.Push(efp.Token{TValue: arg.Value(), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
		} else {
			argsStack.Peek().(*list.List).PushBack(arg)
		}
	} else {
		opdStack.Push(efp.Token{TValue: arg.Value(), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	}
	return nil
}

// calcPow evaluate exponentiation arithmetic operations.
func calcPow(rOpd, lOpd string, opdStack *Stack) error {
	lOpdVal, err := strconv.ParseFloat(lOpd, 64)
	if err != nil {
		return err
	}
	rOpdVal, err := strconv.ParseFloat(rOpd, 64)
	if err != nil {
		return err
	}
	result := math.Pow(lOpdVal, rOpdVal)
	opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calcEq evaluate equal arithmetic operations.
func calcEq(rOpd, lOpd string, opdStack *Stack) error {
	opdStack.Push(efp.Token{TValue: strings.ToUpper(strconv.FormatBool(rOpd == lOpd)), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calcNEq evaluate not equal arithmetic operations.
func calcNEq(rOpd, lOpd string, opdStack *Stack) error {
	opdStack.Push(efp.Token{TValue: strings.ToUpper(strconv.FormatBool(rOpd != lOpd)), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calcL evaluate less than arithmetic operations.
func calcL(rOpd, lOpd string, opdStack *Stack) error {
	lOpdVal, err := strconv.ParseFloat(lOpd, 64)
	if err != nil {
		return err
	}
	rOpdVal, err := strconv.ParseFloat(rOpd, 64)
	if err != nil {
		return err
	}
	opdStack.Push(efp.Token{TValue: strings.ToUpper(strconv.FormatBool(rOpdVal > lOpdVal)), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calcLe evaluate less than or equal arithmetic operations.
func calcLe(rOpd, lOpd string, opdStack *Stack) error {
	lOpdVal, err := strconv.ParseFloat(lOpd, 64)
	if err != nil {
		return err
	}
	rOpdVal, err := strconv.ParseFloat(rOpd, 64)
	if err != nil {
		return err
	}
	opdStack.Push(efp.Token{TValue: strings.ToUpper(strconv.FormatBool(rOpdVal >= lOpdVal)), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calcG evaluate greater than or equal arithmetic operations.
func calcG(rOpd, lOpd string, opdStack *Stack) error {
	lOpdVal, err := strconv.ParseFloat(lOpd, 64)
	if err != nil {
		return err
	}
	rOpdVal, err := strconv.ParseFloat(rOpd, 64)
	if err != nil {
		return err
	}
	opdStack.Push(efp.Token{TValue: strings.ToUpper(strconv.FormatBool(rOpdVal < lOpdVal)), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calcGe evaluate greater than or equal arithmetic operations.
func calcGe(rOpd, lOpd string, opdStack *Stack) error {
	lOpdVal, err := strconv.ParseFloat(lOpd, 64)
	if err != nil {
		return err
	}
	rOpdVal, err := strconv.ParseFloat(rOpd, 64)
	if err != nil {
		return err
	}
	opdStack.Push(efp.Token{TValue: strings.ToUpper(strconv.FormatBool(rOpdVal <= lOpdVal)), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calcSplice evaluate splice '&' operations.
func calcSplice(rOpd, lOpd string, opdStack *Stack) error {
	opdStack.Push(efp.Token{TValue: lOpd + rOpd, TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calcAdd evaluate addition arithmetic operations.
func calcAdd(rOpd, lOpd string, opdStack *Stack) error {
	lOpdVal, err := strconv.ParseFloat(lOpd, 64)
	if err != nil {
		return err
	}
	rOpdVal, err := strconv.ParseFloat(rOpd, 64)
	if err != nil {
		return err
	}
	result := lOpdVal + rOpdVal
	opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calcSubtract evaluate subtraction arithmetic operations.
func calcSubtract(rOpd, lOpd string, opdStack *Stack) error {
	lOpdVal, err := strconv.ParseFloat(lOpd, 64)
	if err != nil {
		return err
	}
	rOpdVal, err := strconv.ParseFloat(rOpd, 64)
	if err != nil {
		return err
	}
	result := lOpdVal - rOpdVal
	opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calcMultiply evaluate multiplication arithmetic operations.
func calcMultiply(rOpd, lOpd string, opdStack *Stack) error {
	lOpdVal, err := strconv.ParseFloat(lOpd, 64)
	if err != nil {
		return err
	}
	rOpdVal, err := strconv.ParseFloat(rOpd, 64)
	if err != nil {
		return err
	}
	result := lOpdVal * rOpdVal
	opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calcDiv evaluate division arithmetic operations.
func calcDiv(rOpd, lOpd string, opdStack *Stack) error {
	lOpdVal, err := strconv.ParseFloat(lOpd, 64)
	if err != nil {
		return err
	}
	rOpdVal, err := strconv.ParseFloat(rOpd, 64)
	if err != nil {
		return err
	}
	result := lOpdVal / rOpdVal
	if rOpdVal == 0 {
		return errors.New(formulaErrorDIV)
	}
	opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	return nil
}

// calculate evaluate basic arithmetic operations.
func calculate(opdStack *Stack, opt efp.Token) error {
	if opt.TValue == "-" && opt.TType == efp.TokenTypeOperatorPrefix {
		if opdStack.Len() < 1 {
			return errors.New("formula not valid")
		}
		opd := opdStack.Pop().(efp.Token)
		opdVal, err := strconv.ParseFloat(opd.TValue, 64)
		if err != nil {
			return err
		}
		result := 0 - opdVal
		opdStack.Push(efp.Token{TValue: fmt.Sprintf("%g", result), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber})
	}
	tokenCalcFunc := map[string]func(rOpd, lOpd string, opdStack *Stack) error{
		"^":  calcPow,
		"*":  calcMultiply,
		"/":  calcDiv,
		"+":  calcAdd,
		"=":  calcEq,
		"<>": calcNEq,
		"<":  calcL,
		"<=": calcLe,
		">":  calcG,
		">=": calcGe,
		"&":  calcSplice,
	}
	if opt.TValue == "-" && opt.TType == efp.TokenTypeOperatorInfix {
		if opdStack.Len() < 2 {
			return errors.New("formula not valid")
		}
		rOpd := opdStack.Pop().(efp.Token)
		lOpd := opdStack.Pop().(efp.Token)
		if err := calcSubtract(rOpd.TValue, lOpd.TValue, opdStack); err != nil {
			return err
		}
	}
	fn, ok := tokenCalcFunc[opt.TValue]
	if ok {
		if opdStack.Len() < 2 {
			return errors.New("formula not valid")
		}
		rOpd := opdStack.Pop().(efp.Token)
		lOpd := opdStack.Pop().(efp.Token)
		if err := fn(rOpd.TValue, lOpd.TValue, opdStack); err != nil {
			return err
		}
	}
	return nil
}

// parseOperatorPrefixToken parse operator prefix token.
func (f *File) parseOperatorPrefixToken(optStack, opdStack *Stack, token efp.Token) (err error) {
	if optStack.Len() == 0 {
		optStack.Push(token)
	} else {
		tokenPriority := getPriority(token)
		topOpt := optStack.Peek().(efp.Token)
		topOptPriority := getPriority(topOpt)
		if tokenPriority > topOptPriority {
			optStack.Push(token)
		} else {
			for tokenPriority <= topOptPriority {
				optStack.Pop()
				if err = calculate(opdStack, topOpt); err != nil {
					return
				}
				if optStack.Len() > 0 {
					topOpt = optStack.Peek().(efp.Token)
					topOptPriority = getPriority(topOpt)
					continue
				}
				break
			}
			optStack.Push(token)
		}
	}
	return
}

// isFunctionStartToken determine if the token is function stop.
func isFunctionStartToken(token efp.Token) bool {
	return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStart
}

// isFunctionStopToken determine if the token is function stop.
func isFunctionStopToken(token efp.Token) bool {
	return token.TType == efp.TokenTypeFunction && token.TSubType == efp.TokenSubTypeStop
}

// isBeginParenthesesToken determine if the token is begin parentheses: (.
func isBeginParenthesesToken(token efp.Token) bool {
	return token.TType == efp.TokenTypeSubexpression && token.TSubType == efp.TokenSubTypeStart
}

// isEndParenthesesToken determine if the token is end parentheses: ).
func isEndParenthesesToken(token efp.Token) bool {
	return token.TType == efp.TokenTypeSubexpression && token.TSubType == efp.TokenSubTypeStop
}

// isOperatorPrefixToken determine if the token is parse operator prefix
// token.
func isOperatorPrefixToken(token efp.Token) bool {
	_, ok := tokenPriority[token.TValue]
	if (token.TValue == "-" && token.TType == efp.TokenTypeOperatorPrefix) || ok {
		return true
	}
	return false
}

// getDefinedNameRefTo convert defined name to reference range.
func (f *File) getDefinedNameRefTo(definedNameName string, currentSheet string) (refTo string) {
	for _, definedName := range f.GetDefinedName() {
		if definedName.Name == definedNameName {
			refTo = definedName.RefersTo
			// worksheet scope takes precedence over scope workbook when both definedNames exist
			if definedName.Scope == currentSheet {
				break
			}
		}
	}
	return refTo
}

// parseToken parse basic arithmetic operator priority and evaluate based on
// operators and operands.
func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Stack) error {
	// parse reference: must reference at here
	if token.TSubType == efp.TokenSubTypeRange {
		refTo := f.getDefinedNameRefTo(token.TValue, sheet)
		if refTo != "" {
			token.TValue = refTo
		}
		result, err := f.parseReference(sheet, token.TValue)
		if err != nil {
			return errors.New(formulaErrorNAME)
		}
		if result.Type != ArgString {
			return errors.New(formulaErrorVALUE)
		}
		token.TValue = result.String
		token.TType = efp.TokenTypeOperand
		token.TSubType = efp.TokenSubTypeNumber
	}
	if isOperatorPrefixToken(token) {
		if err := f.parseOperatorPrefixToken(optStack, opdStack, token); err != nil {
			return err
		}
	}
	if isBeginParenthesesToken(token) { // (
		optStack.Push(token)
	}
	if isEndParenthesesToken(token) { // )
		for !isBeginParenthesesToken(optStack.Peek().(efp.Token)) { // != (
			topOpt := optStack.Peek().(efp.Token)
			if err := calculate(opdStack, topOpt); err != nil {
				return err
			}
			optStack.Pop()
		}
		optStack.Pop()
	}
	// opd
	if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeNumber {
		opdStack.Push(token)
	}
	return nil
}

// parseReference parse reference and extract values by given reference
// characters and default sheet name.
func (f *File) parseReference(sheet, reference string) (arg formulaArg, err error) {
	reference = strings.Replace(reference, "$", "", -1)
	refs, cellRanges, cellRefs := list.New(), list.New(), list.New()
	for _, ref := range strings.Split(reference, ":") {
		tokens := strings.Split(ref, "!")
		cr := cellRef{}
		if len(tokens) == 2 { // have a worksheet name
			cr.Sheet = tokens[0]
			if cr.Col, cr.Row, err = CellNameToCoordinates(tokens[1]); err != nil {
				return
			}
			if refs.Len() > 0 {
				e := refs.Back()
				cellRefs.PushBack(e.Value.(cellRef))
				refs.Remove(e)
			}
			refs.PushBack(cr)
			continue
		}
		if cr.Col, cr.Row, err = CellNameToCoordinates(tokens[0]); err != nil {
			if cr.Col, err = ColumnNameToNumber(tokens[0]); err != nil {
				return
			}
			cellRanges.PushBack(cellRange{
				From: cellRef{Sheet: sheet, Col: cr.Col, Row: 1},
				To:   cellRef{Sheet: sheet, Col: cr.Col, Row: TotalRows},
			})
			cellRefs.Init()
			arg, err = f.rangeResolver(cellRefs, cellRanges)
			return
		}
		e := refs.Back()
		if e == nil {
			cr.Sheet = sheet
			refs.PushBack(cr)
			continue
		}
		cellRanges.PushBack(cellRange{
			From: e.Value.(cellRef),
			To:   cr,
		})
		refs.Remove(e)
	}
	if refs.Len() > 0 {
		e := refs.Back()
		cellRefs.PushBack(e.Value.(cellRef))
		refs.Remove(e)
	}
	arg, err = f.rangeResolver(cellRefs, cellRanges)
	return
}

// prepareValueRange prepare value range.
func prepareValueRange(cr cellRange, valueRange []int) {
	if cr.From.Row < valueRange[0] || valueRange[0] == 0 {
		valueRange[0] = cr.From.Row
	}
	if cr.From.Col < valueRange[2] || valueRange[2] == 0 {
		valueRange[2] = cr.From.Col
	}
	if cr.To.Row > valueRange[1] || valueRange[1] == 0 {
		valueRange[1] = cr.To.Row
	}
	if cr.To.Col > valueRange[3] || valueRange[3] == 0 {
		valueRange[3] = cr.To.Col
	}
}

// prepareValueRef prepare value reference.
func prepareValueRef(cr cellRef, valueRange []int) {
	if cr.Row < valueRange[0] || valueRange[0] == 0 {
		valueRange[0] = cr.Row
	}
	if cr.Col < valueRange[2] || valueRange[2] == 0 {
		valueRange[2] = cr.Col
	}
	if cr.Row > valueRange[1] || valueRange[1] == 0 {
		valueRange[1] = cr.Row
	}
	if cr.Col > valueRange[3] || valueRange[3] == 0 {
		valueRange[3] = cr.Col
	}
}

// rangeResolver extract value as string from given reference and range list.
// This function will not ignore the empty cell. For example, A1:A2:A2:B3 will
// be reference A1:B3.
func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (arg formulaArg, err error) {
	// value range order: from row, to row, from column, to column
	valueRange := []int{0, 0, 0, 0}
	var sheet string
	// prepare value range
	for temp := cellRanges.Front(); temp != nil; temp = temp.Next() {
		cr := temp.Value.(cellRange)
		if cr.From.Sheet != cr.To.Sheet {
			err = errors.New(formulaErrorVALUE)
		}
		rng := []int{cr.From.Col, cr.From.Row, cr.To.Col, cr.To.Row}
		sortCoordinates(rng)
		cr.From.Col, cr.From.Row, cr.To.Col, cr.To.Row = rng[0], rng[1], rng[2], rng[3]
		prepareValueRange(cr, valueRange)
		if cr.From.Sheet != "" {
			sheet = cr.From.Sheet
		}
	}
	for temp := cellRefs.Front(); temp != nil; temp = temp.Next() {
		cr := temp.Value.(cellRef)
		if cr.Sheet != "" {
			sheet = cr.Sheet
		}
		prepareValueRef(cr, valueRange)
	}
	// extract value from ranges
	if cellRanges.Len() > 0 {
		arg.Type = ArgMatrix
		for row := valueRange[0]; row <= valueRange[1]; row++ {
			var matrixRow = []formulaArg{}
			for col := valueRange[2]; col <= valueRange[3]; col++ {
				var cell, value string
				if cell, err = CoordinatesToCellName(col, row); err != nil {
					return
				}
				if value, err = f.GetCellValue(sheet, cell); err != nil {
					return
				}
				matrixRow = append(matrixRow, formulaArg{
					String: value,
					Type:   ArgString,
				})
			}
			arg.Matrix = append(arg.Matrix, matrixRow)
		}
		return
	}
	// extract value from references
	for temp := cellRefs.Front(); temp != nil; temp = temp.Next() {
		cr := temp.Value.(cellRef)
		var cell string
		if cell, err = CoordinatesToCellName(cr.Col, cr.Row); err != nil {
			return
		}
		if arg.String, err = f.GetCellValue(cr.Sheet, cell); err != nil {
			return
		}
		arg.Type = ArgString
	}
	return
}

// callFuncByName calls the no error or only error return function with
// reflect by given receiver, name and parameters.
func callFuncByName(receiver interface{}, name string, params []reflect.Value) (arg formulaArg) {
	function := reflect.ValueOf(receiver).MethodByName(name)
	if function.IsValid() {
		rt := function.Call(params)
		if len(rt) == 0 {
			return
		}
		arg = rt[0].Interface().(formulaArg)
		return
	}
	return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("not support %s function", name))
}

// formulaCriteriaParser parse formula criteria.
func formulaCriteriaParser(exp string) (fc *formulaCriteria) {
	fc = &formulaCriteria{}
	if exp == "" {
		return
	}
	if match := regexp.MustCompile(`^([0-9]+)$`).FindStringSubmatch(exp); len(match) > 1 {
		fc.Type, fc.Condition = criteriaEq, match[1]
		return
	}
	if match := regexp.MustCompile(`^=(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
		fc.Type, fc.Condition = criteriaEq, match[1]
		return
	}
	if match := regexp.MustCompile(`^<=(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
		fc.Type, fc.Condition = criteriaLe, match[1]
		return
	}
	if match := regexp.MustCompile(`^>=(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
		fc.Type, fc.Condition = criteriaGe, match[1]
		return
	}
	if match := regexp.MustCompile(`^<(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
		fc.Type, fc.Condition = criteriaL, match[1]
		return
	}
	if match := regexp.MustCompile(`^>(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
		fc.Type, fc.Condition = criteriaG, match[1]
		return
	}
	if strings.Contains(exp, "*") {
		if strings.HasPrefix(exp, "*") {
			fc.Type, fc.Condition = criteriaEnd, strings.TrimPrefix(exp, "*")
		}
		if strings.HasSuffix(exp, "*") {
			fc.Type, fc.Condition = criteriaBeg, strings.TrimSuffix(exp, "*")
		}
		return
	}
	fc.Type, fc.Condition = criteriaEq, exp
	return
}

// formulaCriteriaEval evaluate formula criteria expression.
func formulaCriteriaEval(val string, criteria *formulaCriteria) (result bool, err error) {
	var value, expected float64
	var e error
	var prepareValue = func(val, cond string) (value float64, expected float64, err error) {
		if value, err = strconv.ParseFloat(val, 64); err != nil {
			return
		}
		if expected, err = strconv.ParseFloat(criteria.Condition, 64); err != nil {
			return
		}
		return
	}
	switch criteria.Type {
	case criteriaEq:
		return val == criteria.Condition, err
	case criteriaLe:
		value, expected, e = prepareValue(val, criteria.Condition)
		return value <= expected && e == nil, err
	case criteriaGe:
		value, expected, e = prepareValue(val, criteria.Condition)
		return value >= expected && e == nil, err
	case criteriaL:
		value, expected, e = prepareValue(val, criteria.Condition)
		return value < expected && e == nil, err
	case criteriaG:
		value, expected, e = prepareValue(val, criteria.Condition)
		return value > expected && e == nil, err
	case criteriaBeg:
		return strings.HasPrefix(val, criteria.Condition), err
	case criteriaEnd:
		return strings.HasSuffix(val, criteria.Condition), err
	}
	return
}

// Math and Trigonometric functions

// ABS function returns the absolute value of any supplied number. The syntax
// of the function is:
//
//    ABS(number)
//
func (fn *formulaFuncs) ABS(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ABS requires 1 numeric argument")
	}
	arg := argsList.Front().Value.(formulaArg).ToNumber()
	if arg.Type == ArgError {
		return arg
	}
	return newNumberFormulaArg(math.Abs(arg.Number))
}

// ACOS function calculates the arccosine (i.e. the inverse cosine) of a given
// number, and returns an angle, in radians, between 0 and π. The syntax of
// the function is:
//
//    ACOS(number)
//
func (fn *formulaFuncs) ACOS(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ACOS requires 1 numeric argument")
	}
	arg := argsList.Front().Value.(formulaArg).ToNumber()
	if arg.Type == ArgError {
		return arg
	}
	return newNumberFormulaArg(math.Acos(arg.Number))
}

// ACOSH function calculates the inverse hyperbolic cosine of a supplied number.
// of the function is:
//
//    ACOSH(number)
//
func (fn *formulaFuncs) ACOSH(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ACOSH requires 1 numeric argument")
	}
	arg := argsList.Front().Value.(formulaArg).ToNumber()
	if arg.Type == ArgError {
		return arg
	}
	return newNumberFormulaArg(math.Acosh(arg.Number))
}

// ACOT function calculates the arccotangent (i.e. the inverse cotangent) of a
// given number, and returns an angle, in radians, between 0 and π. The syntax
// of the function is:
//
//    ACOT(number)
//
func (fn *formulaFuncs) ACOT(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ACOT requires 1 numeric argument")
	}
	arg := argsList.Front().Value.(formulaArg).ToNumber()
	if arg.Type == ArgError {
		return arg
	}
	return newNumberFormulaArg(math.Pi/2 - math.Atan(arg.Number))
}

// ACOTH function calculates the hyperbolic arccotangent (coth) of a supplied
// value. The syntax of the function is:
//
//    ACOTH(number)
//
func (fn *formulaFuncs) ACOTH(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ACOTH requires 1 numeric argument")
	}
	arg := argsList.Front().Value.(formulaArg).ToNumber()
	if arg.Type == ArgError {
		return arg
	}
	return newNumberFormulaArg(math.Atanh(1 / arg.Number))
}

// ARABIC function converts a Roman numeral into an Arabic numeral. The syntax
// of the function is:
//
//    ARABIC(text)
//
func (fn *formulaFuncs) ARABIC(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ARABIC requires 1 numeric argument")
	}
	charMap := map[rune]float64{'I': 1, 'V': 5, 'X': 10, 'L': 50, 'C': 100, 'D': 500, 'M': 1000}
	val, last, prefix := 0.0, 0.0, 1.0
	for _, char := range argsList.Front().Value.(formulaArg).String {
		digit := 0.0
		if char == '-' {
			prefix = -1
			continue
		}
		digit, _ = charMap[char]
		val += digit
		switch {
		case last == digit && (last == 5 || last == 50 || last == 500):
			return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
		case 2*last == digit:
			return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
		}
		if last < digit {
			val -= 2 * last
		}
		last = digit
	}
	return newNumberFormulaArg(prefix * val)
}

// ASIN function calculates the arcsine (i.e. the inverse sine) of a given
// number, and returns an angle, in radians, between -π/2 and π/2. The syntax
// of the function is:
//
//    ASIN(number)
//
func (fn *formulaFuncs) ASIN(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ASIN requires 1 numeric argument")
	}
	arg := argsList.Front().Value.(formulaArg).ToNumber()
	if arg.Type == ArgError {
		return arg
	}
	return newNumberFormulaArg(math.Asin(arg.Number))
}

// ASINH function calculates the inverse hyperbolic sine of a supplied number.
// The syntax of the function is:
//
//    ASINH(number)
//
func (fn *formulaFuncs) ASINH(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ASINH requires 1 numeric argument")
	}
	arg := argsList.Front().Value.(formulaArg).ToNumber()
	if arg.Type == ArgError {
		return arg
	}
	return newNumberFormulaArg(math.Asinh(arg.Number))
}

// ATAN function calculates the arctangent (i.e. the inverse tangent) of a
// given number, and returns an angle, in radians, between -π/2 and +π/2. The
// syntax of the function is:
//
//    ATAN(number)
//
func (fn *formulaFuncs) ATAN(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ATAN requires 1 numeric argument")
	}
	arg := argsList.Front().Value.(formulaArg).ToNumber()
	if arg.Type == ArgError {
		return arg
	}
	return newNumberFormulaArg(math.Atan(arg.Number))
}

// ATANH function calculates the inverse hyperbolic tangent of a supplied
// number. The syntax of the function is:
//
//    ATANH(number)
//
func (fn *formulaFuncs) ATANH(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ATANH requires 1 numeric argument")
	}
	arg := argsList.Front().Value.(formulaArg).ToNumber()
	if arg.Type == ArgError {
		return arg
	}
	return newNumberFormulaArg(math.Atanh(arg.Number))
}

// ATAN2 function calculates the arctangent (i.e. the inverse tangent) of a
// given set of x and y coordinates, and returns an angle, in radians, between
// -π/2 and +π/2. The syntax of the function is:
//
//    ATAN2(x_num,y_num)
//
func (fn *formulaFuncs) ATAN2(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "ATAN2 requires 2 numeric arguments")
	}
	x := argsList.Back().Value.(formulaArg).ToNumber()
	if x.Type == ArgError {
		return x
	}
	y := argsList.Front().Value.(formulaArg).ToNumber()
	if y.Type == ArgError {
		return y
	}
	return newNumberFormulaArg(math.Atan2(x.Number, y.Number))
}

// BASE function converts a number into a supplied base (radix), and returns a
// text representation of the calculated value. The syntax of the function is:
//
//    BASE(number,radix,[min_length])
//
func (fn *formulaFuncs) BASE(argsList *list.List) formulaArg {
	if argsList.Len() < 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "BASE requires at least 2 arguments")
	}
	if argsList.Len() > 3 {
		return newErrorFormulaArg(formulaErrorVALUE, "BASE allows at most 3 arguments")
	}
	var minLength int
	var err error
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	radix := argsList.Front().Next().Value.(formulaArg).ToNumber()
	if radix.Type == ArgError {
		return radix
	}
	if int(radix.Number) < 2 || int(radix.Number) > 36 {
		return newErrorFormulaArg(formulaErrorVALUE, "radix must be an integer >= 2 and <= 36")
	}
	if argsList.Len() > 2 {
		if minLength, err = strconv.Atoi(argsList.Back().Value.(formulaArg).String); err != nil {
			return newErrorFormulaArg(formulaErrorVALUE, err.Error())
		}
	}
	result := strconv.FormatInt(int64(number.Number), int(radix.Number))
	if len(result) < minLength {
		result = strings.Repeat("0", minLength-len(result)) + result
	}
	return newStringFormulaArg(strings.ToUpper(result))
}

// CEILING function rounds a supplied number away from zero, to the nearest
// multiple of a given number. The syntax of the function is:
//
//    CEILING(number,significance)
//
func (fn *formulaFuncs) CEILING(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "CEILING requires at least 1 argument")
	}
	if argsList.Len() > 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "CEILING allows at most 2 arguments")
	}
	number, significance, res := 0.0, 1.0, 0.0
	n := argsList.Front().Value.(formulaArg).ToNumber()
	if n.Type == ArgError {
		return n
	}
	number = n.Number
	if number < 0 {
		significance = -1
	}
	if argsList.Len() > 1 {
		s := argsList.Back().Value.(formulaArg).ToNumber()
		if s.Type == ArgError {
			return s
		}
		significance = s.Number
	}
	if significance < 0 && number > 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "negative sig to CEILING invalid")
	}
	if argsList.Len() == 1 {
		return newNumberFormulaArg(math.Ceil(number))
	}
	number, res = math.Modf(number / significance)
	if res > 0 {
		number++
	}
	return newNumberFormulaArg(number * significance)
}

// CEILINGMATH function rounds a supplied number up to a supplied multiple of
// significance. The syntax of the function is:
//
//    CEILING.MATH(number,[significance],[mode])
//
func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "CEILING.MATH requires at least 1 argument")
	}
	if argsList.Len() > 3 {
		return newErrorFormulaArg(formulaErrorVALUE, "CEILING.MATH allows at most 3 arguments")
	}
	number, significance, mode := 0.0, 1.0, 1.0
	n := argsList.Front().Value.(formulaArg).ToNumber()
	if n.Type == ArgError {
		return n
	}
	number = n.Number
	if number < 0 {
		significance = -1
	}
	if argsList.Len() > 1 {
		s := argsList.Front().Next().Value.(formulaArg).ToNumber()
		if s.Type == ArgError {
			return s
		}
		significance = s.Number
	}
	if argsList.Len() == 1 {
		return newNumberFormulaArg(math.Ceil(number))
	}
	if argsList.Len() > 2 {
		m := argsList.Back().Value.(formulaArg).ToNumber()
		if m.Type == ArgError {
			return m
		}
		mode = m.Number
	}
	val, res := math.Modf(number / significance)
	if res != 0 {
		if number > 0 {
			val++
		} else if mode < 0 {
			val--
		}
	}
	return newNumberFormulaArg(val * significance)
}

// CEILINGPRECISE function rounds a supplied number up (regardless of the
// number's sign), to the nearest multiple of a given number. The syntax of
// the function is:
//
//    CEILING.PRECISE(number,[significance])
//
func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "CEILING.PRECISE requires at least 1 argument")
	}
	if argsList.Len() > 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "CEILING.PRECISE allows at most 2 arguments")
	}
	number, significance := 0.0, 1.0
	n := argsList.Front().Value.(formulaArg).ToNumber()
	if n.Type == ArgError {
		return n
	}
	number = n.Number
	if number < 0 {
		significance = -1
	}
	if argsList.Len() == 1 {
		return newNumberFormulaArg(math.Ceil(number))
	}
	if argsList.Len() > 1 {
		s := argsList.Back().Value.(formulaArg).ToNumber()
		if s.Type == ArgError {
			return s
		}
		significance = s.Number
		significance = math.Abs(significance)
		if significance == 0 {
			return newNumberFormulaArg(significance)
		}
	}
	val, res := math.Modf(number / significance)
	if res != 0 {
		if number > 0 {
			val++
		}
	}
	return newNumberFormulaArg(val * significance)
}

// COMBIN function calculates the number of combinations (in any order) of a
// given number objects from a set. The syntax of the function is:
//
//    COMBIN(number,number_chosen)
//
func (fn *formulaFuncs) COMBIN(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "COMBIN requires 2 argument")
	}
	number, chosen, val := 0.0, 0.0, 1.0
	n := argsList.Front().Value.(formulaArg).ToNumber()
	if n.Type == ArgError {
		return n
	}
	number = n.Number
	c := argsList.Back().Value.(formulaArg).ToNumber()
	if c.Type == ArgError {
		return c
	}
	chosen = c.Number
	number, chosen = math.Trunc(number), math.Trunc(chosen)
	if chosen > number {
		return newErrorFormulaArg(formulaErrorVALUE, "COMBIN requires number >= number_chosen")
	}
	if chosen == number || chosen == 0 {
		return newNumberFormulaArg(1)
	}
	for c := float64(1); c <= chosen; c++ {
		val *= (number + 1 - c) / c
	}
	return newNumberFormulaArg(math.Ceil(val))
}

// COMBINA function calculates the number of combinations, with repetitions,
// of a given number objects from a set. The syntax of the function is:
//
//    COMBINA(number,number_chosen)
//
func (fn *formulaFuncs) COMBINA(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "COMBINA requires 2 argument")
	}
	var number, chosen float64
	n := argsList.Front().Value.(formulaArg).ToNumber()
	if n.Type == ArgError {
		return n
	}
	number = n.Number
	c := argsList.Back().Value.(formulaArg).ToNumber()
	if c.Type == ArgError {
		return c
	}
	chosen = c.Number
	number, chosen = math.Trunc(number), math.Trunc(chosen)
	if number < chosen {
		return newErrorFormulaArg(formulaErrorVALUE, "COMBINA requires number > number_chosen")
	}
	if number == 0 {
		return newNumberFormulaArg(number)
	}
	args := list.New()
	args.PushBack(formulaArg{
		String: fmt.Sprintf("%g", number+chosen-1),
		Type:   ArgString,
	})
	args.PushBack(formulaArg{
		String: fmt.Sprintf("%g", number-1),
		Type:   ArgString,
	})
	return fn.COMBIN(args)
}

// COS function calculates the cosine of a given angle. The syntax of the
// function is:
//
//    COS(number)
//
func (fn *formulaFuncs) COS(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "COS requires 1 numeric argument")
	}
	val := argsList.Front().Value.(formulaArg).ToNumber()
	if val.Type == ArgError {
		return val
	}
	return newNumberFormulaArg(math.Cos(val.Number))
}

// COSH function calculates the hyperbolic cosine (cosh) of a supplied number.
// The syntax of the function is:
//
//    COSH(number)
//
func (fn *formulaFuncs) COSH(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "COSH requires 1 numeric argument")
	}
	val := argsList.Front().Value.(formulaArg).ToNumber()
	if val.Type == ArgError {
		return val
	}
	return newNumberFormulaArg(math.Cosh(val.Number))
}

// COT function calculates the cotangent of a given angle. The syntax of the
// function is:
//
//    COT(number)
//
func (fn *formulaFuncs) COT(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "COT requires 1 numeric argument")
	}
	val := argsList.Front().Value.(formulaArg).ToNumber()
	if val.Type == ArgError {
		return val
	}
	if val.Number == 0 {
		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
	}
	return newNumberFormulaArg(1 / math.Tan(val.Number))
}

// COTH function calculates the hyperbolic cotangent (coth) of a supplied
// angle. The syntax of the function is:
//
//    COTH(number)
//
func (fn *formulaFuncs) COTH(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "COTH requires 1 numeric argument")
	}
	val := argsList.Front().Value.(formulaArg).ToNumber()
	if val.Type == ArgError {
		return val
	}
	if val.Number == 0 {
		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
	}
	return newNumberFormulaArg((math.Exp(val.Number) + math.Exp(-val.Number)) / (math.Exp(val.Number) - math.Exp(-val.Number)))
}

// CSC function calculates the cosecant of a given angle. The syntax of the
// function is:
//
//    CSC(number)
//
func (fn *formulaFuncs) CSC(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "CSC requires 1 numeric argument")
	}
	val := argsList.Front().Value.(formulaArg).ToNumber()
	if val.Type == ArgError {
		return val
	}
	if val.Number == 0 {
		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
	}
	return newNumberFormulaArg(1 / math.Sin(val.Number))
}

// CSCH function calculates the hyperbolic cosecant (csch) of a supplied
// angle. The syntax of the function is:
//
//    CSCH(number)
//
func (fn *formulaFuncs) CSCH(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "CSCH requires 1 numeric argument")
	}
	val := argsList.Front().Value.(formulaArg).ToNumber()
	if val.Type == ArgError {
		return val
	}
	if val.Number == 0 {
		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
	}
	return newNumberFormulaArg(1 / math.Sinh(val.Number))
}

// DECIMAL function converts a text representation of a number in a specified
// base, into a decimal value. The syntax of the function is:
//
//    DECIMAL(text,radix)
//
func (fn *formulaFuncs) DECIMAL(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "DECIMAL requires 2 numeric arguments")
	}
	var text = argsList.Front().Value.(formulaArg).String
	var radix int
	var err error
	radix, err = strconv.Atoi(argsList.Back().Value.(formulaArg).String)
	if err != nil {
		return newErrorFormulaArg(formulaErrorVALUE, err.Error())
	}
	if len(text) > 2 && (strings.HasPrefix(text, "0x") || strings.HasPrefix(text, "0X")) {
		text = text[2:]
	}
	val, err := strconv.ParseInt(text, radix, 64)
	if err != nil {
		return newErrorFormulaArg(formulaErrorVALUE, err.Error())
	}
	return newNumberFormulaArg(float64(val))
}

// DEGREES function converts radians into degrees. The syntax of the function
// is:
//
//    DEGREES(angle)
//
func (fn *formulaFuncs) DEGREES(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "DEGREES requires 1 numeric argument")
	}
	val := argsList.Front().Value.(formulaArg).ToNumber()
	if val.Type == ArgError {
		return val
	}
	if val.Number == 0 {
		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
	}
	return newNumberFormulaArg(180.0 / math.Pi * val.Number)
}

// EVEN function rounds a supplied number away from zero (i.e. rounds a
// positive number up and a negative number down), to the next even number.
// The syntax of the function is:
//
//    EVEN(number)
//
func (fn *formulaFuncs) EVEN(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "EVEN requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	sign := math.Signbit(number.Number)
	m, frac := math.Modf(number.Number / 2)
	val := m * 2
	if frac != 0 {
		if !sign {
			val += 2
		} else {
			val -= 2
		}
	}
	return newNumberFormulaArg(val)
}

// EXP function calculates the value of the mathematical constant e, raised to
// the power of a given number. The syntax of the function is:
//
//    EXP(number)
//
func (fn *formulaFuncs) EXP(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "EXP requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	return newStringFormulaArg(strings.ToUpper(fmt.Sprintf("%g", math.Exp(number.Number))))
}

// fact returns the factorial of a supplied number.
func fact(number float64) float64 {
	val := float64(1)
	for i := float64(2); i <= number; i++ {
		val *= i
	}
	return val
}

// FACT function returns the factorial of a supplied number. The syntax of the
// function is:
//
//    FACT(number)
//
func (fn *formulaFuncs) FACT(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "FACT requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	if number.Number < 0 {
		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
	}
	return newStringFormulaArg(strings.ToUpper(fmt.Sprintf("%g", fact(number.Number))))
}

// FACTDOUBLE function returns the double factorial of a supplied number. The
// syntax of the function is:
//
//    FACTDOUBLE(number)
//
func (fn *formulaFuncs) FACTDOUBLE(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "FACTDOUBLE requires 1 numeric argument")
	}
	val := 1.0
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	if number.Number < 0 {
		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
	}
	for i := math.Trunc(number.Number); i > 1; i -= 2 {
		val *= i
	}
	return newStringFormulaArg(strings.ToUpper(fmt.Sprintf("%g", val)))
}

// FLOOR function rounds a supplied number towards zero to the nearest
// multiple of a specified significance. The syntax of the function is:
//
//    FLOOR(number,significance)
//
func (fn *formulaFuncs) FLOOR(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "FLOOR requires 2 numeric arguments")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	significance := argsList.Back().Value.(formulaArg).ToNumber()
	if significance.Type == ArgError {
		return significance
	}
	if significance.Number < 0 && number.Number >= 0 {
		return newErrorFormulaArg(formulaErrorNUM, "invalid arguments to FLOOR")
	}
	val := number.Number
	val, res := math.Modf(val / significance.Number)
	if res != 0 {
		if number.Number < 0 && res < 0 {
			val--
		}
	}
	return newStringFormulaArg(strings.ToUpper(fmt.Sprintf("%g", val*significance.Number)))
}

// FLOORMATH function rounds a supplied number down to a supplied multiple of
// significance. The syntax of the function is:
//
//    FLOOR.MATH(number,[significance],[mode])
//
func (fn *formulaFuncs) FLOORMATH(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "FLOOR.MATH requires at least 1 argument")
	}
	if argsList.Len() > 3 {
		return newErrorFormulaArg(formulaErrorVALUE, "FLOOR.MATH allows at most 3 arguments")
	}
	significance, mode := 1.0, 1.0
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	if number.Number < 0 {
		significance = -1
	}
	if argsList.Len() > 1 {
		s := argsList.Front().Next().Value.(formulaArg).ToNumber()
		if s.Type == ArgError {
			return s
		}
		significance = s.Number
	}
	if argsList.Len() == 1 {
		return newNumberFormulaArg(math.Floor(number.Number))
	}
	if argsList.Len() > 2 {
		m := argsList.Back().Value.(formulaArg).ToNumber()
		if m.Type == ArgError {
			return m
		}
		mode = m.Number
	}
	val, res := math.Modf(number.Number / significance)
	if res != 0 && number.Number < 0 && mode > 0 {
		val--
	}
	return newNumberFormulaArg(val * significance)
}

// FLOORPRECISE function rounds a supplied number down to a supplied multiple
// of significance. The syntax of the function is:
//
//    FLOOR.PRECISE(number,[significance])
//
func (fn *formulaFuncs) FLOORPRECISE(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "FLOOR.PRECISE requires at least 1 argument")
	}
	if argsList.Len() > 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "FLOOR.PRECISE allows at most 2 arguments")
	}
	var significance float64
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	if number.Number < 0 {
		significance = -1
	}
	if argsList.Len() == 1 {
		return newNumberFormulaArg(math.Floor(number.Number))
	}
	if argsList.Len() > 1 {
		s := argsList.Back().Value.(formulaArg).ToNumber()
		if s.Type == ArgError {
			return s
		}
		significance = s.Number
		significance = math.Abs(significance)
		if significance == 0 {
			return newNumberFormulaArg(significance)
		}
	}
	val, res := math.Modf(number.Number / significance)
	if res != 0 {
		if number.Number < 0 {
			val--
		}
	}
	return newNumberFormulaArg(val * significance)
}

// gcd returns the greatest common divisor of two supplied integers.
func gcd(x, y float64) float64 {
	x, y = math.Trunc(x), math.Trunc(y)
	if x == 0 {
		return y
	}
	if y == 0 {
		return x
	}
	for x != y {
		if x > y {
			x = x - y
		} else {
			y = y - x
		}
	}
	return x
}

// GCD function returns the greatest common divisor of two or more supplied
// integers. The syntax of the function is:
//
//    GCD(number1,[number2],...)
//
func (fn *formulaFuncs) GCD(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "GCD requires at least 1 argument")
	}
	var (
		val  float64
		nums = []float64{}
		err  error
	)
	for arg := argsList.Front(); arg != nil; arg = arg.Next() {
		token := arg.Value.(formulaArg)
		switch token.Type {
		case ArgString:
			if token.String == "" {
				continue
			}
			if val, err = strconv.ParseFloat(token.String, 64); err != nil {
				return newErrorFormulaArg(formulaErrorVALUE, err.Error())
			}
			break
		case ArgNumber:
			val = token.Number
			break
		}
		nums = append(nums, val)
	}
	if nums[0] < 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "GCD only accepts positive arguments")
	}
	if len(nums) == 1 {
		return newNumberFormulaArg(nums[0])
	}
	cd := nums[0]
	for i := 1; i < len(nums); i++ {
		if nums[i] < 0 {
			return newErrorFormulaArg(formulaErrorVALUE, "GCD only accepts positive arguments")
		}
		cd = gcd(cd, nums[i])
	}
	return newNumberFormulaArg(cd)
}

// INT function truncates a supplied number down to the closest integer. The
// syntax of the function is:
//
//    INT(number)
//
func (fn *formulaFuncs) INT(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "INT requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	val, frac := math.Modf(number.Number)
	if frac < 0 {
		val--
	}
	return newNumberFormulaArg(val)
}

// ISOCEILING function rounds a supplied number up (regardless of the number's
// sign), to the nearest multiple of a supplied significance. The syntax of
// the function is:
//
//    ISO.CEILING(number,[significance])
//
func (fn *formulaFuncs) ISOCEILING(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "ISO.CEILING requires at least 1 argument")
	}
	if argsList.Len() > 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "ISO.CEILING allows at most 2 arguments")
	}
	var significance float64
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	if number.Number < 0 {
		significance = -1
	}
	if argsList.Len() == 1 {
		return newNumberFormulaArg(math.Ceil(number.Number))
	}
	if argsList.Len() > 1 {
		s := argsList.Back().Value.(formulaArg).ToNumber()
		if s.Type == ArgError {
			return s
		}
		significance = s.Number
		significance = math.Abs(significance)
		if significance == 0 {
			return newNumberFormulaArg(significance)
		}
	}
	val, res := math.Modf(number.Number / significance)
	if res != 0 {
		if number.Number > 0 {
			val++
		}
	}
	return newNumberFormulaArg(val * significance)
}

// lcm returns the least common multiple of two supplied integers.
func lcm(a, b float64) float64 {
	a = math.Trunc(a)
	b = math.Trunc(b)
	if a == 0 && b == 0 {
		return 0
	}
	return a * b / gcd(a, b)
}

// LCM function returns the least common multiple of two or more supplied
// integers. The syntax of the function is:
//
//    LCM(number1,[number2],...)
//
func (fn *formulaFuncs) LCM(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "LCM requires at least 1 argument")
	}
	var (
		val  float64
		nums = []float64{}
		err  error
	)
	for arg := argsList.Front(); arg != nil; arg = arg.Next() {
		token := arg.Value.(formulaArg)
		switch token.Type {
		case ArgString:
			if token.String == "" {
				continue
			}
			if val, err = strconv.ParseFloat(token.String, 64); err != nil {
				return newErrorFormulaArg(formulaErrorVALUE, err.Error())
			}
			break
		case ArgNumber:
			val = token.Number
			break
		}
		nums = append(nums, val)
	}
	if nums[0] < 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "LCM only accepts positive arguments")
	}
	if len(nums) == 1 {
		return newNumberFormulaArg(nums[0])
	}
	cm := nums[0]
	for i := 1; i < len(nums); i++ {
		if nums[i] < 0 {
			return newErrorFormulaArg(formulaErrorVALUE, "LCM only accepts positive arguments")
		}
		cm = lcm(cm, nums[i])
	}
	return newNumberFormulaArg(cm)
}

// LN function calculates the natural logarithm of a given number. The syntax
// of the function is:
//
//    LN(number)
//
func (fn *formulaFuncs) LN(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "LN requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	return newNumberFormulaArg(math.Log(number.Number))
}

// LOG function calculates the logarithm of a given number, to a supplied
// base. The syntax of the function is:
//
//    LOG(number,[base])
//
func (fn *formulaFuncs) LOG(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "LOG requires at least 1 argument")
	}
	if argsList.Len() > 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "LOG allows at most 2 arguments")
	}
	base := 10.0
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	if argsList.Len() > 1 {
		b := argsList.Back().Value.(formulaArg).ToNumber()
		if b.Type == ArgError {
			return b
		}
		base = b.Number
	}
	if number.Number == 0 {
		return newErrorFormulaArg(formulaErrorNUM, formulaErrorDIV)
	}
	if base == 0 {
		return newErrorFormulaArg(formulaErrorNUM, formulaErrorDIV)
	}
	if base == 1 {
		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
	}
	return newNumberFormulaArg(math.Log(number.Number) / math.Log(base))
}

// LOG10 function calculates the base 10 logarithm of a given number. The
// syntax of the function is:
//
//    LOG10(number)
//
func (fn *formulaFuncs) LOG10(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "LOG10 requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	return newNumberFormulaArg(math.Log10(number.Number))
}

// minor function implement a minor of a matrix A is the determinant of some
// smaller square matrix.
func minor(sqMtx [][]float64, idx int) [][]float64 {
	ret := [][]float64{}
	for i := range sqMtx {
		if i == 0 {
			continue
		}
		row := []float64{}
		for j := range sqMtx {
			if j == idx {
				continue
			}
			row = append(row, sqMtx[i][j])
		}
		ret = append(ret, row)
	}
	return ret
}

// det determinant of the 2x2 matrix.
func det(sqMtx [][]float64) float64 {
	if len(sqMtx) == 2 {
		m00 := sqMtx[0][0]
		m01 := sqMtx[0][1]
		m10 := sqMtx[1][0]
		m11 := sqMtx[1][1]
		return m00*m11 - m10*m01
	}
	var res, sgn float64 = 0, 1
	for j := range sqMtx {
		res += sgn * sqMtx[0][j] * det(minor(sqMtx, j))
		sgn *= -1
	}
	return res
}

// MDETERM calculates the determinant of a square matrix. The
// syntax of the function is:
//
//    MDETERM(array)
//
func (fn *formulaFuncs) MDETERM(argsList *list.List) (result formulaArg) {
	var (
		num    float64
		numMtx = [][]float64{}
		err    error
		strMtx [][]formulaArg
	)
	if argsList.Len() < 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "MDETERM requires at least 1 argument")
	}
	strMtx = argsList.Front().Value.(formulaArg).Matrix
	var rows = len(strMtx)
	for _, row := range argsList.Front().Value.(formulaArg).Matrix {
		if len(row) != rows {
			return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
		}
		numRow := []float64{}
		for _, ele := range row {
			if num, err = strconv.ParseFloat(ele.String, 64); err != nil {
				return newErrorFormulaArg(formulaErrorVALUE, err.Error())
			}
			numRow = append(numRow, num)
		}
		numMtx = append(numMtx, numRow)
	}
	return newNumberFormulaArg(det(numMtx))
}

// MOD function returns the remainder of a division between two supplied
// numbers. The syntax of the function is:
//
//    MOD(number,divisor)
//
func (fn *formulaFuncs) MOD(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "MOD requires 2 numeric arguments")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	divisor := argsList.Back().Value.(formulaArg).ToNumber()
	if divisor.Type == ArgError {
		return divisor
	}
	if divisor.Number == 0 {
		return newErrorFormulaArg(formulaErrorDIV, "MOD divide by zero")
	}
	trunc, rem := math.Modf(number.Number / divisor.Number)
	if rem < 0 {
		trunc--
	}
	return newNumberFormulaArg(number.Number - divisor.Number*trunc)
}

// MROUND function rounds a supplied number up or down to the nearest multiple
// of a given number. The syntax of the function is:
//
//    MROUND(number,multiple)
//
func (fn *formulaFuncs) MROUND(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "MROUND requires 2 numeric arguments")
	}
	n := argsList.Front().Value.(formulaArg).ToNumber()
	if n.Type == ArgError {
		return n
	}
	multiple := argsList.Back().Value.(formulaArg).ToNumber()
	if multiple.Type == ArgError {
		return multiple
	}
	if multiple.Number == 0 {
		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
	}
	if multiple.Number < 0 && n.Number > 0 ||
		multiple.Number > 0 && n.Number < 0 {
		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
	}
	number, res := math.Modf(n.Number / multiple.Number)
	if math.Trunc(res+0.5) > 0 {
		number++
	}
	return newNumberFormulaArg(number * multiple.Number)
}

// MULTINOMIAL function calculates the ratio of the factorial of a sum of
// supplied values to the product of factorials of those values. The syntax of
// the function is:
//
//    MULTINOMIAL(number1,[number2],...)
//
func (fn *formulaFuncs) MULTINOMIAL(argsList *list.List) formulaArg {
	val, num, denom := 0.0, 0.0, 1.0
	var err error
	for arg := argsList.Front(); arg != nil; arg = arg.Next() {
		token := arg.Value.(formulaArg)
		switch token.Type {
		case ArgString:
			if token.String == "" {
				continue
			}
			if val, err = strconv.ParseFloat(token.String, 64); err != nil {
				return newErrorFormulaArg(formulaErrorVALUE, err.Error())
			}
			break
		case ArgNumber:
			val = token.Number
			break
		}
		num += val
		denom *= fact(val)
	}
	return newNumberFormulaArg(fact(num) / denom)
}

// MUNIT function returns the unit matrix for a specified dimension. The
// syntax of the function is:
//
//   MUNIT(dimension)
//
func (fn *formulaFuncs) MUNIT(argsList *list.List) (result formulaArg) {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "MUNIT requires 1 numeric argument")
	}
	dimension := argsList.Back().Value.(formulaArg).ToNumber()
	if dimension.Type == ArgError || dimension.Number < 0 {
		return newErrorFormulaArg(formulaErrorVALUE, dimension.Error)
	}
	matrix := make([][]formulaArg, 0, int(dimension.Number))
	for i := 0; i < int(dimension.Number); i++ {
		row := make([]formulaArg, int(dimension.Number))
		for j := 0; j < int(dimension.Number); j++ {
			if i == j {
				row[j] = newNumberFormulaArg(1.0)
			} else {
				row[j] = newNumberFormulaArg(0.0)
			}
		}
		matrix = append(matrix, row)
	}
	return newMatrixFormulaArg(matrix)
}

// ODD function ounds a supplied number away from zero (i.e. rounds a positive
// number up and a negative number down), to the next odd number. The syntax
// of the function is:
//
//   ODD(number)
//
func (fn *formulaFuncs) ODD(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ODD requires 1 numeric argument")
	}
	number := argsList.Back().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	if number.Number == 0 {
		return newNumberFormulaArg(1)
	}
	sign := math.Signbit(number.Number)
	m, frac := math.Modf((number.Number - 1) / 2)
	val := m*2 + 1
	if frac != 0 {
		if !sign {
			val += 2
		} else {
			val -= 2
		}
	}
	return newNumberFormulaArg(val)
}

// PI function returns the value of the mathematical constant π (pi), accurate
// to 15 digits (14 decimal places). The syntax of the function is:
//
//   PI()
//
func (fn *formulaFuncs) PI(argsList *list.List) formulaArg {
	if argsList.Len() != 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "PI accepts no arguments")
	}
	return newNumberFormulaArg(math.Pi)
}

// POWER function calculates a given number, raised to a supplied power.
// The syntax of the function is:
//
//    POWER(number,power)
//
func (fn *formulaFuncs) POWER(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "POWER requires 2 numeric arguments")
	}
	x := argsList.Front().Value.(formulaArg).ToNumber()
	if x.Type == ArgError {
		return x
	}
	y := argsList.Back().Value.(formulaArg).ToNumber()
	if y.Type == ArgError {
		return y
	}
	if x.Number == 0 && y.Number == 0 {
		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
	}
	if x.Number == 0 && y.Number < 0 {
		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
	}
	return newNumberFormulaArg(math.Pow(x.Number, y.Number))
}

// PRODUCT function returns the product (multiplication) of a supplied set of
// numerical values. The syntax of the function is:
//
//    PRODUCT(number1,[number2],...)
//
func (fn *formulaFuncs) PRODUCT(argsList *list.List) formulaArg {
	val, product := 0.0, 1.0
	var err error
	for arg := argsList.Front(); arg != nil; arg = arg.Next() {
		token := arg.Value.(formulaArg)
		switch token.Type {
		case ArgUnknown:
			continue
		case ArgString:
			if token.String == "" {
				continue
			}
			if val, err = strconv.ParseFloat(token.String, 64); err != nil {
				return newErrorFormulaArg(formulaErrorVALUE, err.Error())
			}
			product = product * val
			break
		case ArgNumber:
			product = product * token.Number
			break
		case ArgMatrix:
			for _, row := range token.Matrix {
				for _, value := range row {
					if value.String == "" {
						continue
					}
					if val, err = strconv.ParseFloat(value.String, 64); err != nil {
						return newErrorFormulaArg(formulaErrorVALUE, err.Error())
					}
					product = product * val
				}
			}
		}
	}
	return newNumberFormulaArg(product)
}

// QUOTIENT function returns the integer portion of a division between two
// supplied numbers. The syntax of the function is:
//
//   QUOTIENT(numerator,denominator)
//
func (fn *formulaFuncs) QUOTIENT(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "QUOTIENT requires 2 numeric arguments")
	}
	x := argsList.Front().Value.(formulaArg).ToNumber()
	if x.Type == ArgError {
		return x
	}
	y := argsList.Back().Value.(formulaArg).ToNumber()
	if y.Type == ArgError {
		return y
	}
	if y.Number == 0 {
		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
	}
	return newNumberFormulaArg(math.Trunc(x.Number / y.Number))
}

// RADIANS function converts radians into degrees. The syntax of the function is:
//
//   RADIANS(angle)
//
func (fn *formulaFuncs) RADIANS(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "RADIANS requires 1 numeric argument")
	}
	angle := argsList.Front().Value.(formulaArg).ToNumber()
	if angle.Type == ArgError {
		return angle
	}
	return newNumberFormulaArg(math.Pi / 180.0 * angle.Number)
}

// RAND function generates a random real number between 0 and 1. The syntax of
// the function is:
//
//   RAND()
//
func (fn *formulaFuncs) RAND(argsList *list.List) formulaArg {
	if argsList.Len() != 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "RAND accepts no arguments")
	}
	return newNumberFormulaArg(rand.New(rand.NewSource(time.Now().UnixNano())).Float64())
}

// RANDBETWEEN function generates a random integer between two supplied
// integers. The syntax of the function is:
//
//   RANDBETWEEN(bottom,top)
//
func (fn *formulaFuncs) RANDBETWEEN(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "RANDBETWEEN requires 2 numeric arguments")
	}
	bottom := argsList.Front().Value.(formulaArg).ToNumber()
	if bottom.Type == ArgError {
		return bottom
	}
	top := argsList.Back().Value.(formulaArg).ToNumber()
	if top.Type == ArgError {
		return top
	}
	if top.Number < bottom.Number {
		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
	}
	return newNumberFormulaArg(float64(rand.New(rand.NewSource(time.Now().UnixNano())).Int63n(int64(top.Number-bottom.Number+1)) + int64(bottom.Number)))
}

// romanNumerals defined a numeral system that originated in ancient Rome and
// remained the usual way of writing numbers throughout Europe well into the
// Late Middle Ages.
type romanNumerals struct {
	n float64
	s string
}

var romanTable = [][]romanNumerals{{{1000, "M"}, {900, "CM"}, {500, "D"}, {400, "CD"}, {100, "C"}, {90, "XC"}, {50, "L"}, {40, "XL"}, {10, "X"}, {9, "IX"}, {5, "V"}, {4, "IV"}, {1, "I"}},
	{{1000, "M"}, {950, "LM"}, {900, "CM"}, {500, "D"}, {450, "LD"}, {400, "CD"}, {100, "C"}, {95, "VC"}, {90, "XC"}, {50, "L"}, {45, "VL"}, {40, "XL"}, {10, "X"}, {9, "IX"}, {5, "V"}, {4, "IV"}, {1, "I"}},
	{{1000, "M"}, {990, "XM"}, {950, "LM"}, {900, "CM"}, {500, "D"}, {490, "XD"}, {450, "LD"}, {400, "CD"}, {100, "C"}, {99, "IC"}, {90, "XC"}, {50, "L"}, {45, "VL"}, {40, "XL"}, {10, "X"}, {9, "IX"}, {5, "V"}, {4, "IV"}, {1, "I"}},
	{{1000, "M"}, {995, "VM"}, {990, "XM"}, {950, "LM"}, {900, "CM"}, {500, "D"}, {495, "VD"}, {490, "XD"}, {450, "LD"}, {400, "CD"}, {100, "C"}, {99, "IC"}, {90, "XC"}, {50, "L"}, {45, "VL"}, {40, "XL"}, {10, "X"}, {9, "IX"}, {5, "V"}, {4, "IV"}, {1, "I"}},
	{{1000, "M"}, {999, "IM"}, {995, "VM"}, {990, "XM"}, {950, "LM"}, {900, "CM"}, {500, "D"}, {499, "ID"}, {495, "VD"}, {490, "XD"}, {450, "LD"}, {400, "CD"}, {100, "C"}, {99, "IC"}, {90, "XC"}, {50, "L"}, {45, "VL"}, {40, "XL"}, {10, "X"}, {9, "IX"}, {5, "V"}, {4, "IV"}, {1, "I"}}}

// ROMAN function converts an arabic number to Roman. I.e. for a supplied
// integer, the function returns a text string depicting the roman numeral
// form of the number. The syntax of the function is:
//
//   ROMAN(number,[form])
//
func (fn *formulaFuncs) ROMAN(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "ROMAN requires at least 1 argument")
	}
	if argsList.Len() > 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "ROMAN allows at most 2 arguments")
	}
	var form int
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	if argsList.Len() > 1 {
		f := argsList.Back().Value.(formulaArg).ToNumber()
		if f.Type == ArgError {
			return f
		}
		form = int(f.Number)
		if form < 0 {
			form = 0
		} else if form > 4 {
			form = 4
		}
	}
	decimalTable := romanTable[0]
	switch form {
	case 1:
		decimalTable = romanTable[1]
	case 2:
		decimalTable = romanTable[2]
	case 3:
		decimalTable = romanTable[3]
	case 4:
		decimalTable = romanTable[4]
	}
	val := math.Trunc(number.Number)
	buf := bytes.Buffer{}
	for _, r := range decimalTable {
		for val >= r.n {
			buf.WriteString(r.s)
			val -= r.n
		}
	}
	return newStringFormulaArg(buf.String())
}

type roundMode byte

const (
	closest roundMode = iota
	down
	up
)

// round rounds a supplied number up or down.
func (fn *formulaFuncs) round(number, digits float64, mode roundMode) float64 {
	var significance float64
	if digits > 0 {
		significance = math.Pow(1/10.0, digits)
	} else {
		significance = math.Pow(10.0, -digits)
	}
	val, res := math.Modf(number / significance)
	switch mode {
	case closest:
		const eps = 0.499999999
		if res >= eps {
			val++
		} else if res <= -eps {
			val--
		}
	case down:
	case up:
		if res > 0 {
			val++
		} else if res < 0 {
			val--
		}
	}
	return val * significance
}

// ROUND function rounds a supplied number up or down, to a specified number
// of decimal places. The syntax of the function is:
//
//   ROUND(number,num_digits)
//
func (fn *formulaFuncs) ROUND(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "ROUND requires 2 numeric arguments")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	digits := argsList.Back().Value.(formulaArg).ToNumber()
	if digits.Type == ArgError {
		return digits
	}
	return newNumberFormulaArg(fn.round(number.Number, digits.Number, closest))
}

// ROUNDDOWN function rounds a supplied number down towards zero, to a
// specified number of decimal places. The syntax of the function is:
//
//   ROUNDDOWN(number,num_digits)
//
func (fn *formulaFuncs) ROUNDDOWN(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "ROUNDDOWN requires 2 numeric arguments")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	digits := argsList.Back().Value.(formulaArg).ToNumber()
	if digits.Type == ArgError {
		return digits
	}
	return newNumberFormulaArg(fn.round(number.Number, digits.Number, down))
}

// ROUNDUP function rounds a supplied number up, away from zero, to a
// specified number of decimal places. The syntax of the function is:
//
//   ROUNDUP(number,num_digits)
//
func (fn *formulaFuncs) ROUNDUP(argsList *list.List) formulaArg {
	if argsList.Len() != 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "ROUNDUP requires 2 numeric arguments")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	digits := argsList.Back().Value.(formulaArg).ToNumber()
	if digits.Type == ArgError {
		return digits
	}
	return newNumberFormulaArg(fn.round(number.Number, digits.Number, up))
}

// SEC function calculates the secant of a given angle. The syntax of the
// function is:
//
//    SEC(number)
//
func (fn *formulaFuncs) SEC(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "SEC requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	return newNumberFormulaArg(math.Cos(number.Number))
}

// SECH function calculates the hyperbolic secant (sech) of a supplied angle.
// The syntax of the function is:
//
//    SECH(number)
//
func (fn *formulaFuncs) SECH(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "SECH requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	return newNumberFormulaArg(1 / math.Cosh(number.Number))
}

// SIGN function returns the arithmetic sign (+1, -1 or 0) of a supplied
// number. I.e. if the number is positive, the Sign function returns +1, if
// the number is negative, the function returns -1 and if the number is 0
// (zero), the function returns 0. The syntax of the function is:
//
//   SIGN(number)
//
func (fn *formulaFuncs) SIGN(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "SIGN requires 1 numeric argument")
	}
	val := argsList.Front().Value.(formulaArg).ToNumber()
	if val.Type == ArgError {
		return val
	}
	if val.Number < 0 {
		return newNumberFormulaArg(-1)
	}
	if val.Number > 0 {
		return newNumberFormulaArg(1)
	}
	return newNumberFormulaArg(0)
}

// SIN function calculates the sine of a given angle. The syntax of the
// function is:
//
//    SIN(number)
//
func (fn *formulaFuncs) SIN(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "SIN requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	return newNumberFormulaArg(math.Sin(number.Number))
}

// SINH function calculates the hyperbolic sine (sinh) of a supplied number.
// The syntax of the function is:
//
//    SINH(number)
//
func (fn *formulaFuncs) SINH(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "SINH requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	return newNumberFormulaArg(math.Sinh(number.Number))
}

// SQRT function calculates the positive square root of a supplied number. The
// syntax of the function is:
//
//    SQRT(number)
//
func (fn *formulaFuncs) SQRT(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "SQRT requires 1 numeric argument")
	}
	value := argsList.Front().Value.(formulaArg).ToNumber()
	if value.Type == ArgError {
		return value
	}
	if value.Number < 0 {
		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
	}
	return newNumberFormulaArg(math.Sqrt(value.Number))
}

// SQRTPI function returns the square root of a supplied number multiplied by
// the mathematical constant, π. The syntax of the function is:
//
//    SQRTPI(number)
//
func (fn *formulaFuncs) SQRTPI(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "SQRTPI requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	return newNumberFormulaArg(math.Sqrt(number.Number * math.Pi))
}

// SUM function adds together a supplied set of numbers and returns the sum of
// these values. The syntax of the function is:
//
//    SUM(number1,[number2],...)
//
func (fn *formulaFuncs) SUM(argsList *list.List) formulaArg {
	var (
		val, sum float64
		err      error
	)
	for arg := argsList.Front(); arg != nil; arg = arg.Next() {
		token := arg.Value.(formulaArg)
		switch token.Type {
		case ArgUnknown:
			continue
		case ArgString:
			if token.String == "" {
				continue
			}
			if val, err = strconv.ParseFloat(token.String, 64); err != nil {
				return newErrorFormulaArg(formulaErrorVALUE, err.Error())
			}
			sum += val
		case ArgNumber:
			sum += token.Number
		case ArgMatrix:
			for _, row := range token.Matrix {
				for _, value := range row {
					if value.String == "" {
						continue
					}
					if val, err = strconv.ParseFloat(value.String, 64); err != nil {
						return newErrorFormulaArg(formulaErrorVALUE, err.Error())
					}
					sum += val
				}
			}
		}
	}
	return newNumberFormulaArg(sum)
}

// SUMIF function finds the values in a supplied array, that satisfy a given
// criteria, and returns the sum of the corresponding values in a second
// supplied array. The syntax of the function is:
//
//    SUMIF(range,criteria,[sum_range])
//
func (fn *formulaFuncs) SUMIF(argsList *list.List) formulaArg {
	if argsList.Len() < 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "SUMIF requires at least 2 argument")
	}
	var criteria = formulaCriteriaParser(argsList.Front().Next().Value.(formulaArg).String)
	var rangeMtx = argsList.Front().Value.(formulaArg).Matrix
	var sumRange [][]formulaArg
	if argsList.Len() == 3 {
		sumRange = argsList.Back().Value.(formulaArg).Matrix
	}
	var sum, val float64
	var err error
	for rowIdx, row := range rangeMtx {
		for colIdx, col := range row {
			var ok bool
			fromVal := col.String
			if col.String == "" {
				continue
			}
			if ok, err = formulaCriteriaEval(fromVal, criteria); err != nil {
				return newErrorFormulaArg(formulaErrorVALUE, err.Error())
			}
			if ok {
				if argsList.Len() == 3 {
					if len(sumRange) <= rowIdx || len(sumRange[rowIdx]) <= colIdx {
						continue
					}
					fromVal = sumRange[rowIdx][colIdx].String
				}
				if val, err = strconv.ParseFloat(fromVal, 64); err != nil {
					return newErrorFormulaArg(formulaErrorVALUE, err.Error())
				}
				sum += val
			}
		}
	}
	return newNumberFormulaArg(sum)
}

// SUMSQ function returns the sum of squares of a supplied set of values. The
// syntax of the function is:
//
//    SUMSQ(number1,[number2],...)
//
func (fn *formulaFuncs) SUMSQ(argsList *list.List) formulaArg {
	var val, sq float64
	var err error
	for arg := argsList.Front(); arg != nil; arg = arg.Next() {
		token := arg.Value.(formulaArg)
		switch token.Type {
		case ArgString:
			if token.String == "" {
				continue
			}
			if val, err = strconv.ParseFloat(token.String, 64); err != nil {
				return newErrorFormulaArg(formulaErrorVALUE, err.Error())
			}
			sq += val * val
			break
		case ArgNumber:
			sq += token.Number
			break
		case ArgMatrix:
			for _, row := range token.Matrix {
				for _, value := range row {
					if value.String == "" {
						continue
					}
					if val, err = strconv.ParseFloat(value.String, 64); err != nil {
						return newErrorFormulaArg(formulaErrorVALUE, err.Error())
					}
					sq += val * val
				}
			}
		}
	}
	return newNumberFormulaArg(sq)
}

// TAN function calculates the tangent of a given angle. The syntax of the
// function is:
//
//    TAN(number)
//
func (fn *formulaFuncs) TAN(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "TAN requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	return newNumberFormulaArg(math.Tan(number.Number))
}

// TANH function calculates the hyperbolic tangent (tanh) of a supplied
// number. The syntax of the function is:
//
//    TANH(number)
//
func (fn *formulaFuncs) TANH(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "TANH requires 1 numeric argument")
	}
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	return newNumberFormulaArg(math.Tanh(number.Number))
}

// TRUNC function truncates a supplied number to a specified number of decimal
// places. The syntax of the function is:
//
//    TRUNC(number,[number_digits])
//
func (fn *formulaFuncs) TRUNC(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "TRUNC requires at least 1 argument")
	}
	var digits, adjust, rtrim float64
	var err error
	number := argsList.Front().Value.(formulaArg).ToNumber()
	if number.Type == ArgError {
		return number
	}
	if argsList.Len() > 1 {
		d := argsList.Back().Value.(formulaArg).ToNumber()
		if d.Type == ArgError {
			return d
		}
		digits = d.Number
		digits = math.Floor(digits)
	}
	adjust = math.Pow(10, digits)
	x := int((math.Abs(number.Number) - math.Abs(float64(int(number.Number)))) * adjust)
	if x != 0 {
		if rtrim, err = strconv.ParseFloat(strings.TrimRight(strconv.Itoa(x), "0"), 64); err != nil {
			return newErrorFormulaArg(formulaErrorVALUE, err.Error())
		}
	}
	if (digits > 0) && (rtrim < adjust/10) {
		return newNumberFormulaArg(number.Number)
	}
	return newNumberFormulaArg(float64(int(number.Number*adjust)) / adjust)
}

// Statistical functions

// COUNTA function returns the number of non-blanks within a supplied set of
// cells or values. The syntax of the function is:
//
//    COUNTA(value1,[value2],...)
//
func (fn *formulaFuncs) COUNTA(argsList *list.List) formulaArg {
	var count int
	for token := argsList.Front(); token != nil; token = token.Next() {
		arg := token.Value.(formulaArg)
		switch arg.Type {
		case ArgString:
			if arg.String != "" {
				count++
			}
		case ArgMatrix:
			for _, row := range arg.Matrix {
				for _, value := range row {
					if value.String != "" {
						count++
					}
				}
			}
		}
	}
	return newStringFormulaArg(fmt.Sprintf("%d", count))
}

// MEDIAN function returns the statistical median (the middle value) of a list
// of supplied numbers. The syntax of the function is:
//
//    MEDIAN(number1,[number2],...)
//
func (fn *formulaFuncs) MEDIAN(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "MEDIAN requires at least 1 argument")
	}
	var values = []float64{}
	var median, digits float64
	var err error
	for token := argsList.Front(); token != nil; token = token.Next() {
		arg := token.Value.(formulaArg)
		switch arg.Type {
		case ArgString:
			if digits, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).String, 64); err != nil {
				return newErrorFormulaArg(formulaErrorVALUE, err.Error())
			}
			values = append(values, digits)
			break
		case ArgNumber:
			values = append(values, arg.Number)
			break
		case ArgMatrix:
			for _, row := range arg.Matrix {
				for _, value := range row {
					if value.String == "" {
						continue
					}
					if digits, err = strconv.ParseFloat(value.String, 64); err != nil {
						return newErrorFormulaArg(formulaErrorVALUE, err.Error())
					}
					values = append(values, digits)
				}
			}
		}
	}
	sort.Float64s(values)
	if len(values)%2 == 0 {
		median = (values[len(values)/2-1] + values[len(values)/2]) / 2
	} else {
		median = values[len(values)/2]
	}
	return newNumberFormulaArg(median)
}

// Information functions

// ISBLANK function tests if a specified cell is blank (empty) and if so,
// returns TRUE; Otherwise the function returns FALSE. The syntax of the
// function is:
//
//    ISBLANK(value)
//
func (fn *formulaFuncs) ISBLANK(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ISBLANK requires 1 argument")
	}
	token := argsList.Front().Value.(formulaArg)
	result := "FALSE"
	switch token.Type {
	case ArgUnknown:
		result = "TRUE"
	case ArgString:
		if token.String == "" {
			result = "TRUE"
		}
	}
	return newStringFormulaArg(result)
}

// ISERR function tests if an initial supplied expression (or value) returns
// any Excel Error, except the #N/A error. If so, the function returns the
// logical value TRUE; If the supplied value is not an error or is the #N/A
// error, the ISERR function returns FALSE. The syntax of the function is:
//
//    ISERR(value)
//
func (fn *formulaFuncs) ISERR(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ISERR requires 1 argument")
	}
	token := argsList.Front().Value.(formulaArg)
	result := "FALSE"
	if token.Type == ArgString {
		for _, errType := range []string{formulaErrorDIV, formulaErrorNAME, formulaErrorNUM, formulaErrorVALUE, formulaErrorREF, formulaErrorNULL, formulaErrorSPILL, formulaErrorCALC, formulaErrorGETTINGDATA} {
			if errType == token.String {
				result = "TRUE"
			}
		}
	}
	return newStringFormulaArg(result)
}

// ISERROR function tests if an initial supplied expression (or value) returns
// an Excel Error, and if so, returns the logical value TRUE; Otherwise the
// function returns FALSE. The syntax of the function is:
//
//    ISERROR(value)
//
func (fn *formulaFuncs) ISERROR(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ISERROR requires 1 argument")
	}
	token := argsList.Front().Value.(formulaArg)
	result := "FALSE"
	if token.Type == ArgString {
		for _, errType := range []string{formulaErrorDIV, formulaErrorNAME, formulaErrorNA, formulaErrorNUM, formulaErrorVALUE, formulaErrorREF, formulaErrorNULL, formulaErrorSPILL, formulaErrorCALC, formulaErrorGETTINGDATA} {
			if errType == token.String {
				result = "TRUE"
			}
		}
	}
	return newStringFormulaArg(result)
}

// ISEVEN function tests if a supplied number (or numeric expression)
// evaluates to an even number, and if so, returns TRUE; Otherwise, the
// function returns FALSE. The syntax of the function is:
//
//    ISEVEN(value)
//
func (fn *formulaFuncs) ISEVEN(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ISEVEN requires 1 argument")
	}
	var (
		token   = argsList.Front().Value.(formulaArg)
		result  = "FALSE"
		numeric int
		err     error
	)
	if token.Type == ArgString {
		if numeric, err = strconv.Atoi(token.String); err != nil {
			return newErrorFormulaArg(formulaErrorVALUE, err.Error())
		}
		if numeric == numeric/2*2 {
			return newStringFormulaArg("TRUE")
		}
	}
	return newStringFormulaArg(result)
}

// ISNA function tests if an initial supplied expression (or value) returns
// the Excel #N/A Error, and if so, returns TRUE; Otherwise the function
// returns FALSE. The syntax of the function is:
//
//    ISNA(value)
//
func (fn *formulaFuncs) ISNA(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ISNA requires 1 argument")
	}
	token := argsList.Front().Value.(formulaArg)
	result := "FALSE"
	if token.Type == ArgString && token.String == formulaErrorNA {
		result = "TRUE"
	}
	return newStringFormulaArg(result)
}

// ISNONTEXT function function tests if a supplied value is text. If not, the
// function returns TRUE; If the supplied value is text, the function returns
// FALSE. The syntax of the function is:
//
//    ISNONTEXT(value)
//
func (fn *formulaFuncs) ISNONTEXT(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ISNONTEXT requires 1 argument")
	}
	token := argsList.Front().Value.(formulaArg)
	result := "TRUE"
	if token.Type == ArgString && token.String != "" {
		result = "FALSE"
	}
	return newStringFormulaArg(result)
}

// ISNUMBER function function tests if a supplied value is a number. If so,
// the function returns TRUE; Otherwise it returns FALSE. The syntax of the
// function is:
//
//    ISNUMBER(value)
//
func (fn *formulaFuncs) ISNUMBER(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ISNUMBER requires 1 argument")
	}
	token, result := argsList.Front().Value.(formulaArg), false
	if token.Type == ArgString && token.String != "" {
		if _, err := strconv.Atoi(token.String); err == nil {
			result = true
		}
	}
	return newBoolFormulaArg(result)
}

// ISODD function tests if a supplied number (or numeric expression) evaluates
// to an odd number, and if so, returns TRUE; Otherwise, the function returns
// FALSE. The syntax of the function is:
//
//    ISODD(value)
//
func (fn *formulaFuncs) ISODD(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "ISODD requires 1 argument")
	}
	var (
		token   = argsList.Front().Value.(formulaArg)
		result  = "FALSE"
		numeric int
		err     error
	)
	if token.Type == ArgString {
		if numeric, err = strconv.Atoi(token.String); err != nil {
			return newErrorFormulaArg(formulaErrorVALUE, err.Error())
		}
		if numeric != numeric/2*2 {
			return newStringFormulaArg("TRUE")
		}
	}
	return newStringFormulaArg(result)
}

// NA function returns the Excel #N/A error. This error message has the
// meaning 'value not available' and is produced when an Excel Formula is
// unable to find a value that it needs. The syntax of the function is:
//
//    NA()
//
func (fn *formulaFuncs) NA(argsList *list.List) formulaArg {
	if argsList.Len() != 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "NA accepts no arguments")
	}
	return newStringFormulaArg(formulaErrorNA)
}

// Logical Functions

// AND function tests a number of supplied conditions and returns TRUE or
// FALSE. The syntax of the function is:
//
//    AND(logical_test1,[logical_test2],...)
//
func (fn *formulaFuncs) AND(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "AND requires at least 1 argument")
	}
	if argsList.Len() > 30 {
		return newErrorFormulaArg(formulaErrorVALUE, "AND accepts at most 30 arguments")
	}
	var (
		and = true
		val float64
		err error
	)
	for arg := argsList.Front(); arg != nil; arg = arg.Next() {
		token := arg.Value.(formulaArg)
		switch token.Type {
		case ArgUnknown:
			continue
		case ArgString:
			if token.String == "TRUE" {
				continue
			}
			if token.String == "FALSE" {
				return newStringFormulaArg(token.String)
			}
			if val, err = strconv.ParseFloat(token.String, 64); err != nil {
				return newErrorFormulaArg(formulaErrorVALUE, err.Error())
			}
			and = and && (val != 0)
		case ArgMatrix:
			// TODO
			return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
		}
	}
	return newBoolFormulaArg(and)
}

// OR function tests a number of supplied conditions and returns either TRUE
// or FALSE. The syntax of the function is:
//
//    OR(logical_test1,[logical_test2],...)
//
func (fn *formulaFuncs) OR(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "OR requires at least 1 argument")
	}
	if argsList.Len() > 30 {
		return newErrorFormulaArg(formulaErrorVALUE, "OR accepts at most 30 arguments")
	}
	var (
		or  bool
		val float64
		err error
	)
	for arg := argsList.Front(); arg != nil; arg = arg.Next() {
		token := arg.Value.(formulaArg)
		switch token.Type {
		case ArgUnknown:
			continue
		case ArgString:
			if token.String == "FALSE" {
				continue
			}
			if token.String == "TRUE" {
				or = true
				continue
			}
			if val, err = strconv.ParseFloat(token.String, 64); err != nil {
				return newErrorFormulaArg(formulaErrorVALUE, err.Error())
			}
			or = val != 0
		case ArgMatrix:
			// TODO
			return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
		}
	}
	return newStringFormulaArg(strings.ToUpper(strconv.FormatBool(or)))
}

// Date and Time Functions

// DATE returns a date, from a user-supplied year, month and day. The syntax
// of the function is:
//
//    DATE(year,month,day)
//
func (fn *formulaFuncs) DATE(argsList *list.List) formulaArg {
	if argsList.Len() != 3 {
		return newErrorFormulaArg(formulaErrorVALUE, "DATE requires 3 number arguments")
	}
	var year, month, day int
	var err error
	if year, err = strconv.Atoi(argsList.Front().Value.(formulaArg).String); err != nil {
		return newErrorFormulaArg(formulaErrorVALUE, "DATE requires 3 number arguments")
	}
	if month, err = strconv.Atoi(argsList.Front().Next().Value.(formulaArg).String); err != nil {
		return newErrorFormulaArg(formulaErrorVALUE, "DATE requires 3 number arguments")
	}
	if day, err = strconv.Atoi(argsList.Back().Value.(formulaArg).String); err != nil {
		return newErrorFormulaArg(formulaErrorVALUE, "DATE requires 3 number arguments")
	}
	d := makeDate(year, time.Month(month), day)
	return newStringFormulaArg(timeFromExcelTime(daysBetween(excelMinTime1900.Unix(), d)+1, false).String())
}

// makeDate return date as a Unix time, the number of seconds elapsed since
// January 1, 1970 UTC.
func makeDate(y int, m time.Month, d int) int64 {
	if y == 1900 && int(m) <= 2 {
		d--
	}
	date := time.Date(y, m, d, 0, 0, 0, 0, time.UTC)
	return date.Unix()
}

// daysBetween return time interval of the given start timestamp and end
// timestamp.
func daysBetween(startDate, endDate int64) float64 {
	return float64(int(0.5 + float64((endDate-startDate)/86400)))
}

// Text Functions

// CLEAN removes all non-printable characters from a supplied text string. The
// syntax of the function is:
//
//    CLEAN(text)
//
func (fn *formulaFuncs) CLEAN(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "CLEAN requires 1 argument")
	}
	b := bytes.Buffer{}
	for _, c := range argsList.Front().Value.(formulaArg).String {
		if c > 31 {
			b.WriteRune(c)
		}
	}
	return newStringFormulaArg(b.String())
}

// LEN returns the length of a supplied text string. The syntax of the
// function is:
//
//    LEN(text)
//
func (fn *formulaFuncs) LEN(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "LEN requires 1 string argument")
	}
	return newStringFormulaArg(strconv.Itoa(len(argsList.Front().Value.(formulaArg).String)))
}

// TRIM removes extra spaces (i.e. all spaces except for single spaces between
// words or characters) from a supplied text string. The syntax of the
// function is:
//
//    TRIM(text)
//
func (fn *formulaFuncs) TRIM(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "TRIM requires 1 argument")
	}
	return newStringFormulaArg(strings.TrimSpace(argsList.Front().Value.(formulaArg).String))
}

// LOWER converts all characters in a supplied text string to lower case. The
// syntax of the function is:
//
//    LOWER(text)
//
func (fn *formulaFuncs) LOWER(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "LOWER requires 1 argument")
	}
	return newStringFormulaArg(strings.ToLower(argsList.Front().Value.(formulaArg).String))
}

// PROPER converts all characters in a supplied text string to proper case
// (i.e. all letters that do not immediately follow another letter are set to
// upper case and all other characters are lower case). The syntax of the
// function is:
//
//    PROPER(text)
//
func (fn *formulaFuncs) PROPER(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "PROPER requires 1 argument")
	}
	buf := bytes.Buffer{}
	isLetter := false
	for _, char := range argsList.Front().Value.(formulaArg).String {
		if !isLetter && unicode.IsLetter(char) {
			buf.WriteRune(unicode.ToUpper(char))
		} else {
			buf.WriteRune(unicode.ToLower(char))
		}
		isLetter = unicode.IsLetter(char)
	}
	return newStringFormulaArg(buf.String())
}

// UPPER converts all characters in a supplied text string to upper case. The
// syntax of the function is:
//
//    UPPER(text)
//
func (fn *formulaFuncs) UPPER(argsList *list.List) formulaArg {
	if argsList.Len() != 1 {
		return newErrorFormulaArg(formulaErrorVALUE, "UPPER requires 1 argument")
	}
	return newStringFormulaArg(strings.ToUpper(argsList.Front().Value.(formulaArg).String))
}

// Conditional Functions

// IF function tests a supplied condition and returns one result if the
// condition evaluates to TRUE, and another result if the condition evaluates
// to FALSE. The syntax of the function is:
//
//    IF(logical_test,value_if_true,value_if_false)
//
func (fn *formulaFuncs) IF(argsList *list.List) formulaArg {
	if argsList.Len() == 0 {
		return newErrorFormulaArg(formulaErrorVALUE, "IF requires at least 1 argument")
	}
	if argsList.Len() > 3 {
		return newErrorFormulaArg(formulaErrorVALUE, "IF accepts at most 3 arguments")
	}
	token := argsList.Front().Value.(formulaArg)
	var (
		cond   bool
		err    error
		result string
	)
	switch token.Type {
	case ArgString:
		if cond, err = strconv.ParseBool(token.String); err != nil {
			return newErrorFormulaArg(formulaErrorVALUE, err.Error())
		}
		if argsList.Len() == 1 {
			return newBoolFormulaArg(cond)
		}
		if cond {
			return newStringFormulaArg(argsList.Front().Next().Value.(formulaArg).String)
		}
		if argsList.Len() == 3 {
			result = argsList.Back().Value.(formulaArg).String
		}
	}
	return newStringFormulaArg(result)
}

// Excel Lookup and Reference Functions

// CHOOSE function returns a value from an array, that corresponds to a
// supplied index number (position). The syntax of the function is:
//
//    CHOOSE(index_num,value1,[value2],...)
//
func (fn *formulaFuncs) CHOOSE(argsList *list.List) formulaArg {
	if argsList.Len() < 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "CHOOSE requires 2 arguments")
	}
	idx, err := strconv.Atoi(argsList.Front().Value.(formulaArg).String)
	if err != nil {
		return newErrorFormulaArg(formulaErrorVALUE, "CHOOSE requires first argument of type number")
	}
	if argsList.Len() <= idx {
		return newErrorFormulaArg(formulaErrorVALUE, "index_num should be <= to the number of values")
	}
	arg := argsList.Front()
	for i := 0; i < idx; i++ {
		arg = arg.Next()
	}
	var result formulaArg
	switch arg.Value.(formulaArg).Type {
	case ArgString:
		result = newStringFormulaArg(arg.Value.(formulaArg).String)
	case ArgMatrix:
		result = newMatrixFormulaArg(arg.Value.(formulaArg).Matrix)
	}
	return result
}

// deepMatchRune finds whether the text deep matches/satisfies the pattern
// string.
func deepMatchRune(str, pattern []rune, simple bool) bool {
	for len(pattern) > 0 {
		switch pattern[0] {
		default:
			if len(str) == 0 || str[0] != pattern[0] {
				return false
			}
		case '?':
			if len(str) == 0 && !simple {
				return false
			}
		case '*':
			return deepMatchRune(str, pattern[1:], simple) ||
				(len(str) > 0 && deepMatchRune(str[1:], pattern, simple))
		}
		str = str[1:]
		pattern = pattern[1:]
	}
	return len(str) == 0 && len(pattern) == 0
}

// matchPattern finds whether the text matches or satisfies the pattern
// string. The pattern supports '*' and '?' wildcards in the pattern string.
func matchPattern(pattern, name string) (matched bool) {
	if pattern == "" {
		return name == pattern
	}
	if pattern == "*" {
		return true
	}
	rname, rpattern := make([]rune, 0, len(name)), make([]rune, 0, len(pattern))
	for _, r := range name {
		rname = append(rname, r)
	}
	for _, r := range pattern {
		rpattern = append(rpattern, r)
	}
	simple := false // Does extended wildcard '*' and '?' match.
	return deepMatchRune(rname, rpattern, simple)
}

// compareFormulaArg compares the left-hand sides and the right-hand sides
// formula arguments by given conditions such as case sensitive, if exact
// match, and make compare result as formula criteria condition type.
func compareFormulaArg(lhs, rhs formulaArg, caseSensitive, exactMatch bool) byte {
	if lhs.Type != rhs.Type {
		return criteriaErr
	}
	switch lhs.Type {
	case ArgNumber:
		if lhs.Number == rhs.Number {
			return criteriaEq
		}
		if lhs.Number < rhs.Number {
			return criteriaL
		}
		return criteriaG
	case ArgString:
		ls, rs := lhs.String, rhs.String
		if !caseSensitive {
			ls, rs = strings.ToLower(ls), strings.ToLower(rs)
		}
		if exactMatch {
			match := matchPattern(rs, ls)
			if match {
				return criteriaEq
			}
			return criteriaG
		}
		switch strings.Compare(ls, rs) {
		case 1:
			return criteriaG
		case -1:
			return criteriaL
		case 0:
			return criteriaEq
		}
		return criteriaErr
	case ArgEmpty:
		return criteriaEq
	case ArgList:
		return compareFormulaArgList(lhs, rhs, caseSensitive, exactMatch)
	case ArgMatrix:
		return compareFormulaArgMatrix(lhs, rhs, caseSensitive, exactMatch)
	}
	return criteriaErr
}

// compareFormulaArgList compares the left-hand sides and the right-hand sides
// list type formula arguments.
func compareFormulaArgList(lhs, rhs formulaArg, caseSensitive, exactMatch bool) byte {
	if len(lhs.List) < len(rhs.List) {
		return criteriaL
	}
	if len(lhs.List) > len(rhs.List) {
		return criteriaG
	}
	for arg := range lhs.List {
		criteria := compareFormulaArg(lhs.List[arg], rhs.List[arg], caseSensitive, exactMatch)
		if criteria != criteriaEq {
			return criteria
		}
	}
	return criteriaEq
}

// compareFormulaArgMatrix compares the left-hand sides and the right-hand sides
// matrix type formula arguments.
func compareFormulaArgMatrix(lhs, rhs formulaArg, caseSensitive, exactMatch bool) byte {
	if len(lhs.Matrix) < len(rhs.Matrix) {
		return criteriaL
	}
	if len(lhs.Matrix) > len(rhs.Matrix) {
		return criteriaG
	}
	for i := range lhs.Matrix {
		left := lhs.Matrix[i]
		right := lhs.Matrix[i]
		if len(left) < len(right) {
			return criteriaL
		}
		if len(left) > len(right) {
			return criteriaG
		}
		for arg := range left {
			criteria := compareFormulaArg(left[arg], right[arg], caseSensitive, exactMatch)
			if criteria != criteriaEq {
				return criteria
			}
		}
	}
	return criteriaEq
}

// HLOOKUP function 'looks up' a given value in the top row of a data array
// (or table), and returns the corresponding value from another row of the
// array. The syntax of the function is:
//
//    HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
//
func (fn *formulaFuncs) HLOOKUP(argsList *list.List) formulaArg {
	if argsList.Len() < 3 {
		return newErrorFormulaArg(formulaErrorVALUE, "HLOOKUP requires at least 3 arguments")
	}
	if argsList.Len() > 4 {
		return newErrorFormulaArg(formulaErrorVALUE, "HLOOKUP requires at most 4 arguments")
	}
	lookupValue := argsList.Front().Value.(formulaArg)
	tableArray := argsList.Front().Next().Value.(formulaArg)
	if tableArray.Type != ArgMatrix {
		return newErrorFormulaArg(formulaErrorVALUE, "HLOOKUP requires second argument of table array")
	}
	rowArg := argsList.Front().Next().Next().Value.(formulaArg).ToNumber()
	if rowArg.Type != ArgNumber {
		return newErrorFormulaArg(formulaErrorVALUE, "HLOOKUP requires numeric row argument")
	}
	rowIdx, matchIdx, wasExact, exactMatch := int(rowArg.Number)-1, -1, false, false
	if argsList.Len() == 4 {
		rangeLookup := argsList.Back().Value.(formulaArg).ToBool()
		if rangeLookup.Type == ArgError {
			return newErrorFormulaArg(formulaErrorVALUE, rangeLookup.Error)
		}
		if rangeLookup.Number == 0 {
			exactMatch = true
		}
	}
	row := tableArray.Matrix[0]
	if exactMatch || len(tableArray.Matrix) == TotalRows {
	start:
		for idx, mtx := range row {
			lhs := mtx
			switch lookupValue.Type {
			case ArgNumber:
				if !lookupValue.Boolean {
					lhs = mtx.ToNumber()
					if lhs.Type == ArgError {
						lhs = mtx
					}
				}
			case ArgMatrix:
				lhs = tableArray
			}
			if compareFormulaArg(lhs, lookupValue, false, exactMatch) == criteriaEq {
				matchIdx = idx
				wasExact = true
				break start
			}
		}
	} else {
		matchIdx, wasExact = hlookupBinarySearch(row, lookupValue)
	}
	if matchIdx == -1 {
		return newErrorFormulaArg(formulaErrorNA, "HLOOKUP no result found")
	}
	if rowIdx < 0 || rowIdx >= len(tableArray.Matrix) {
		return newErrorFormulaArg(formulaErrorNA, "HLOOKUP has invalid row index")
	}
	row = tableArray.Matrix[rowIdx]
	if wasExact || !exactMatch {
		return row[matchIdx]
	}
	return newErrorFormulaArg(formulaErrorNA, "HLOOKUP no result found")
}

// VLOOKUP function 'looks up' a given value in the left-hand column of a
// data array (or table), and returns the corresponding value from another
// column of the array. The syntax of the function is:
//
//    VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
//
func (fn *formulaFuncs) VLOOKUP(argsList *list.List) formulaArg {
	if argsList.Len() < 3 {
		return newErrorFormulaArg(formulaErrorVALUE, "VLOOKUP requires at least 3 arguments")
	}
	if argsList.Len() > 4 {
		return newErrorFormulaArg(formulaErrorVALUE, "VLOOKUP requires at most 4 arguments")
	}
	lookupValue := argsList.Front().Value.(formulaArg)
	tableArray := argsList.Front().Next().Value.(formulaArg)
	if tableArray.Type != ArgMatrix {
		return newErrorFormulaArg(formulaErrorVALUE, "VLOOKUP requires second argument of table array")
	}
	colIdx := argsList.Front().Next().Next().Value.(formulaArg).ToNumber()
	if colIdx.Type != ArgNumber {
		return newErrorFormulaArg(formulaErrorVALUE, "VLOOKUP requires numeric col argument")
	}
	col, matchIdx, wasExact, exactMatch := int(colIdx.Number)-1, -1, false, false
	if argsList.Len() == 4 {
		rangeLookup := argsList.Back().Value.(formulaArg).ToBool()
		if rangeLookup.Type == ArgError {
			return newErrorFormulaArg(formulaErrorVALUE, rangeLookup.Error)
		}
		if rangeLookup.Number == 0 {
			exactMatch = true
		}
	}
	if exactMatch || len(tableArray.Matrix) == TotalRows {
	start:
		for idx, mtx := range tableArray.Matrix {
			lhs := mtx[0]
			switch lookupValue.Type {
			case ArgNumber:
				if !lookupValue.Boolean {
					lhs = mtx[0].ToNumber()
					if lhs.Type == ArgError {
						lhs = mtx[0]
					}
				}
			case ArgMatrix:
				lhs = tableArray
			}
			if compareFormulaArg(lhs, lookupValue, false, exactMatch) == criteriaEq {
				matchIdx = idx
				wasExact = true
				break start
			}
		}
	} else {
		matchIdx, wasExact = vlookupBinarySearch(tableArray, lookupValue)
	}
	if matchIdx == -1 {
		return newErrorFormulaArg(formulaErrorNA, "VLOOKUP no result found")
	}
	mtx := tableArray.Matrix[matchIdx]
	if col < 0 || col >= len(mtx) {
		return newErrorFormulaArg(formulaErrorNA, "VLOOKUP has invalid column index")
	}
	if wasExact || !exactMatch {
		return mtx[col]
	}
	return newErrorFormulaArg(formulaErrorNA, "VLOOKUP no result found")
}

// vlookupBinarySearch finds the position of a target value when range lookup
// is TRUE, if the data of table array can't guarantee be sorted, it will
// return wrong result.
func vlookupBinarySearch(tableArray, lookupValue formulaArg) (matchIdx int, wasExact bool) {
	var low, high, lastMatchIdx int = 0, len(tableArray.Matrix) - 1, -1
	for low <= high {
		var mid int = low + (high-low)/2
		mtx := tableArray.Matrix[mid]
		lhs := mtx[0]
		switch lookupValue.Type {
		case ArgNumber:
			if !lookupValue.Boolean {
				lhs = mtx[0].ToNumber()
				if lhs.Type == ArgError {
					lhs = mtx[0]
				}
			}
		case ArgMatrix:
			lhs = tableArray
		}
		result := compareFormulaArg(lhs, lookupValue, false, false)
		if result == criteriaEq {
			matchIdx, wasExact = mid, true
			return
		} else if result == criteriaG {
			high = mid - 1
		} else if result == criteriaL {
			matchIdx, low = mid, mid+1
			if lhs.Value() != "" {
				lastMatchIdx = matchIdx
			}
		} else {
			return -1, false
		}
	}
	matchIdx, wasExact = lastMatchIdx, true
	return
}

// vlookupBinarySearch finds the position of a target value when range lookup
// is TRUE, if the data of table array can't guarantee be sorted, it will
// return wrong result.
func hlookupBinarySearch(row []formulaArg, lookupValue formulaArg) (matchIdx int, wasExact bool) {
	var low, high, lastMatchIdx int = 0, len(row) - 1, -1
	for low <= high {
		var mid int = low + (high-low)/2
		mtx := row[mid]
		result := compareFormulaArg(mtx, lookupValue, false, false)
		if result == criteriaEq {
			matchIdx, wasExact = mid, true
			return
		} else if result == criteriaG {
			high = mid - 1
		} else if result == criteriaL {
			low, lastMatchIdx = mid+1, mid
		} else {
			return -1, false
		}
	}
	matchIdx, wasExact = lastMatchIdx, true
	return
}

// LOOKUP function performs an approximate match lookup in a one-column or
// one-row range, and returns the corresponding value from another one-column
// or one-row range. The syntax of the function is:
//
//    LOOKUP(lookup_value,lookup_vector,[result_vector])
//
func (fn *formulaFuncs) LOOKUP(argsList *list.List) formulaArg {
	if argsList.Len() < 2 {
		return newErrorFormulaArg(formulaErrorVALUE, "LOOKUP requires at least 2 arguments")
	}
	if argsList.Len() > 3 {
		return newErrorFormulaArg(formulaErrorVALUE, "LOOKUP requires at most 3 arguments")
	}
	lookupValue := argsList.Front().Value.(formulaArg)
	lookupVector := argsList.Front().Next().Value.(formulaArg)
	if lookupVector.Type != ArgMatrix && lookupVector.Type != ArgList {
		return newErrorFormulaArg(formulaErrorVALUE, "LOOKUP requires second argument of table array")
	}
	cols, matchIdx := lookupCol(lookupVector), -1
	for idx, col := range cols {
		lhs := lookupValue
		switch col.Type {
		case ArgNumber:
			lhs = lhs.ToNumber()
			if !col.Boolean {
				if lhs.Type == ArgError {
					lhs = lookupValue
				}
			}
		}
		if compareFormulaArg(lhs, col, false, false) == criteriaEq {
			matchIdx = idx
			break
		}
	}
	column := cols
	if argsList.Len() == 3 {
		column = lookupCol(argsList.Back().Value.(formulaArg))
	}
	if matchIdx < 0 || matchIdx >= len(column) {
		return newErrorFormulaArg(formulaErrorNA, "LOOKUP no result found")
	}
	return column[matchIdx]
}

// lookupCol extract columns for LOOKUP.
func lookupCol(arr formulaArg) []formulaArg {
	col := arr.List
	if arr.Type == ArgMatrix {
		col = nil
		for _, r := range arr.Matrix {
			if len(r) > 0 {
				col = append(col, r[0])
				continue
			}
			col = append(col, newEmptyFormulaArg())
		}
	}
	return col
}