.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);
??????? }