Rank function

[segunodu]

Hello Jing, I am trying to implement Excel RANK function using Customize function.

Code:

public static int Rank<T>(T value, IEnumerable<T> data)
{
  return data.OrderByDescending(x => x).ToList().IndexOf(value) + 1;
}

Usage:

int[] data = new[] { 3, 2, 2, 3, 4 };
int rank = Rank(3, data); // returns 2

How do I implement it in Customize function.
Thank you.

[jingwood]

Please refer to https://reogrid.net/document/customize-function/

unvell.ReoGrid.Formula.FormulaExtension.CustomFunctions["RANK"] = (cell, args) =>
  {
    // sample code

    if (args.Length == 0)
    {
      // this function needs at least one argument
      return null;
    }

    return Convert.ToString(args[0]).ToUpper();
  };

Also will be great if you make the function as a standard built-in function of ReoGrid by pushing your change to this repository.

[segunodu]

Thank you very much.

[segunodu]

Hello Jing, I tried to use RANK in the customize function but I am not getting the desired result

FormulaExtension.CustomFunctions["RANK"] = (cell, args) =>
{
  return Rank(cell, args);
};

How do I get the data in the reference cell and rank it in the reference range?

[jingwood]

@segunodu Did you solve this? I am considering to make the RANK function as a built-in function of ReoGrid in next version.

[segunodu]

Hello Jingwood, sorry I have not responded since. I have solved the Rank function issue using a Rank method and creating Customize Rank function.

The method:

	public static double Rank<T>(T value, IEnumerable<T> data)
	{
		return data.OrderByDescending(x => x).ToList().IndexOf(value) + 1;
	}

And the Customize function:

		FormulaExtension.CustomFunctions["RANK"] = (cell, args) =>
		{
			if (args.Length < 1 || !(args[0] is CellPosition || args[1] is RangePosition))
			{
				return null;
			}
			double cellVal = (double)args[0];
			RangePosition rangePos = (RangePosition)args[1];
			List <double> rangeNums = new List<double>();

			cell.Worksheet.IterateCells(rangePos, (r, c, inCell) =>
			{
				double rangeVal;
				
				if (CellUtility.TryGetNumberData(inCell.Data, out rangeVal))
				{
					rangeNums.Add(rangeVal);
				}
				return true;
			});
			double[] rangeArray = rangeNums.ToArray();
			double numPos = Rank(cellVal, rangeArray);
			return numPos;
		};

Thank you very much. I hope this method will be easier to add into the next Reogrid release. If you need help creating other Customize functions, let me know.

Thank you.