This project has moved. For the latest updates, please go here.

Can you return arrays from ExcelFunction?

Aug 8, 2015 at 1:21 PM
Hi,

I am trying to implement the LINEST function in an Excel Function. I can get the single slope value to work fine. However, when I try to return an array for the statistics option by returning CreateResult(statisticsMatrix, DataType.Enumerable); it isn't working all that well. Should this be possible with the current build? If so, is there an example on how to implement this somewhere?

Thanks for the help,
  • Eric
Below is my entire class:
    internal class LinestFunction : ExcelFunction
    {
        private const int knowYsIndex = 0;
        private const int knowXsIndex = 1;
        private const int interceptIndex = 2;
        private const int statisticsIndex = 3;
        private const int slopeIndex = 0;
        private const int standardDeviationIndex = 1;
        private const int correlationCoefficientIndex = 2;

        public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
        {
            CompileResult result = CreateResult("Error", DataType.ExcelError);
            FunctionArgument[] _arguments = arguments.ToArray();
            ExcelDataProvider.IRangeInfo knownYsArgument = _arguments[knowYsIndex].ValueAsRangeInfo;
            ExcelDataProvider.IRangeInfo  knownXsArgument = _arguments[knowXsIndex].ValueAsRangeInfo;
            if (knownYsArgument != null && knownXsArgument != null)
            {
                double[] xValues = knownXsArgument.Select(cell => cell.ValueDouble).ToArray();
                double[] yValues = knownYsArgument.Select(cell => cell.ValueDouble).ToArray();

                Tuple<double,double> lineFitParameters = SimpleRegression.Fit(xValues, yValues);
                double slope = lineFitParameters.Item2;
                if (!double.IsNaN(slope))
                {
                    FunctionArgument statisticsArgument = _arguments.Length > statisticsIndex ? _arguments[statisticsIndex] : null;
                    if (statisticsArgument != null && (bool)statisticsArgument.Value)
                    {
                        double intercept = lineFitParameters.Item1;
                        double[] yInterpolated = xValues.Select(x => slope * x + intercept).ToArray();

                        double[] statisticMatrix = new double[3];
                        statisticMatrix[slopeIndex] = slope;
                        statisticMatrix[standardDeviationIndex] = 0;
                        statisticMatrix[correlationCoefficientIndex] = Correlation.Pearson(yValues, yInterpolated);
                        result = CreateResult(statisticMatrix, DataType.Enumerable);
                    }
                    else
                        result = CreateResult(slope, DataType.Decimal);
                }
            }
            return result;
        }
    }