EPPlus/FormulaParsing/Excel/Functions/Math/Rank.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml.FormulaParsing.ExpressionGraph;

namespace OfficeOpenXml.FormulaParsing.Excel.Functions.Math
{
    public class Rank : ExcelFunction
    {
        bool _isAvg;
        public Rank(bool isAvg=false)
        {
            _isAvg=isAvg;
        }
        public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
        {
            ValidateArguments(arguments, 2);
            var number = ArgToDecimal(arguments, 0);
            var refer = arguments.ElementAt(1);
            bool asc = false;
            if (arguments.Count() > 2)
            {
                asc = base.ArgToBool(arguments, 2);
            }
            var l = new List<double>();

            foreach (var c in refer.ValueAsRangeInfo)
            {
                var v = Utils.ConvertUtil.GetValueDouble(c.Value, false, true);
                if (!double.IsNaN(v))
                {
                    l.Add(v);
                }
            }
            l.Sort();
            double ix;
            if (asc)
            {
                ix = l.IndexOf(number)+1;
                if(_isAvg)
                {
                    int st = Convert.ToInt32(ix);
                    while (l.Count > st && l[st] == number) st++;
                    if (st > ix) ix = ix + ((st - ix) / 2D);
                }
            }
            else
            {
                ix = l.LastIndexOf(number);
                if (_isAvg)
                {
                    int st = Convert.ToInt32(ix)-1;
                    while (0 <= st && l[st] == number) st--;
                    if (st+1 < ix) ix = ix - ((ix - st - 1) / 2D);
                }
                ix = l.Count - ix;
            }
            if (ix <= 0 || ix>l.Count)
            {
                return new CompileResult(ExcelErrorValue.Create(eErrorType.NA), DataType.ExcelError);
            }
            else
            {
                return CreateResult(ix, DataType.Decimal);
            }
        }
    }
}