.net实现Excel中的几个函数

这几天做一个数值的线性回归分析，用.net实现了几个有关于计算线性回归的几个函数

分位值函数PERCENTILE

算法说明：

//Excel中 percentile(array,p)算法是：

//将数组array从小到大排序，计算(n-1)*p的整数部分为i，小数部分为j，其中n为数组大小，
 //则percentile的值是：(1-j)*array第i+1个数+j*array第i+2个数。
 //例如：{1，3，4，5，6，7，8，9，19，29，39，49，59，69，79，80}计算30%的分位数：
 //(16-1)*30%=4.5,  i= 4,   j =0.5    
 //percentile(a,30%)=(1-0.5)*6+0.5*7=6.5
 //ref http://office.microsoft.com/zh-cn/excel-help/HP005209211.aspx

/// <summary>
        /// Excel中PERCENTILE的算法，求分位值
        /// </summary>
        /// <param name="array">数据数组</param>
        /// <param name="p">要求的分位值的值</param>
        /// <returns></returns>
        public static double PERCENTILE(ArrayList array,double p)
        {
            array.Sort();  //从小到大排序
            double d = (array.Count - 1) * p;
            string s = d.ToString("F");
            int iLen = s.LastIndexOf(".");
            int i=int.Parse(s.Substring(0, iLen));
            string sValue = "0." + s.Substring(iLen + 1, s.Length-iLen-1);
            double j=Double.Parse(sValue);
            double fristNumber = Double.Parse(array[i].ToString());
            double nextNumber = Double.Parse(array[i+1].ToString());
            double valueNumber = (1 - j) * fristNumber + j * nextNumber;
            return Math.Round(valueNumber,4);
        }

 

/// <summary>
        /// 求一个数的对数，保留6位小数
        /// </summary>
        /// <param name="d"></param>
        /// <returns></returns>
        public static double LN(double d)
        {
           
            return Math.Round(Math.Log(d),6);
        }

        /// <summary>
        /// Excel中的SLOPE函数,求斜率，保留6位小数
        /// </summary>
        /// <param name="yArray">y点的数据数组</param>
        /// <param name="xArray">x点的数据数组</param>
        /// <returns></returns>
        public static double SLOPE(double[] yArray, double[] xArray)
        {
            //算法说明
            //先求xArray和yArray的分别平均值
            //然后
            if (xArray == null)
                throw new ArgumentNullException("xArray");
            if (yArray == null)
                throw new ArgumentNullException("yArray");
            if (xArray.Length != yArray.Length)
                throw new ArgumentException("Array Length Mismatch");
            if (xArray.Length < 2)
                throw new ArgumentException("Arrays too short.");

            double n = xArray.Length;
            double sumxy = 0;
            double sumx = 0;
            double sumy = 0;
            double sumx2 = 0;
            for (int i = 0; i < xArray.Length; i++)
            {
                sumx += xArray[i];
                sumy += yArray[i];
            }
            ////求平均值
            sumx = sumx / n;
            sumy = sumy / n;
            for (int i = 0; i < xArray.Length; i++)
            {
                sumxy += (xArray[i]-sumx) * (yArray[i]-sumy);
                sumx2 += (xArray[i]-sumx) * (xArray[i]-sumx);
            }
            return Math.Round(sumxy / sumx2, 6);
        }

        /// <summary>
        /// Excel中的INTERCEPT函数,求截距，保留6位小数
        /// </summary>
        /// <param name="yArray">y点的数据数组</param>
        /// <param name="xArray">x点的数据数组</param>
        /// <returns></returns>
        public static double INTERCEPT(double[] yArray, double[] xArray)
        {
            if (xArray == null)
                throw new ArgumentNullException("xArray");
            if (yArray == null)
                throw new ArgumentNullException("yArray");
            if (xArray.Length != yArray.Length)
                throw new ArgumentException("Array Length Mismatch");
            if (xArray.Length < 2)
                throw new ArgumentException("Arrays too short.");

            double n = xArray.Length;
            double sumxy = 0;
            double sumx = 0;
            double sumy = 0;
            double sumx2 = 0;
            for (int i = 0; i < xArray.Length; i++)
            {
                sumxy += xArray[i] * yArray[i];
                sumx += xArray[i];
                sumy += yArray[i];
                sumx2 += xArray[i] * xArray[i];
            }
            double k = Math.Round(((sumxy - sumx * sumy / n) / (sumx2 - sumx * sumx / n)), 6);  //斜率
            //直线方程：y=kx+b  b=y-kx
            return Math.Round((sumy - k * sumx) / n,6);
        }

        /// <summary>
        /// Excel中的EXP函数,求e的n次幂，保留6位小数
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static double EXP(double value)
        {
            return Math.Round(Math.Exp(value), 6);
        }