0b9aac7c87371616dbebc10fb109471083d7ba8d.svn-base 107 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490249124922493249424952496249724982499250025012502250325042505250625072508250925102511251225132514251525162517251825192520252125222523252425252526252725282529253025312532253325342535253625372538253925402541254225432544254525462547254825492550255125522553255425552556255725582559256025612562256325642565256625672568256925702571257225732574257525762577257825792580258125822583258425852586258725882589259025912592259325942595259625972598259926002601260226032604260526062607260826092610261126122613261426152616261726182619262026212622262326242625262626272628262926302631263226332634263526362637263826392640264126422643264426452646264726482649265026512652265326542655265626572658265926602661266226632664266526662667266826692670267126722673267426752676267726782679268026812682268326842685268626872688268926902691269226932694269526962697269826992700270127022703270427052706270727082709271027112712271327142715271627172718271927202721272227232724272527262727272827292730273127322733273427352736273727382739274027412742274327442745274627472748274927502751275227532754275527562757275827592760276127622763276427652766276727682769277027712772277327742775277627772778277927802781278227832784278527862787278827892790279127922793279427952796279727982799280028012802280328042805280628072808280928102811281228132814281528162817281828192820282128222823282428252826282728282829283028312832283328342835283628372838283928402841284228432844284528462847284828492850285128522853285428552856285728582859286028612862286328642865286628672868286928702871287228732874287528762877287828792880288128822883288428852886288728882889289028912892289328942895289628972898289929002901290229032904290529062907290829092910291129122913291429152916291729182919292029212922292329242925292629272928292929302931293229332934293529362937293829392940294129422943294429452946294729482949295029512952295329542955295629572958295929602961296229632964296529662967296829692970297129722973297429752976297729782979298029812982298329842985298629872988298929902991299229932994299529962997299829993000300130023003300430053006300730083009301030113012301330143015301630173018301930203021302230233024302530263027302830293030303130323033303430353036303730383039304030413042304330443045304630473048304930503051305230533054305530563057305830593060306130623063306430653066306730683069307030713072307330743075307630773078307930803081308230833084308530863087308830893090309130923093309430953096309730983099310031013102310331043105310631073108310931103111311231133114311531163117311831193120312131223123312431253126312731283129313031313132313331343135313631373138313931403141314231433144314531463147314831493150315131523153315431553156315731583159316031613162316331643165316631673168316931703171317231733174317531763177317831793180318131823183318431853186318731883189319031913192319331943195319631973198319932003201320232033204320532063207320832093210321132123213321432153216321732183219322032213222322332243225322632273228322932303231323232333234323532363237323832393240324132423243324432453246324732483249325032513252325332543255325632573258325932603261326232633264326532663267326832693270327132723273327432753276327732783279328032813282328332843285328632873288328932903291329232933294329532963297329832993300330133023303330433053306330733083309331033113312331333143315331633173318331933203321332233233324332533263327332833293330333133323333333433353336333733383339334033413342334333443345334633473348334933503351335233533354335533563357335833593360336133623363336433653366336733683369337033713372337333743375337633773378337933803381338233833384338533863387338833893390339133923393339433953396339733983399340034013402340334043405340634073408340934103411341234133414341534163417341834193420342134223423342434253426342734283429343034313432343334343435343634373438343934403441344234433444344534463447344834493450345134523453345434553456345734583459346034613462346334643465346634673468346934703471347234733474347534763477347834793480348134823483348434853486348734883489349034913492349334943495349634973498349935003501350235033504350535063507350835093510351135123513351435153516351735183519352035213522352335243525352635273528352935303531353235333534353535363537353835393540354135423543354435453546354735483549355035513552355335543555355635573558355935603561356235633564356535663567356835693570357135723573357435753576357735783579358035813582358335843585358635873588358935903591359235933594359535963597359835993600360136023603360436053606360736083609361036113612361336143615361636173618361936203621362236233624362536263627362836293630363136323633363436353636363736383639364036413642364336443645364636473648364936503651
  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2014 PHPExcel
  6. *
  7. * This library is free software; you can redistribute it and/or
  8. * modify it under the terms of the GNU Lesser General Public
  9. * License as published by the Free Software Foundation; either
  10. * version 2.1 of the License, or (at your option) any later version.
  11. *
  12. * This library is distributed in the hope that it will be useful,
  13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  15. * Lesser General Public License for more details.
  16. *
  17. * You should have received a copy of the GNU Lesser General Public
  18. * License along with this library; if not, write to the Free Software
  19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  20. *
  21. * @category PHPExcel
  22. * @package PHPExcel_Calculation
  23. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version ##VERSION##, ##DATE##
  26. */
  27. /** PHPExcel root directory */
  28. if (!defined('PHPEXCEL_ROOT')) {
  29. /**
  30. * @ignore
  31. */
  32. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
  33. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  34. }
  35. require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/trend/trendClass.php';
  36. /** LOG_GAMMA_X_MAX_VALUE */
  37. define('LOG_GAMMA_X_MAX_VALUE', 2.55e305);
  38. /** XMININ */
  39. define('XMININ', 2.23e-308);
  40. /** EPS */
  41. define('EPS', 2.22e-16);
  42. /** SQRT2PI */
  43. define('SQRT2PI', 2.5066282746310005024157652848110452530069867406099);
  44. /**
  45. * PHPExcel_Calculation_Statistical
  46. *
  47. * @category PHPExcel
  48. * @package PHPExcel_Calculation
  49. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  50. */
  51. class PHPExcel_Calculation_Statistical {
  52. private static function _checkTrendArrays(&$array1,&$array2) {
  53. if (!is_array($array1)) { $array1 = array($array1); }
  54. if (!is_array($array2)) { $array2 = array($array2); }
  55. $array1 = PHPExcel_Calculation_Functions::flattenArray($array1);
  56. $array2 = PHPExcel_Calculation_Functions::flattenArray($array2);
  57. foreach($array1 as $key => $value) {
  58. if ((is_bool($value)) || (is_string($value)) || (is_null($value))) {
  59. unset($array1[$key]);
  60. unset($array2[$key]);
  61. }
  62. }
  63. foreach($array2 as $key => $value) {
  64. if ((is_bool($value)) || (is_string($value)) || (is_null($value))) {
  65. unset($array1[$key]);
  66. unset($array2[$key]);
  67. }
  68. }
  69. $array1 = array_merge($array1);
  70. $array2 = array_merge($array2);
  71. return True;
  72. } // function _checkTrendArrays()
  73. /**
  74. * Beta function.
  75. *
  76. * @author Jaco van Kooten
  77. *
  78. * @param p require p>0
  79. * @param q require q>0
  80. * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow
  81. */
  82. private static function _beta($p, $q) {
  83. if ($p <= 0.0 || $q <= 0.0 || ($p + $q) > LOG_GAMMA_X_MAX_VALUE) {
  84. return 0.0;
  85. } else {
  86. return exp(self::_logBeta($p, $q));
  87. }
  88. } // function _beta()
  89. /**
  90. * Incomplete beta function
  91. *
  92. * @author Jaco van Kooten
  93. * @author Paul Meagher
  94. *
  95. * The computation is based on formulas from Numerical Recipes, Chapter 6.4 (W.H. Press et al, 1992).
  96. * @param x require 0<=x<=1
  97. * @param p require p>0
  98. * @param q require q>0
  99. * @return 0 if x<0, p<=0, q<=0 or p+q>2.55E305 and 1 if x>1 to avoid errors and over/underflow
  100. */
  101. private static function _incompleteBeta($x, $p, $q) {
  102. if ($x <= 0.0) {
  103. return 0.0;
  104. } elseif ($x >= 1.0) {
  105. return 1.0;
  106. } elseif (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
  107. return 0.0;
  108. }
  109. $beta_gam = exp((0 - self::_logBeta($p, $q)) + $p * log($x) + $q * log(1.0 - $x));
  110. if ($x < ($p + 1.0) / ($p + $q + 2.0)) {
  111. return $beta_gam * self::_betaFraction($x, $p, $q) / $p;
  112. } else {
  113. return 1.0 - ($beta_gam * self::_betaFraction(1 - $x, $q, $p) / $q);
  114. }
  115. } // function _incompleteBeta()
  116. // Function cache for _logBeta function
  117. private static $_logBetaCache_p = 0.0;
  118. private static $_logBetaCache_q = 0.0;
  119. private static $_logBetaCache_result = 0.0;
  120. /**
  121. * The natural logarithm of the beta function.
  122. *
  123. * @param p require p>0
  124. * @param q require q>0
  125. * @return 0 if p<=0, q<=0 or p+q>2.55E305 to avoid errors and over/underflow
  126. * @author Jaco van Kooten
  127. */
  128. private static function _logBeta($p, $q) {
  129. if ($p != self::$_logBetaCache_p || $q != self::$_logBetaCache_q) {
  130. self::$_logBetaCache_p = $p;
  131. self::$_logBetaCache_q = $q;
  132. if (($p <= 0.0) || ($q <= 0.0) || (($p + $q) > LOG_GAMMA_X_MAX_VALUE)) {
  133. self::$_logBetaCache_result = 0.0;
  134. } else {
  135. self::$_logBetaCache_result = self::_logGamma($p) + self::_logGamma($q) - self::_logGamma($p + $q);
  136. }
  137. }
  138. return self::$_logBetaCache_result;
  139. } // function _logBeta()
  140. /**
  141. * Evaluates of continued fraction part of incomplete beta function.
  142. * Based on an idea from Numerical Recipes (W.H. Press et al, 1992).
  143. * @author Jaco van Kooten
  144. */
  145. private static function _betaFraction($x, $p, $q) {
  146. $c = 1.0;
  147. $sum_pq = $p + $q;
  148. $p_plus = $p + 1.0;
  149. $p_minus = $p - 1.0;
  150. $h = 1.0 - $sum_pq * $x / $p_plus;
  151. if (abs($h) < XMININ) {
  152. $h = XMININ;
  153. }
  154. $h = 1.0 / $h;
  155. $frac = $h;
  156. $m = 1;
  157. $delta = 0.0;
  158. while ($m <= MAX_ITERATIONS && abs($delta-1.0) > PRECISION ) {
  159. $m2 = 2 * $m;
  160. // even index for d
  161. $d = $m * ($q - $m) * $x / ( ($p_minus + $m2) * ($p + $m2));
  162. $h = 1.0 + $d * $h;
  163. if (abs($h) < XMININ) {
  164. $h = XMININ;
  165. }
  166. $h = 1.0 / $h;
  167. $c = 1.0 + $d / $c;
  168. if (abs($c) < XMININ) {
  169. $c = XMININ;
  170. }
  171. $frac *= $h * $c;
  172. // odd index for d
  173. $d = -($p + $m) * ($sum_pq + $m) * $x / (($p + $m2) * ($p_plus + $m2));
  174. $h = 1.0 + $d * $h;
  175. if (abs($h) < XMININ) {
  176. $h = XMININ;
  177. }
  178. $h = 1.0 / $h;
  179. $c = 1.0 + $d / $c;
  180. if (abs($c) < XMININ) {
  181. $c = XMININ;
  182. }
  183. $delta = $h * $c;
  184. $frac *= $delta;
  185. ++$m;
  186. }
  187. return $frac;
  188. } // function _betaFraction()
  189. /**
  190. * logGamma function
  191. *
  192. * @version 1.1
  193. * @author Jaco van Kooten
  194. *
  195. * Original author was Jaco van Kooten. Ported to PHP by Paul Meagher.
  196. *
  197. * The natural logarithm of the gamma function. <br />
  198. * Based on public domain NETLIB (Fortran) code by W. J. Cody and L. Stoltz <br />
  199. * Applied Mathematics Division <br />
  200. * Argonne National Laboratory <br />
  201. * Argonne, IL 60439 <br />
  202. * <p>
  203. * References:
  204. * <ol>
  205. * <li>W. J. Cody and K. E. Hillstrom, 'Chebyshev Approximations for the Natural
  206. * Logarithm of the Gamma Function,' Math. Comp. 21, 1967, pp. 198-203.</li>
  207. * <li>K. E. Hillstrom, ANL/AMD Program ANLC366S, DGAMMA/DLGAMA, May, 1969.</li>
  208. * <li>Hart, Et. Al., Computer Approximations, Wiley and sons, New York, 1968.</li>
  209. * </ol>
  210. * </p>
  211. * <p>
  212. * From the original documentation:
  213. * </p>
  214. * <p>
  215. * This routine calculates the LOG(GAMMA) function for a positive real argument X.
  216. * Computation is based on an algorithm outlined in references 1 and 2.
  217. * The program uses rational functions that theoretically approximate LOG(GAMMA)
  218. * to at least 18 significant decimal digits. The approximation for X > 12 is from
  219. * reference 3, while approximations for X < 12.0 are similar to those in reference
  220. * 1, but are unpublished. The accuracy achieved depends on the arithmetic system,
  221. * the compiler, the intrinsic functions, and proper selection of the
  222. * machine-dependent constants.
  223. * </p>
  224. * <p>
  225. * Error returns: <br />
  226. * The program returns the value XINF for X .LE. 0.0 or when overflow would occur.
  227. * The computation is believed to be free of underflow and overflow.
  228. * </p>
  229. * @return MAX_VALUE for x < 0.0 or when overflow would occur, i.e. x > 2.55E305
  230. */
  231. // Function cache for logGamma
  232. private static $_logGammaCache_result = 0.0;
  233. private static $_logGammaCache_x = 0.0;
  234. private static function _logGamma($x) {
  235. // Log Gamma related constants
  236. static $lg_d1 = -0.5772156649015328605195174;
  237. static $lg_d2 = 0.4227843350984671393993777;
  238. static $lg_d4 = 1.791759469228055000094023;
  239. static $lg_p1 = array( 4.945235359296727046734888,
  240. 201.8112620856775083915565,
  241. 2290.838373831346393026739,
  242. 11319.67205903380828685045,
  243. 28557.24635671635335736389,
  244. 38484.96228443793359990269,
  245. 26377.48787624195437963534,
  246. 7225.813979700288197698961 );
  247. static $lg_p2 = array( 4.974607845568932035012064,
  248. 542.4138599891070494101986,
  249. 15506.93864978364947665077,
  250. 184793.2904445632425417223,
  251. 1088204.76946882876749847,
  252. 3338152.967987029735917223,
  253. 5106661.678927352456275255,
  254. 3074109.054850539556250927 );
  255. static $lg_p4 = array( 14745.02166059939948905062,
  256. 2426813.369486704502836312,
  257. 121475557.4045093227939592,
  258. 2663432449.630976949898078,
  259. 29403789566.34553899906876,
  260. 170266573776.5398868392998,
  261. 492612579337.743088758812,
  262. 560625185622.3951465078242 );
  263. static $lg_q1 = array( 67.48212550303777196073036,
  264. 1113.332393857199323513008,
  265. 7738.757056935398733233834,
  266. 27639.87074403340708898585,
  267. 54993.10206226157329794414,
  268. 61611.22180066002127833352,
  269. 36351.27591501940507276287,
  270. 8785.536302431013170870835 );
  271. static $lg_q2 = array( 183.0328399370592604055942,
  272. 7765.049321445005871323047,
  273. 133190.3827966074194402448,
  274. 1136705.821321969608938755,
  275. 5267964.117437946917577538,
  276. 13467014.54311101692290052,
  277. 17827365.30353274213975932,
  278. 9533095.591844353613395747 );
  279. static $lg_q4 = array( 2690.530175870899333379843,
  280. 639388.5654300092398984238,
  281. 41355999.30241388052042842,
  282. 1120872109.61614794137657,
  283. 14886137286.78813811542398,
  284. 101680358627.2438228077304,
  285. 341747634550.7377132798597,
  286. 446315818741.9713286462081 );
  287. static $lg_c = array( -0.001910444077728,
  288. 8.4171387781295e-4,
  289. -5.952379913043012e-4,
  290. 7.93650793500350248e-4,
  291. -0.002777777777777681622553,
  292. 0.08333333333333333331554247,
  293. 0.0057083835261 );
  294. // Rough estimate of the fourth root of logGamma_xBig
  295. static $lg_frtbig = 2.25e76;
  296. static $pnt68 = 0.6796875;
  297. if ($x == self::$_logGammaCache_x) {
  298. return self::$_logGammaCache_result;
  299. }
  300. $y = $x;
  301. if ($y > 0.0 && $y <= LOG_GAMMA_X_MAX_VALUE) {
  302. if ($y <= EPS) {
  303. $res = -log(y);
  304. } elseif ($y <= 1.5) {
  305. // ---------------------
  306. // EPS .LT. X .LE. 1.5
  307. // ---------------------
  308. if ($y < $pnt68) {
  309. $corr = -log($y);
  310. $xm1 = $y;
  311. } else {
  312. $corr = 0.0;
  313. $xm1 = $y - 1.0;
  314. }
  315. if ($y <= 0.5 || $y >= $pnt68) {
  316. $xden = 1.0;
  317. $xnum = 0.0;
  318. for ($i = 0; $i < 8; ++$i) {
  319. $xnum = $xnum * $xm1 + $lg_p1[$i];
  320. $xden = $xden * $xm1 + $lg_q1[$i];
  321. }
  322. $res = $corr + $xm1 * ($lg_d1 + $xm1 * ($xnum / $xden));
  323. } else {
  324. $xm2 = $y - 1.0;
  325. $xden = 1.0;
  326. $xnum = 0.0;
  327. for ($i = 0; $i < 8; ++$i) {
  328. $xnum = $xnum * $xm2 + $lg_p2[$i];
  329. $xden = $xden * $xm2 + $lg_q2[$i];
  330. }
  331. $res = $corr + $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
  332. }
  333. } elseif ($y <= 4.0) {
  334. // ---------------------
  335. // 1.5 .LT. X .LE. 4.0
  336. // ---------------------
  337. $xm2 = $y - 2.0;
  338. $xden = 1.0;
  339. $xnum = 0.0;
  340. for ($i = 0; $i < 8; ++$i) {
  341. $xnum = $xnum * $xm2 + $lg_p2[$i];
  342. $xden = $xden * $xm2 + $lg_q2[$i];
  343. }
  344. $res = $xm2 * ($lg_d2 + $xm2 * ($xnum / $xden));
  345. } elseif ($y <= 12.0) {
  346. // ----------------------
  347. // 4.0 .LT. X .LE. 12.0
  348. // ----------------------
  349. $xm4 = $y - 4.0;
  350. $xden = -1.0;
  351. $xnum = 0.0;
  352. for ($i = 0; $i < 8; ++$i) {
  353. $xnum = $xnum * $xm4 + $lg_p4[$i];
  354. $xden = $xden * $xm4 + $lg_q4[$i];
  355. }
  356. $res = $lg_d4 + $xm4 * ($xnum / $xden);
  357. } else {
  358. // ---------------------------------
  359. // Evaluate for argument .GE. 12.0
  360. // ---------------------------------
  361. $res = 0.0;
  362. if ($y <= $lg_frtbig) {
  363. $res = $lg_c[6];
  364. $ysq = $y * $y;
  365. for ($i = 0; $i < 6; ++$i)
  366. $res = $res / $ysq + $lg_c[$i];
  367. }
  368. $res /= $y;
  369. $corr = log($y);
  370. $res = $res + log(SQRT2PI) - 0.5 * $corr;
  371. $res += $y * ($corr - 1.0);
  372. }
  373. } else {
  374. // --------------------------
  375. // Return for bad arguments
  376. // --------------------------
  377. $res = MAX_VALUE;
  378. }
  379. // ------------------------------
  380. // Final adjustments and return
  381. // ------------------------------
  382. self::$_logGammaCache_x = $x;
  383. self::$_logGammaCache_result = $res;
  384. return $res;
  385. } // function _logGamma()
  386. //
  387. // Private implementation of the incomplete Gamma function
  388. //
  389. private static function _incompleteGamma($a,$x) {
  390. static $max = 32;
  391. $summer = 0;
  392. for ($n=0; $n<=$max; ++$n) {
  393. $divisor = $a;
  394. for ($i=1; $i<=$n; ++$i) {
  395. $divisor *= ($a + $i);
  396. }
  397. $summer += (pow($x,$n) / $divisor);
  398. }
  399. return pow($x,$a) * exp(0-$x) * $summer;
  400. } // function _incompleteGamma()
  401. //
  402. // Private implementation of the Gamma function
  403. //
  404. private static function _gamma($data) {
  405. if ($data == 0.0) return 0;
  406. static $p0 = 1.000000000190015;
  407. static $p = array ( 1 => 76.18009172947146,
  408. 2 => -86.50532032941677,
  409. 3 => 24.01409824083091,
  410. 4 => -1.231739572450155,
  411. 5 => 1.208650973866179e-3,
  412. 6 => -5.395239384953e-6
  413. );
  414. $y = $x = $data;
  415. $tmp = $x + 5.5;
  416. $tmp -= ($x + 0.5) * log($tmp);
  417. $summer = $p0;
  418. for ($j=1;$j<=6;++$j) {
  419. $summer += ($p[$j] / ++$y);
  420. }
  421. return exp(0 - $tmp + log(SQRT2PI * $summer / $x));
  422. } // function _gamma()
  423. /***************************************************************************
  424. * inverse_ncdf.php
  425. * -------------------
  426. * begin : Friday, January 16, 2004
  427. * copyright : (C) 2004 Michael Nickerson
  428. * email : nickersonm@yahoo.com
  429. *
  430. ***************************************************************************/
  431. private static function _inverse_ncdf($p) {
  432. // Inverse ncdf approximation by Peter J. Acklam, implementation adapted to
  433. // PHP by Michael Nickerson, using Dr. Thomas Ziegler's C implementation as
  434. // a guide. http://home.online.no/~pjacklam/notes/invnorm/index.html
  435. // I have not checked the accuracy of this implementation. Be aware that PHP
  436. // will truncate the coeficcients to 14 digits.
  437. // You have permission to use and distribute this function freely for
  438. // whatever purpose you want, but please show common courtesy and give credit
  439. // where credit is due.
  440. // Input paramater is $p - probability - where 0 < p < 1.
  441. // Coefficients in rational approximations
  442. static $a = array( 1 => -3.969683028665376e+01,
  443. 2 => 2.209460984245205e+02,
  444. 3 => -2.759285104469687e+02,
  445. 4 => 1.383577518672690e+02,
  446. 5 => -3.066479806614716e+01,
  447. 6 => 2.506628277459239e+00
  448. );
  449. static $b = array( 1 => -5.447609879822406e+01,
  450. 2 => 1.615858368580409e+02,
  451. 3 => -1.556989798598866e+02,
  452. 4 => 6.680131188771972e+01,
  453. 5 => -1.328068155288572e+01
  454. );
  455. static $c = array( 1 => -7.784894002430293e-03,
  456. 2 => -3.223964580411365e-01,
  457. 3 => -2.400758277161838e+00,
  458. 4 => -2.549732539343734e+00,
  459. 5 => 4.374664141464968e+00,
  460. 6 => 2.938163982698783e+00
  461. );
  462. static $d = array( 1 => 7.784695709041462e-03,
  463. 2 => 3.224671290700398e-01,
  464. 3 => 2.445134137142996e+00,
  465. 4 => 3.754408661907416e+00
  466. );
  467. // Define lower and upper region break-points.
  468. $p_low = 0.02425; //Use lower region approx. below this
  469. $p_high = 1 - $p_low; //Use upper region approx. above this
  470. if (0 < $p && $p < $p_low) {
  471. // Rational approximation for lower region.
  472. $q = sqrt(-2 * log($p));
  473. return ((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
  474. (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
  475. } elseif ($p_low <= $p && $p <= $p_high) {
  476. // Rational approximation for central region.
  477. $q = $p - 0.5;
  478. $r = $q * $q;
  479. return ((((($a[1] * $r + $a[2]) * $r + $a[3]) * $r + $a[4]) * $r + $a[5]) * $r + $a[6]) * $q /
  480. ((((($b[1] * $r + $b[2]) * $r + $b[3]) * $r + $b[4]) * $r + $b[5]) * $r + 1);
  481. } elseif ($p_high < $p && $p < 1) {
  482. // Rational approximation for upper region.
  483. $q = sqrt(-2 * log(1 - $p));
  484. return -((((($c[1] * $q + $c[2]) * $q + $c[3]) * $q + $c[4]) * $q + $c[5]) * $q + $c[6]) /
  485. (((($d[1] * $q + $d[2]) * $q + $d[3]) * $q + $d[4]) * $q + 1);
  486. }
  487. // If 0 < p < 1, return a null value
  488. return PHPExcel_Calculation_Functions::NULL();
  489. } // function _inverse_ncdf()
  490. private static function _inverse_ncdf2($prob) {
  491. // Approximation of inverse standard normal CDF developed by
  492. // B. Moro, "The Full Monte," Risk 8(2), Feb 1995, 57-58.
  493. $a1 = 2.50662823884;
  494. $a2 = -18.61500062529;
  495. $a3 = 41.39119773534;
  496. $a4 = -25.44106049637;
  497. $b1 = -8.4735109309;
  498. $b2 = 23.08336743743;
  499. $b3 = -21.06224101826;
  500. $b4 = 3.13082909833;
  501. $c1 = 0.337475482272615;
  502. $c2 = 0.976169019091719;
  503. $c3 = 0.160797971491821;
  504. $c4 = 2.76438810333863E-02;
  505. $c5 = 3.8405729373609E-03;
  506. $c6 = 3.951896511919E-04;
  507. $c7 = 3.21767881768E-05;
  508. $c8 = 2.888167364E-07;
  509. $c9 = 3.960315187E-07;
  510. $y = $prob - 0.5;
  511. if (abs($y) < 0.42) {
  512. $z = ($y * $y);
  513. $z = $y * ((($a4 * $z + $a3) * $z + $a2) * $z + $a1) / (((($b4 * $z + $b3) * $z + $b2) * $z + $b1) * $z + 1);
  514. } else {
  515. if ($y > 0) {
  516. $z = log(-log(1 - $prob));
  517. } else {
  518. $z = log(-log($prob));
  519. }
  520. $z = $c1 + $z * ($c2 + $z * ($c3 + $z * ($c4 + $z * ($c5 + $z * ($c6 + $z * ($c7 + $z * ($c8 + $z * $c9)))))));
  521. if ($y < 0) {
  522. $z = -$z;
  523. }
  524. }
  525. return $z;
  526. } // function _inverse_ncdf2()
  527. private static function _inverse_ncdf3($p) {
  528. // ALGORITHM AS241 APPL. STATIST. (1988) VOL. 37, NO. 3.
  529. // Produces the normal deviate Z corresponding to a given lower
  530. // tail area of P; Z is accurate to about 1 part in 10**16.
  531. //
  532. // This is a PHP version of the original FORTRAN code that can
  533. // be found at http://lib.stat.cmu.edu/apstat/
  534. $split1 = 0.425;
  535. $split2 = 5;
  536. $const1 = 0.180625;
  537. $const2 = 1.6;
  538. // coefficients for p close to 0.5
  539. $a0 = 3.3871328727963666080;
  540. $a1 = 1.3314166789178437745E+2;
  541. $a2 = 1.9715909503065514427E+3;
  542. $a3 = 1.3731693765509461125E+4;
  543. $a4 = 4.5921953931549871457E+4;
  544. $a5 = 6.7265770927008700853E+4;
  545. $a6 = 3.3430575583588128105E+4;
  546. $a7 = 2.5090809287301226727E+3;
  547. $b1 = 4.2313330701600911252E+1;
  548. $b2 = 6.8718700749205790830E+2;
  549. $b3 = 5.3941960214247511077E+3;
  550. $b4 = 2.1213794301586595867E+4;
  551. $b5 = 3.9307895800092710610E+4;
  552. $b6 = 2.8729085735721942674E+4;
  553. $b7 = 5.2264952788528545610E+3;
  554. // coefficients for p not close to 0, 0.5 or 1.
  555. $c0 = 1.42343711074968357734;
  556. $c1 = 4.63033784615654529590;
  557. $c2 = 5.76949722146069140550;
  558. $c3 = 3.64784832476320460504;
  559. $c4 = 1.27045825245236838258;
  560. $c5 = 2.41780725177450611770E-1;
  561. $c6 = 2.27238449892691845833E-2;
  562. $c7 = 7.74545014278341407640E-4;
  563. $d1 = 2.05319162663775882187;
  564. $d2 = 1.67638483018380384940;
  565. $d3 = 6.89767334985100004550E-1;
  566. $d4 = 1.48103976427480074590E-1;
  567. $d5 = 1.51986665636164571966E-2;
  568. $d6 = 5.47593808499534494600E-4;
  569. $d7 = 1.05075007164441684324E-9;
  570. // coefficients for p near 0 or 1.
  571. $e0 = 6.65790464350110377720;
  572. $e1 = 5.46378491116411436990;
  573. $e2 = 1.78482653991729133580;
  574. $e3 = 2.96560571828504891230E-1;
  575. $e4 = 2.65321895265761230930E-2;
  576. $e5 = 1.24266094738807843860E-3;
  577. $e6 = 2.71155556874348757815E-5;
  578. $e7 = 2.01033439929228813265E-7;
  579. $f1 = 5.99832206555887937690E-1;
  580. $f2 = 1.36929880922735805310E-1;
  581. $f3 = 1.48753612908506148525E-2;
  582. $f4 = 7.86869131145613259100E-4;
  583. $f5 = 1.84631831751005468180E-5;
  584. $f6 = 1.42151175831644588870E-7;
  585. $f7 = 2.04426310338993978564E-15;
  586. $q = $p - 0.5;
  587. // computation for p close to 0.5
  588. if (abs($q) <= split1) {
  589. $R = $const1 - $q * $q;
  590. $z = $q * ((((((($a7 * $R + $a6) * $R + $a5) * $R + $a4) * $R + $a3) * $R + $a2) * $R + $a1) * $R + $a0) /
  591. ((((((($b7 * $R + $b6) * $R + $b5) * $R + $b4) * $R + $b3) * $R + $b2) * $R + $b1) * $R + 1);
  592. } else {
  593. if ($q < 0) {
  594. $R = $p;
  595. } else {
  596. $R = 1 - $p;
  597. }
  598. $R = pow(-log($R),2);
  599. // computation for p not close to 0, 0.5 or 1.
  600. If ($R <= $split2) {
  601. $R = $R - $const2;
  602. $z = ((((((($c7 * $R + $c6) * $R + $c5) * $R + $c4) * $R + $c3) * $R + $c2) * $R + $c1) * $R + $c0) /
  603. ((((((($d7 * $R + $d6) * $R + $d5) * $R + $d4) * $R + $d3) * $R + $d2) * $R + $d1) * $R + 1);
  604. } else {
  605. // computation for p near 0 or 1.
  606. $R = $R - $split2;
  607. $z = ((((((($e7 * $R + $e6) * $R + $e5) * $R + $e4) * $R + $e3) * $R + $e2) * $R + $e1) * $R + $e0) /
  608. ((((((($f7 * $R + $f6) * $R + $f5) * $R + $f4) * $R + $f3) * $R + $f2) * $R + $f1) * $R + 1);
  609. }
  610. if ($q < 0) {
  611. $z = -$z;
  612. }
  613. }
  614. return $z;
  615. } // function _inverse_ncdf3()
  616. /**
  617. * AVEDEV
  618. *
  619. * Returns the average of the absolute deviations of data points from their mean.
  620. * AVEDEV is a measure of the variability in a data set.
  621. *
  622. * Excel Function:
  623. * AVEDEV(value1[,value2[, ...]])
  624. *
  625. * @access public
  626. * @category Statistical Functions
  627. * @param mixed $arg,... Data values
  628. * @return float
  629. */
  630. public static function AVEDEV() {
  631. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  632. // Return value
  633. $returnValue = null;
  634. $aMean = self::AVERAGE($aArgs);
  635. if ($aMean != PHPExcel_Calculation_Functions::DIV0()) {
  636. $aCount = 0;
  637. foreach ($aArgs as $k => $arg) {
  638. if ((is_bool($arg)) &&
  639. ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
  640. $arg = (integer) $arg;
  641. }
  642. // Is it a numeric value?
  643. if ((is_numeric($arg)) && (!is_string($arg))) {
  644. if (is_null($returnValue)) {
  645. $returnValue = abs($arg - $aMean);
  646. } else {
  647. $returnValue += abs($arg - $aMean);
  648. }
  649. ++$aCount;
  650. }
  651. }
  652. // Return
  653. if ($aCount == 0) {
  654. return PHPExcel_Calculation_Functions::DIV0();
  655. }
  656. return $returnValue / $aCount;
  657. }
  658. return PHPExcel_Calculation_Functions::NaN();
  659. } // function AVEDEV()
  660. /**
  661. * AVERAGE
  662. *
  663. * Returns the average (arithmetic mean) of the arguments
  664. *
  665. * Excel Function:
  666. * AVERAGE(value1[,value2[, ...]])
  667. *
  668. * @access public
  669. * @category Statistical Functions
  670. * @param mixed $arg,... Data values
  671. * @return float
  672. */
  673. public static function AVERAGE() {
  674. $returnValue = $aCount = 0;
  675. // Loop through arguments
  676. foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
  677. if ((is_bool($arg)) &&
  678. ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
  679. $arg = (integer) $arg;
  680. }
  681. // Is it a numeric value?
  682. if ((is_numeric($arg)) && (!is_string($arg))) {
  683. if (is_null($returnValue)) {
  684. $returnValue = $arg;
  685. } else {
  686. $returnValue += $arg;
  687. }
  688. ++$aCount;
  689. }
  690. }
  691. // Return
  692. if ($aCount > 0) {
  693. return $returnValue / $aCount;
  694. } else {
  695. return PHPExcel_Calculation_Functions::DIV0();
  696. }
  697. } // function AVERAGE()
  698. /**
  699. * AVERAGEA
  700. *
  701. * Returns the average of its arguments, including numbers, text, and logical values
  702. *
  703. * Excel Function:
  704. * AVERAGEA(value1[,value2[, ...]])
  705. *
  706. * @access public
  707. * @category Statistical Functions
  708. * @param mixed $arg,... Data values
  709. * @return float
  710. */
  711. public static function AVERAGEA() {
  712. // Return value
  713. $returnValue = null;
  714. $aCount = 0;
  715. // Loop through arguments
  716. foreach (PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args()) as $k => $arg) {
  717. if ((is_bool($arg)) &&
  718. (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
  719. } else {
  720. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
  721. if (is_bool($arg)) {
  722. $arg = (integer) $arg;
  723. } elseif (is_string($arg)) {
  724. $arg = 0;
  725. }
  726. if (is_null($returnValue)) {
  727. $returnValue = $arg;
  728. } else {
  729. $returnValue += $arg;
  730. }
  731. ++$aCount;
  732. }
  733. }
  734. }
  735. // Return
  736. if ($aCount > 0) {
  737. return $returnValue / $aCount;
  738. } else {
  739. return PHPExcel_Calculation_Functions::DIV0();
  740. }
  741. } // function AVERAGEA()
  742. /**
  743. * AVERAGEIF
  744. *
  745. * Returns the average value from a range of cells that contain numbers within the list of arguments
  746. *
  747. * Excel Function:
  748. * AVERAGEIF(value1[,value2[, ...]],condition)
  749. *
  750. * @access public
  751. * @category Mathematical and Trigonometric Functions
  752. * @param mixed $arg,... Data values
  753. * @param string $condition The criteria that defines which cells will be checked.
  754. * @param mixed[] $averageArgs Data values
  755. * @return float
  756. */
  757. public static function AVERAGEIF($aArgs,$condition,$averageArgs = array()) {
  758. // Return value
  759. $returnValue = 0;
  760. $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
  761. $averageArgs = PHPExcel_Calculation_Functions::flattenArray($averageArgs);
  762. if (empty($averageArgs)) {
  763. $averageArgs = $aArgs;
  764. }
  765. $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
  766. // Loop through arguments
  767. $aCount = 0;
  768. foreach ($aArgs as $key => $arg) {
  769. if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
  770. $testCondition = '='.$arg.$condition;
  771. if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
  772. if ((is_null($returnValue)) || ($arg > $returnValue)) {
  773. $returnValue += $arg;
  774. ++$aCount;
  775. }
  776. }
  777. }
  778. // Return
  779. if ($aCount > 0) {
  780. return $returnValue / $aCount;
  781. } else {
  782. return PHPExcel_Calculation_Functions::DIV0();
  783. }
  784. } // function AVERAGEIF()
  785. /**
  786. * BETADIST
  787. *
  788. * Returns the beta distribution.
  789. *
  790. * @param float $value Value at which you want to evaluate the distribution
  791. * @param float $alpha Parameter to the distribution
  792. * @param float $beta Parameter to the distribution
  793. * @param boolean $cumulative
  794. * @return float
  795. *
  796. */
  797. public static function BETADIST($value,$alpha,$beta,$rMin=0,$rMax=1) {
  798. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  799. $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
  800. $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
  801. $rMin = PHPExcel_Calculation_Functions::flattenSingleValue($rMin);
  802. $rMax = PHPExcel_Calculation_Functions::flattenSingleValue($rMax);
  803. if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
  804. if (($value < $rMin) || ($value > $rMax) || ($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax)) {
  805. return PHPExcel_Calculation_Functions::NaN();
  806. }
  807. if ($rMin > $rMax) {
  808. $tmp = $rMin;
  809. $rMin = $rMax;
  810. $rMax = $tmp;
  811. }
  812. $value -= $rMin;
  813. $value /= ($rMax - $rMin);
  814. return self::_incompleteBeta($value,$alpha,$beta);
  815. }
  816. return PHPExcel_Calculation_Functions::VALUE();
  817. } // function BETADIST()
  818. /**
  819. * BETAINV
  820. *
  821. * Returns the inverse of the beta distribution.
  822. *
  823. * @param float $probability Probability at which you want to evaluate the distribution
  824. * @param float $alpha Parameter to the distribution
  825. * @param float $beta Parameter to the distribution
  826. * @param float $rMin Minimum value
  827. * @param float $rMax Maximum value
  828. * @param boolean $cumulative
  829. * @return float
  830. *
  831. */
  832. public static function BETAINV($probability,$alpha,$beta,$rMin=0,$rMax=1) {
  833. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  834. $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
  835. $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
  836. $rMin = PHPExcel_Calculation_Functions::flattenSingleValue($rMin);
  837. $rMax = PHPExcel_Calculation_Functions::flattenSingleValue($rMax);
  838. if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta)) && (is_numeric($rMin)) && (is_numeric($rMax))) {
  839. if (($alpha <= 0) || ($beta <= 0) || ($rMin == $rMax) || ($probability <= 0) || ($probability > 1)) {
  840. return PHPExcel_Calculation_Functions::NaN();
  841. }
  842. if ($rMin > $rMax) {
  843. $tmp = $rMin;
  844. $rMin = $rMax;
  845. $rMax = $tmp;
  846. }
  847. $a = 0;
  848. $b = 2;
  849. $i = 0;
  850. while ((($b - $a) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
  851. $guess = ($a + $b) / 2;
  852. $result = self::BETADIST($guess, $alpha, $beta);
  853. if (($result == $probability) || ($result == 0)) {
  854. $b = $a;
  855. } elseif ($result > $probability) {
  856. $b = $guess;
  857. } else {
  858. $a = $guess;
  859. }
  860. }
  861. if ($i == MAX_ITERATIONS) {
  862. return PHPExcel_Calculation_Functions::NA();
  863. }
  864. return round($rMin + $guess * ($rMax - $rMin),12);
  865. }
  866. return PHPExcel_Calculation_Functions::VALUE();
  867. } // function BETAINV()
  868. /**
  869. * BINOMDIST
  870. *
  871. * Returns the individual term binomial distribution probability. Use BINOMDIST in problems with
  872. * a fixed number of tests or trials, when the outcomes of any trial are only success or failure,
  873. * when trials are independent, and when the probability of success is constant throughout the
  874. * experiment. For example, BINOMDIST can calculate the probability that two of the next three
  875. * babies born are male.
  876. *
  877. * @param float $value Number of successes in trials
  878. * @param float $trials Number of trials
  879. * @param float $probability Probability of success on each trial
  880. * @param boolean $cumulative
  881. * @return float
  882. *
  883. * @todo Cumulative distribution function
  884. *
  885. */
  886. public static function BINOMDIST($value, $trials, $probability, $cumulative) {
  887. $value = floor(PHPExcel_Calculation_Functions::flattenSingleValue($value));
  888. $trials = floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials));
  889. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  890. if ((is_numeric($value)) && (is_numeric($trials)) && (is_numeric($probability))) {
  891. if (($value < 0) || ($value > $trials)) {
  892. return PHPExcel_Calculation_Functions::NaN();
  893. }
  894. if (($probability < 0) || ($probability > 1)) {
  895. return PHPExcel_Calculation_Functions::NaN();
  896. }
  897. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  898. if ($cumulative) {
  899. $summer = 0;
  900. for ($i = 0; $i <= $value; ++$i) {
  901. $summer += PHPExcel_Calculation_MathTrig::COMBIN($trials,$i) * pow($probability,$i) * pow(1 - $probability,$trials - $i);
  902. }
  903. return $summer;
  904. } else {
  905. return PHPExcel_Calculation_MathTrig::COMBIN($trials,$value) * pow($probability,$value) * pow(1 - $probability,$trials - $value) ;
  906. }
  907. }
  908. }
  909. return PHPExcel_Calculation_Functions::VALUE();
  910. } // function BINOMDIST()
  911. /**
  912. * CHIDIST
  913. *
  914. * Returns the one-tailed probability of the chi-squared distribution.
  915. *
  916. * @param float $value Value for the function
  917. * @param float $degrees degrees of freedom
  918. * @return float
  919. */
  920. public static function CHIDIST($value, $degrees) {
  921. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  922. $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
  923. if ((is_numeric($value)) && (is_numeric($degrees))) {
  924. if ($degrees < 1) {
  925. return PHPExcel_Calculation_Functions::NaN();
  926. }
  927. if ($value < 0) {
  928. if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
  929. return 1;
  930. }
  931. return PHPExcel_Calculation_Functions::NaN();
  932. }
  933. return 1 - (self::_incompleteGamma($degrees/2,$value/2) / self::_gamma($degrees/2));
  934. }
  935. return PHPExcel_Calculation_Functions::VALUE();
  936. } // function CHIDIST()
  937. /**
  938. * CHIINV
  939. *
  940. * Returns the one-tailed probability of the chi-squared distribution.
  941. *
  942. * @param float $probability Probability for the function
  943. * @param float $degrees degrees of freedom
  944. * @return float
  945. */
  946. public static function CHIINV($probability, $degrees) {
  947. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  948. $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
  949. if ((is_numeric($probability)) && (is_numeric($degrees))) {
  950. $xLo = 100;
  951. $xHi = 0;
  952. $x = $xNew = 1;
  953. $dx = 1;
  954. $i = 0;
  955. while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
  956. // Apply Newton-Raphson step
  957. $result = self::CHIDIST($x, $degrees);
  958. $error = $result - $probability;
  959. if ($error == 0.0) {
  960. $dx = 0;
  961. } elseif ($error < 0.0) {
  962. $xLo = $x;
  963. } else {
  964. $xHi = $x;
  965. }
  966. // Avoid division by zero
  967. if ($result != 0.0) {
  968. $dx = $error / $result;
  969. $xNew = $x - $dx;
  970. }
  971. // If the NR fails to converge (which for example may be the
  972. // case if the initial guess is too rough) we apply a bisection
  973. // step to determine a more narrow interval around the root.
  974. if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
  975. $xNew = ($xLo + $xHi) / 2;
  976. $dx = $xNew - $x;
  977. }
  978. $x = $xNew;
  979. }
  980. if ($i == MAX_ITERATIONS) {
  981. return PHPExcel_Calculation_Functions::NA();
  982. }
  983. return round($x,12);
  984. }
  985. return PHPExcel_Calculation_Functions::VALUE();
  986. } // function CHIINV()
  987. /**
  988. * CONFIDENCE
  989. *
  990. * Returns the confidence interval for a population mean
  991. *
  992. * @param float $alpha
  993. * @param float $stdDev Standard Deviation
  994. * @param float $size
  995. * @return float
  996. *
  997. */
  998. public static function CONFIDENCE($alpha,$stdDev,$size) {
  999. $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
  1000. $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
  1001. $size = floor(PHPExcel_Calculation_Functions::flattenSingleValue($size));
  1002. if ((is_numeric($alpha)) && (is_numeric($stdDev)) && (is_numeric($size))) {
  1003. if (($alpha <= 0) || ($alpha >= 1)) {
  1004. return PHPExcel_Calculation_Functions::NaN();
  1005. }
  1006. if (($stdDev <= 0) || ($size < 1)) {
  1007. return PHPExcel_Calculation_Functions::NaN();
  1008. }
  1009. return self::NORMSINV(1 - $alpha / 2) * $stdDev / sqrt($size);
  1010. }
  1011. return PHPExcel_Calculation_Functions::VALUE();
  1012. } // function CONFIDENCE()
  1013. /**
  1014. * CORREL
  1015. *
  1016. * Returns covariance, the average of the products of deviations for each data point pair.
  1017. *
  1018. * @param array of mixed Data Series Y
  1019. * @param array of mixed Data Series X
  1020. * @return float
  1021. */
  1022. public static function CORREL($yValues,$xValues=null) {
  1023. if ((is_null($xValues)) || (!is_array($yValues)) || (!is_array($xValues))) {
  1024. return PHPExcel_Calculation_Functions::VALUE();
  1025. }
  1026. if (!self::_checkTrendArrays($yValues,$xValues)) {
  1027. return PHPExcel_Calculation_Functions::VALUE();
  1028. }
  1029. $yValueCount = count($yValues);
  1030. $xValueCount = count($xValues);
  1031. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  1032. return PHPExcel_Calculation_Functions::NA();
  1033. } elseif ($yValueCount == 1) {
  1034. return PHPExcel_Calculation_Functions::DIV0();
  1035. }
  1036. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
  1037. return $bestFitLinear->getCorrelation();
  1038. } // function CORREL()
  1039. /**
  1040. * COUNT
  1041. *
  1042. * Counts the number of cells that contain numbers within the list of arguments
  1043. *
  1044. * Excel Function:
  1045. * COUNT(value1[,value2[, ...]])
  1046. *
  1047. * @access public
  1048. * @category Statistical Functions
  1049. * @param mixed $arg,... Data values
  1050. * @return int
  1051. */
  1052. public static function COUNT() {
  1053. // Return value
  1054. $returnValue = 0;
  1055. // Loop through arguments
  1056. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  1057. foreach ($aArgs as $k => $arg) {
  1058. if ((is_bool($arg)) &&
  1059. ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
  1060. $arg = (integer) $arg;
  1061. }
  1062. // Is it a numeric value?
  1063. if ((is_numeric($arg)) && (!is_string($arg))) {
  1064. ++$returnValue;
  1065. }
  1066. }
  1067. // Return
  1068. return $returnValue;
  1069. } // function COUNT()
  1070. /**
  1071. * COUNTA
  1072. *
  1073. * Counts the number of cells that are not empty within the list of arguments
  1074. *
  1075. * Excel Function:
  1076. * COUNTA(value1[,value2[, ...]])
  1077. *
  1078. * @access public
  1079. * @category Statistical Functions
  1080. * @param mixed $arg,... Data values
  1081. * @return int
  1082. */
  1083. public static function COUNTA() {
  1084. // Return value
  1085. $returnValue = 0;
  1086. // Loop through arguments
  1087. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  1088. foreach ($aArgs as $arg) {
  1089. // Is it a numeric, boolean or string value?
  1090. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
  1091. ++$returnValue;
  1092. }
  1093. }
  1094. // Return
  1095. return $returnValue;
  1096. } // function COUNTA()
  1097. /**
  1098. * COUNTBLANK
  1099. *
  1100. * Counts the number of empty cells within the list of arguments
  1101. *
  1102. * Excel Function:
  1103. * COUNTBLANK(value1[,value2[, ...]])
  1104. *
  1105. * @access public
  1106. * @category Statistical Functions
  1107. * @param mixed $arg,... Data values
  1108. * @return int
  1109. */
  1110. public static function COUNTBLANK() {
  1111. // Return value
  1112. $returnValue = 0;
  1113. // Loop through arguments
  1114. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  1115. foreach ($aArgs as $arg) {
  1116. // Is it a blank cell?
  1117. if ((is_null($arg)) || ((is_string($arg)) && ($arg == ''))) {
  1118. ++$returnValue;
  1119. }
  1120. }
  1121. // Return
  1122. return $returnValue;
  1123. } // function COUNTBLANK()
  1124. /**
  1125. * COUNTIF
  1126. *
  1127. * Counts the number of cells that contain numbers within the list of arguments
  1128. *
  1129. * Excel Function:
  1130. * COUNTIF(value1[,value2[, ...]],condition)
  1131. *
  1132. * @access public
  1133. * @category Statistical Functions
  1134. * @param mixed $arg,... Data values
  1135. * @param string $condition The criteria that defines which cells will be counted.
  1136. * @return int
  1137. */
  1138. public static function COUNTIF($aArgs,$condition) {
  1139. // Return value
  1140. $returnValue = 0;
  1141. $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
  1142. $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
  1143. // Loop through arguments
  1144. foreach ($aArgs as $arg) {
  1145. if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
  1146. $testCondition = '='.$arg.$condition;
  1147. if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
  1148. // Is it a value within our criteria
  1149. ++$returnValue;
  1150. }
  1151. }
  1152. // Return
  1153. return $returnValue;
  1154. } // function COUNTIF()
  1155. /**
  1156. * COVAR
  1157. *
  1158. * Returns covariance, the average of the products of deviations for each data point pair.
  1159. *
  1160. * @param array of mixed Data Series Y
  1161. * @param array of mixed Data Series X
  1162. * @return float
  1163. */
  1164. public static function COVAR($yValues,$xValues) {
  1165. if (!self::_checkTrendArrays($yValues,$xValues)) {
  1166. return PHPExcel_Calculation_Functions::VALUE();
  1167. }
  1168. $yValueCount = count($yValues);
  1169. $xValueCount = count($xValues);
  1170. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  1171. return PHPExcel_Calculation_Functions::NA();
  1172. } elseif ($yValueCount == 1) {
  1173. return PHPExcel_Calculation_Functions::DIV0();
  1174. }
  1175. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
  1176. return $bestFitLinear->getCovariance();
  1177. } // function COVAR()
  1178. /**
  1179. * CRITBINOM
  1180. *
  1181. * Returns the smallest value for which the cumulative binomial distribution is greater
  1182. * than or equal to a criterion value
  1183. *
  1184. * See http://support.microsoft.com/kb/828117/ for details of the algorithm used
  1185. *
  1186. * @param float $trials number of Bernoulli trials
  1187. * @param float $probability probability of a success on each trial
  1188. * @param float $alpha criterion value
  1189. * @return int
  1190. *
  1191. * @todo Warning. This implementation differs from the algorithm detailed on the MS
  1192. * web site in that $CumPGuessMinus1 = $CumPGuess - 1 rather than $CumPGuess - $PGuess
  1193. * This eliminates a potential endless loop error, but may have an adverse affect on the
  1194. * accuracy of the function (although all my tests have so far returned correct results).
  1195. *
  1196. */
  1197. public static function CRITBINOM($trials, $probability, $alpha) {
  1198. $trials = floor(PHPExcel_Calculation_Functions::flattenSingleValue($trials));
  1199. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  1200. $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
  1201. if ((is_numeric($trials)) && (is_numeric($probability)) && (is_numeric($alpha))) {
  1202. if ($trials < 0) {
  1203. return PHPExcel_Calculation_Functions::NaN();
  1204. }
  1205. if (($probability < 0) || ($probability > 1)) {
  1206. return PHPExcel_Calculation_Functions::NaN();
  1207. }
  1208. if (($alpha < 0) || ($alpha > 1)) {
  1209. return PHPExcel_Calculation_Functions::NaN();
  1210. }
  1211. if ($alpha <= 0.5) {
  1212. $t = sqrt(log(1 / ($alpha * $alpha)));
  1213. $trialsApprox = 0 - ($t + (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t));
  1214. } else {
  1215. $t = sqrt(log(1 / pow(1 - $alpha,2)));
  1216. $trialsApprox = $t - (2.515517 + 0.802853 * $t + 0.010328 * $t * $t) / (1 + 1.432788 * $t + 0.189269 * $t * $t + 0.001308 * $t * $t * $t);
  1217. }
  1218. $Guess = floor($trials * $probability + $trialsApprox * sqrt($trials * $probability * (1 - $probability)));
  1219. if ($Guess < 0) {
  1220. $Guess = 0;
  1221. } elseif ($Guess > $trials) {
  1222. $Guess = $trials;
  1223. }
  1224. $TotalUnscaledProbability = $UnscaledPGuess = $UnscaledCumPGuess = 0.0;
  1225. $EssentiallyZero = 10e-12;
  1226. $m = floor($trials * $probability);
  1227. ++$TotalUnscaledProbability;
  1228. if ($m == $Guess) { ++$UnscaledPGuess; }
  1229. if ($m <= $Guess) { ++$UnscaledCumPGuess; }
  1230. $PreviousValue = 1;
  1231. $Done = False;
  1232. $k = $m + 1;
  1233. while ((!$Done) && ($k <= $trials)) {
  1234. $CurrentValue = $PreviousValue * ($trials - $k + 1) * $probability / ($k * (1 - $probability));
  1235. $TotalUnscaledProbability += $CurrentValue;
  1236. if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
  1237. if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
  1238. if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
  1239. $PreviousValue = $CurrentValue;
  1240. ++$k;
  1241. }
  1242. $PreviousValue = 1;
  1243. $Done = False;
  1244. $k = $m - 1;
  1245. while ((!$Done) && ($k >= 0)) {
  1246. $CurrentValue = $PreviousValue * $k + 1 * (1 - $probability) / (($trials - $k) * $probability);
  1247. $TotalUnscaledProbability += $CurrentValue;
  1248. if ($k == $Guess) { $UnscaledPGuess += $CurrentValue; }
  1249. if ($k <= $Guess) { $UnscaledCumPGuess += $CurrentValue; }
  1250. if ($CurrentValue <= $EssentiallyZero) { $Done = True; }
  1251. $PreviousValue = $CurrentValue;
  1252. --$k;
  1253. }
  1254. $PGuess = $UnscaledPGuess / $TotalUnscaledProbability;
  1255. $CumPGuess = $UnscaledCumPGuess / $TotalUnscaledProbability;
  1256. // $CumPGuessMinus1 = $CumPGuess - $PGuess;
  1257. $CumPGuessMinus1 = $CumPGuess - 1;
  1258. while (True) {
  1259. if (($CumPGuessMinus1 < $alpha) && ($CumPGuess >= $alpha)) {
  1260. return $Guess;
  1261. } elseif (($CumPGuessMinus1 < $alpha) && ($CumPGuess < $alpha)) {
  1262. $PGuessPlus1 = $PGuess * ($trials - $Guess) * $probability / $Guess / (1 - $probability);
  1263. $CumPGuessMinus1 = $CumPGuess;
  1264. $CumPGuess = $CumPGuess + $PGuessPlus1;
  1265. $PGuess = $PGuessPlus1;
  1266. ++$Guess;
  1267. } elseif (($CumPGuessMinus1 >= $alpha) && ($CumPGuess >= $alpha)) {
  1268. $PGuessMinus1 = $PGuess * $Guess * (1 - $probability) / ($trials - $Guess + 1) / $probability;
  1269. $CumPGuess = $CumPGuessMinus1;
  1270. $CumPGuessMinus1 = $CumPGuessMinus1 - $PGuess;
  1271. $PGuess = $PGuessMinus1;
  1272. --$Guess;
  1273. }
  1274. }
  1275. }
  1276. return PHPExcel_Calculation_Functions::VALUE();
  1277. } // function CRITBINOM()
  1278. /**
  1279. * DEVSQ
  1280. *
  1281. * Returns the sum of squares of deviations of data points from their sample mean.
  1282. *
  1283. * Excel Function:
  1284. * DEVSQ(value1[,value2[, ...]])
  1285. *
  1286. * @access public
  1287. * @category Statistical Functions
  1288. * @param mixed $arg,... Data values
  1289. * @return float
  1290. */
  1291. public static function DEVSQ() {
  1292. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  1293. // Return value
  1294. $returnValue = null;
  1295. $aMean = self::AVERAGE($aArgs);
  1296. if ($aMean != PHPExcel_Calculation_Functions::DIV0()) {
  1297. $aCount = -1;
  1298. foreach ($aArgs as $k => $arg) {
  1299. // Is it a numeric value?
  1300. if ((is_bool($arg)) &&
  1301. ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
  1302. $arg = (integer) $arg;
  1303. }
  1304. if ((is_numeric($arg)) && (!is_string($arg))) {
  1305. if (is_null($returnValue)) {
  1306. $returnValue = pow(($arg - $aMean),2);
  1307. } else {
  1308. $returnValue += pow(($arg - $aMean),2);
  1309. }
  1310. ++$aCount;
  1311. }
  1312. }
  1313. // Return
  1314. if (is_null($returnValue)) {
  1315. return PHPExcel_Calculation_Functions::NaN();
  1316. } else {
  1317. return $returnValue;
  1318. }
  1319. }
  1320. return self::NA();
  1321. } // function DEVSQ()
  1322. /**
  1323. * EXPONDIST
  1324. *
  1325. * Returns the exponential distribution. Use EXPONDIST to model the time between events,
  1326. * such as how long an automated bank teller takes to deliver cash. For example, you can
  1327. * use EXPONDIST to determine the probability that the process takes at most 1 minute.
  1328. *
  1329. * @param float $value Value of the function
  1330. * @param float $lambda The parameter value
  1331. * @param boolean $cumulative
  1332. * @return float
  1333. */
  1334. public static function EXPONDIST($value, $lambda, $cumulative) {
  1335. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  1336. $lambda = PHPExcel_Calculation_Functions::flattenSingleValue($lambda);
  1337. $cumulative = PHPExcel_Calculation_Functions::flattenSingleValue($cumulative);
  1338. if ((is_numeric($value)) && (is_numeric($lambda))) {
  1339. if (($value < 0) || ($lambda < 0)) {
  1340. return PHPExcel_Calculation_Functions::NaN();
  1341. }
  1342. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  1343. if ($cumulative) {
  1344. return 1 - exp(0-$value*$lambda);
  1345. } else {
  1346. return $lambda * exp(0-$value*$lambda);
  1347. }
  1348. }
  1349. }
  1350. return PHPExcel_Calculation_Functions::VALUE();
  1351. } // function EXPONDIST()
  1352. /**
  1353. * FISHER
  1354. *
  1355. * Returns the Fisher transformation at x. This transformation produces a function that
  1356. * is normally distributed rather than skewed. Use this function to perform hypothesis
  1357. * testing on the correlation coefficient.
  1358. *
  1359. * @param float $value
  1360. * @return float
  1361. */
  1362. public static function FISHER($value) {
  1363. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  1364. if (is_numeric($value)) {
  1365. if (($value <= -1) || ($value >= 1)) {
  1366. return PHPExcel_Calculation_Functions::NaN();
  1367. }
  1368. return 0.5 * log((1+$value)/(1-$value));
  1369. }
  1370. return PHPExcel_Calculation_Functions::VALUE();
  1371. } // function FISHER()
  1372. /**
  1373. * FISHERINV
  1374. *
  1375. * Returns the inverse of the Fisher transformation. Use this transformation when
  1376. * analyzing correlations between ranges or arrays of data. If y = FISHER(x), then
  1377. * FISHERINV(y) = x.
  1378. *
  1379. * @param float $value
  1380. * @return float
  1381. */
  1382. public static function FISHERINV($value) {
  1383. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  1384. if (is_numeric($value)) {
  1385. return (exp(2 * $value) - 1) / (exp(2 * $value) + 1);
  1386. }
  1387. return PHPExcel_Calculation_Functions::VALUE();
  1388. } // function FISHERINV()
  1389. /**
  1390. * FORECAST
  1391. *
  1392. * Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value.
  1393. *
  1394. * @param float Value of X for which we want to find Y
  1395. * @param array of mixed Data Series Y
  1396. * @param array of mixed Data Series X
  1397. * @return float
  1398. */
  1399. public static function FORECAST($xValue,$yValues,$xValues) {
  1400. $xValue = PHPExcel_Calculation_Functions::flattenSingleValue($xValue);
  1401. if (!is_numeric($xValue)) {
  1402. return PHPExcel_Calculation_Functions::VALUE();
  1403. }
  1404. if (!self::_checkTrendArrays($yValues,$xValues)) {
  1405. return PHPExcel_Calculation_Functions::VALUE();
  1406. }
  1407. $yValueCount = count($yValues);
  1408. $xValueCount = count($xValues);
  1409. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  1410. return PHPExcel_Calculation_Functions::NA();
  1411. } elseif ($yValueCount == 1) {
  1412. return PHPExcel_Calculation_Functions::DIV0();
  1413. }
  1414. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
  1415. return $bestFitLinear->getValueOfYForX($xValue);
  1416. } // function FORECAST()
  1417. /**
  1418. * GAMMADIST
  1419. *
  1420. * Returns the gamma distribution.
  1421. *
  1422. * @param float $value Value at which you want to evaluate the distribution
  1423. * @param float $a Parameter to the distribution
  1424. * @param float $b Parameter to the distribution
  1425. * @param boolean $cumulative
  1426. * @return float
  1427. *
  1428. */
  1429. public static function GAMMADIST($value,$a,$b,$cumulative) {
  1430. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  1431. $a = PHPExcel_Calculation_Functions::flattenSingleValue($a);
  1432. $b = PHPExcel_Calculation_Functions::flattenSingleValue($b);
  1433. if ((is_numeric($value)) && (is_numeric($a)) && (is_numeric($b))) {
  1434. if (($value < 0) || ($a <= 0) || ($b <= 0)) {
  1435. return PHPExcel_Calculation_Functions::NaN();
  1436. }
  1437. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  1438. if ($cumulative) {
  1439. return self::_incompleteGamma($a,$value / $b) / self::_gamma($a);
  1440. } else {
  1441. return (1 / (pow($b,$a) * self::_gamma($a))) * pow($value,$a-1) * exp(0-($value / $b));
  1442. }
  1443. }
  1444. }
  1445. return PHPExcel_Calculation_Functions::VALUE();
  1446. } // function GAMMADIST()
  1447. /**
  1448. * GAMMAINV
  1449. *
  1450. * Returns the inverse of the beta distribution.
  1451. *
  1452. * @param float $probability Probability at which you want to evaluate the distribution
  1453. * @param float $alpha Parameter to the distribution
  1454. * @param float $beta Parameter to the distribution
  1455. * @return float
  1456. *
  1457. */
  1458. public static function GAMMAINV($probability,$alpha,$beta) {
  1459. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  1460. $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
  1461. $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
  1462. if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) {
  1463. if (($alpha <= 0) || ($beta <= 0) || ($probability < 0) || ($probability > 1)) {
  1464. return PHPExcel_Calculation_Functions::NaN();
  1465. }
  1466. $xLo = 0;
  1467. $xHi = $alpha * $beta * 5;
  1468. $x = $xNew = 1;
  1469. $error = $pdf = 0;
  1470. $dx = 1024;
  1471. $i = 0;
  1472. while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
  1473. // Apply Newton-Raphson step
  1474. $error = self::GAMMADIST($x, $alpha, $beta, True) - $probability;
  1475. if ($error < 0.0) {
  1476. $xLo = $x;
  1477. } else {
  1478. $xHi = $x;
  1479. }
  1480. $pdf = self::GAMMADIST($x, $alpha, $beta, False);
  1481. // Avoid division by zero
  1482. if ($pdf != 0.0) {
  1483. $dx = $error / $pdf;
  1484. $xNew = $x - $dx;
  1485. }
  1486. // If the NR fails to converge (which for example may be the
  1487. // case if the initial guess is too rough) we apply a bisection
  1488. // step to determine a more narrow interval around the root.
  1489. if (($xNew < $xLo) || ($xNew > $xHi) || ($pdf == 0.0)) {
  1490. $xNew = ($xLo + $xHi) / 2;
  1491. $dx = $xNew - $x;
  1492. }
  1493. $x = $xNew;
  1494. }
  1495. if ($i == MAX_ITERATIONS) {
  1496. return PHPExcel_Calculation_Functions::NA();
  1497. }
  1498. return $x;
  1499. }
  1500. return PHPExcel_Calculation_Functions::VALUE();
  1501. } // function GAMMAINV()
  1502. /**
  1503. * GAMMALN
  1504. *
  1505. * Returns the natural logarithm of the gamma function.
  1506. *
  1507. * @param float $value
  1508. * @return float
  1509. */
  1510. public static function GAMMALN($value) {
  1511. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  1512. if (is_numeric($value)) {
  1513. if ($value <= 0) {
  1514. return PHPExcel_Calculation_Functions::NaN();
  1515. }
  1516. return log(self::_gamma($value));
  1517. }
  1518. return PHPExcel_Calculation_Functions::VALUE();
  1519. } // function GAMMALN()
  1520. /**
  1521. * GEOMEAN
  1522. *
  1523. * Returns the geometric mean of an array or range of positive data. For example, you
  1524. * can use GEOMEAN to calculate average growth rate given compound interest with
  1525. * variable rates.
  1526. *
  1527. * Excel Function:
  1528. * GEOMEAN(value1[,value2[, ...]])
  1529. *
  1530. * @access public
  1531. * @category Statistical Functions
  1532. * @param mixed $arg,... Data values
  1533. * @return float
  1534. */
  1535. public static function GEOMEAN() {
  1536. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  1537. $aMean = PHPExcel_Calculation_MathTrig::PRODUCT($aArgs);
  1538. if (is_numeric($aMean) && ($aMean > 0)) {
  1539. $aCount = self::COUNT($aArgs) ;
  1540. if (self::MIN($aArgs) > 0) {
  1541. return pow($aMean, (1 / $aCount));
  1542. }
  1543. }
  1544. return PHPExcel_Calculation_Functions::NaN();
  1545. } // GEOMEAN()
  1546. /**
  1547. * GROWTH
  1548. *
  1549. * Returns values along a predicted emponential trend
  1550. *
  1551. * @param array of mixed Data Series Y
  1552. * @param array of mixed Data Series X
  1553. * @param array of mixed Values of X for which we want to find Y
  1554. * @param boolean A logical value specifying whether to force the intersect to equal 0.
  1555. * @return array of float
  1556. */
  1557. public static function GROWTH($yValues,$xValues=array(),$newValues=array(),$const=True) {
  1558. $yValues = PHPExcel_Calculation_Functions::flattenArray($yValues);
  1559. $xValues = PHPExcel_Calculation_Functions::flattenArray($xValues);
  1560. $newValues = PHPExcel_Calculation_Functions::flattenArray($newValues);
  1561. $const = (is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
  1562. $bestFitExponential = trendClass::calculate(trendClass::TREND_EXPONENTIAL,$yValues,$xValues,$const);
  1563. if (empty($newValues)) {
  1564. $newValues = $bestFitExponential->getXValues();
  1565. }
  1566. $returnArray = array();
  1567. foreach($newValues as $xValue) {
  1568. $returnArray[0][] = $bestFitExponential->getValueOfYForX($xValue);
  1569. }
  1570. return $returnArray;
  1571. } // function GROWTH()
  1572. /**
  1573. * HARMEAN
  1574. *
  1575. * Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the
  1576. * arithmetic mean of reciprocals.
  1577. *
  1578. * Excel Function:
  1579. * HARMEAN(value1[,value2[, ...]])
  1580. *
  1581. * @access public
  1582. * @category Statistical Functions
  1583. * @param mixed $arg,... Data values
  1584. * @return float
  1585. */
  1586. public static function HARMEAN() {
  1587. // Return value
  1588. $returnValue = PHPExcel_Calculation_Functions::NA();
  1589. // Loop through arguments
  1590. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  1591. if (self::MIN($aArgs) < 0) {
  1592. return PHPExcel_Calculation_Functions::NaN();
  1593. }
  1594. $aCount = 0;
  1595. foreach ($aArgs as $arg) {
  1596. // Is it a numeric value?
  1597. if ((is_numeric($arg)) && (!is_string($arg))) {
  1598. if ($arg <= 0) {
  1599. return PHPExcel_Calculation_Functions::NaN();
  1600. }
  1601. if (is_null($returnValue)) {
  1602. $returnValue = (1 / $arg);
  1603. } else {
  1604. $returnValue += (1 / $arg);
  1605. }
  1606. ++$aCount;
  1607. }
  1608. }
  1609. // Return
  1610. if ($aCount > 0) {
  1611. return 1 / ($returnValue / $aCount);
  1612. } else {
  1613. return $returnValue;
  1614. }
  1615. } // function HARMEAN()
  1616. /**
  1617. * HYPGEOMDIST
  1618. *
  1619. * Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of
  1620. * sample successes, given the sample size, population successes, and population size.
  1621. *
  1622. * @param float $sampleSuccesses Number of successes in the sample
  1623. * @param float $sampleNumber Size of the sample
  1624. * @param float $populationSuccesses Number of successes in the population
  1625. * @param float $populationNumber Population size
  1626. * @return float
  1627. *
  1628. */
  1629. public static function HYPGEOMDIST($sampleSuccesses, $sampleNumber, $populationSuccesses, $populationNumber) {
  1630. $sampleSuccesses = floor(PHPExcel_Calculation_Functions::flattenSingleValue($sampleSuccesses));
  1631. $sampleNumber = floor(PHPExcel_Calculation_Functions::flattenSingleValue($sampleNumber));
  1632. $populationSuccesses = floor(PHPExcel_Calculation_Functions::flattenSingleValue($populationSuccesses));
  1633. $populationNumber = floor(PHPExcel_Calculation_Functions::flattenSingleValue($populationNumber));
  1634. if ((is_numeric($sampleSuccesses)) && (is_numeric($sampleNumber)) && (is_numeric($populationSuccesses)) && (is_numeric($populationNumber))) {
  1635. if (($sampleSuccesses < 0) || ($sampleSuccesses > $sampleNumber) || ($sampleSuccesses > $populationSuccesses)) {
  1636. return PHPExcel_Calculation_Functions::NaN();
  1637. }
  1638. if (($sampleNumber <= 0) || ($sampleNumber > $populationNumber)) {
  1639. return PHPExcel_Calculation_Functions::NaN();
  1640. }
  1641. if (($populationSuccesses <= 0) || ($populationSuccesses > $populationNumber)) {
  1642. return PHPExcel_Calculation_Functions::NaN();
  1643. }
  1644. return PHPExcel_Calculation_MathTrig::COMBIN($populationSuccesses,$sampleSuccesses) *
  1645. PHPExcel_Calculation_MathTrig::COMBIN($populationNumber - $populationSuccesses,$sampleNumber - $sampleSuccesses) /
  1646. PHPExcel_Calculation_MathTrig::COMBIN($populationNumber,$sampleNumber);
  1647. }
  1648. return PHPExcel_Calculation_Functions::VALUE();
  1649. } // function HYPGEOMDIST()
  1650. /**
  1651. * INTERCEPT
  1652. *
  1653. * Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values.
  1654. *
  1655. * @param array of mixed Data Series Y
  1656. * @param array of mixed Data Series X
  1657. * @return float
  1658. */
  1659. public static function INTERCEPT($yValues,$xValues) {
  1660. if (!self::_checkTrendArrays($yValues,$xValues)) {
  1661. return PHPExcel_Calculation_Functions::VALUE();
  1662. }
  1663. $yValueCount = count($yValues);
  1664. $xValueCount = count($xValues);
  1665. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  1666. return PHPExcel_Calculation_Functions::NA();
  1667. } elseif ($yValueCount == 1) {
  1668. return PHPExcel_Calculation_Functions::DIV0();
  1669. }
  1670. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
  1671. return $bestFitLinear->getIntersect();
  1672. } // function INTERCEPT()
  1673. /**
  1674. * KURT
  1675. *
  1676. * Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness
  1677. * or flatness of a distribution compared with the normal distribution. Positive
  1678. * kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a
  1679. * relatively flat distribution.
  1680. *
  1681. * @param array Data Series
  1682. * @return float
  1683. */
  1684. public static function KURT() {
  1685. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  1686. $mean = self::AVERAGE($aArgs);
  1687. $stdDev = self::STDEV($aArgs);
  1688. if ($stdDev > 0) {
  1689. $count = $summer = 0;
  1690. // Loop through arguments
  1691. foreach ($aArgs as $k => $arg) {
  1692. if ((is_bool($arg)) &&
  1693. (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
  1694. } else {
  1695. // Is it a numeric value?
  1696. if ((is_numeric($arg)) && (!is_string($arg))) {
  1697. $summer += pow((($arg - $mean) / $stdDev),4) ;
  1698. ++$count;
  1699. }
  1700. }
  1701. }
  1702. // Return
  1703. if ($count > 3) {
  1704. return $summer * ($count * ($count+1) / (($count-1) * ($count-2) * ($count-3))) - (3 * pow($count-1,2) / (($count-2) * ($count-3)));
  1705. }
  1706. }
  1707. return PHPExcel_Calculation_Functions::DIV0();
  1708. } // function KURT()
  1709. /**
  1710. * LARGE
  1711. *
  1712. * Returns the nth largest value in a data set. You can use this function to
  1713. * select a value based on its relative standing.
  1714. *
  1715. * Excel Function:
  1716. * LARGE(value1[,value2[, ...]],entry)
  1717. *
  1718. * @access public
  1719. * @category Statistical Functions
  1720. * @param mixed $arg,... Data values
  1721. * @param int $entry Position (ordered from the largest) in the array or range of data to return
  1722. * @return float
  1723. *
  1724. */
  1725. public static function LARGE() {
  1726. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  1727. // Calculate
  1728. $entry = floor(array_pop($aArgs));
  1729. if ((is_numeric($entry)) && (!is_string($entry))) {
  1730. $mArgs = array();
  1731. foreach ($aArgs as $arg) {
  1732. // Is it a numeric value?
  1733. if ((is_numeric($arg)) && (!is_string($arg))) {
  1734. $mArgs[] = $arg;
  1735. }
  1736. }
  1737. $count = self::COUNT($mArgs);
  1738. $entry = floor(--$entry);
  1739. if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
  1740. return PHPExcel_Calculation_Functions::NaN();
  1741. }
  1742. rsort($mArgs);
  1743. return $mArgs[$entry];
  1744. }
  1745. return PHPExcel_Calculation_Functions::VALUE();
  1746. } // function LARGE()
  1747. /**
  1748. * LINEST
  1749. *
  1750. * Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data,
  1751. * and then returns an array that describes the line.
  1752. *
  1753. * @param array of mixed Data Series Y
  1754. * @param array of mixed Data Series X
  1755. * @param boolean A logical value specifying whether to force the intersect to equal 0.
  1756. * @param boolean A logical value specifying whether to return additional regression statistics.
  1757. * @return array
  1758. */
  1759. public static function LINEST($yValues, $xValues = NULL, $const = TRUE, $stats = FALSE) {
  1760. $const = (is_null($const)) ? TRUE : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
  1761. $stats = (is_null($stats)) ? FALSE : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($stats);
  1762. if (is_null($xValues)) $xValues = range(1,count(PHPExcel_Calculation_Functions::flattenArray($yValues)));
  1763. if (!self::_checkTrendArrays($yValues,$xValues)) {
  1764. return PHPExcel_Calculation_Functions::VALUE();
  1765. }
  1766. $yValueCount = count($yValues);
  1767. $xValueCount = count($xValues);
  1768. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  1769. return PHPExcel_Calculation_Functions::NA();
  1770. } elseif ($yValueCount == 1) {
  1771. return 0;
  1772. }
  1773. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues,$const);
  1774. if ($stats) {
  1775. return array( array( $bestFitLinear->getSlope(),
  1776. $bestFitLinear->getSlopeSE(),
  1777. $bestFitLinear->getGoodnessOfFit(),
  1778. $bestFitLinear->getF(),
  1779. $bestFitLinear->getSSRegression(),
  1780. ),
  1781. array( $bestFitLinear->getIntersect(),
  1782. $bestFitLinear->getIntersectSE(),
  1783. $bestFitLinear->getStdevOfResiduals(),
  1784. $bestFitLinear->getDFResiduals(),
  1785. $bestFitLinear->getSSResiduals()
  1786. )
  1787. );
  1788. } else {
  1789. return array( $bestFitLinear->getSlope(),
  1790. $bestFitLinear->getIntersect()
  1791. );
  1792. }
  1793. } // function LINEST()
  1794. /**
  1795. * LOGEST
  1796. *
  1797. * Calculates an exponential curve that best fits the X and Y data series,
  1798. * and then returns an array that describes the line.
  1799. *
  1800. * @param array of mixed Data Series Y
  1801. * @param array of mixed Data Series X
  1802. * @param boolean A logical value specifying whether to force the intersect to equal 0.
  1803. * @param boolean A logical value specifying whether to return additional regression statistics.
  1804. * @return array
  1805. */
  1806. public static function LOGEST($yValues,$xValues=null,$const=True,$stats=False) {
  1807. $const = (is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
  1808. $stats = (is_null($stats)) ? False : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($stats);
  1809. if (is_null($xValues)) $xValues = range(1,count(PHPExcel_Calculation_Functions::flattenArray($yValues)));
  1810. if (!self::_checkTrendArrays($yValues,$xValues)) {
  1811. return PHPExcel_Calculation_Functions::VALUE();
  1812. }
  1813. $yValueCount = count($yValues);
  1814. $xValueCount = count($xValues);
  1815. foreach($yValues as $value) {
  1816. if ($value <= 0.0) {
  1817. return PHPExcel_Calculation_Functions::NaN();
  1818. }
  1819. }
  1820. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  1821. return PHPExcel_Calculation_Functions::NA();
  1822. } elseif ($yValueCount == 1) {
  1823. return 1;
  1824. }
  1825. $bestFitExponential = trendClass::calculate(trendClass::TREND_EXPONENTIAL,$yValues,$xValues,$const);
  1826. if ($stats) {
  1827. return array( array( $bestFitExponential->getSlope(),
  1828. $bestFitExponential->getSlopeSE(),
  1829. $bestFitExponential->getGoodnessOfFit(),
  1830. $bestFitExponential->getF(),
  1831. $bestFitExponential->getSSRegression(),
  1832. ),
  1833. array( $bestFitExponential->getIntersect(),
  1834. $bestFitExponential->getIntersectSE(),
  1835. $bestFitExponential->getStdevOfResiduals(),
  1836. $bestFitExponential->getDFResiduals(),
  1837. $bestFitExponential->getSSResiduals()
  1838. )
  1839. );
  1840. } else {
  1841. return array( $bestFitExponential->getSlope(),
  1842. $bestFitExponential->getIntersect()
  1843. );
  1844. }
  1845. } // function LOGEST()
  1846. /**
  1847. * LOGINV
  1848. *
  1849. * Returns the inverse of the normal cumulative distribution
  1850. *
  1851. * @param float $probability
  1852. * @param float $mean
  1853. * @param float $stdDev
  1854. * @return float
  1855. *
  1856. * @todo Try implementing P J Acklam's refinement algorithm for greater
  1857. * accuracy if I can get my head round the mathematics
  1858. * (as described at) http://home.online.no/~pjacklam/notes/invnorm/
  1859. */
  1860. public static function LOGINV($probability, $mean, $stdDev) {
  1861. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  1862. $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
  1863. $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
  1864. if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
  1865. if (($probability < 0) || ($probability > 1) || ($stdDev <= 0)) {
  1866. return PHPExcel_Calculation_Functions::NaN();
  1867. }
  1868. return exp($mean + $stdDev * self::NORMSINV($probability));
  1869. }
  1870. return PHPExcel_Calculation_Functions::VALUE();
  1871. } // function LOGINV()
  1872. /**
  1873. * LOGNORMDIST
  1874. *
  1875. * Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed
  1876. * with parameters mean and standard_dev.
  1877. *
  1878. * @param float $value
  1879. * @param float $mean
  1880. * @param float $stdDev
  1881. * @return float
  1882. */
  1883. public static function LOGNORMDIST($value, $mean, $stdDev) {
  1884. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  1885. $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
  1886. $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
  1887. if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
  1888. if (($value <= 0) || ($stdDev <= 0)) {
  1889. return PHPExcel_Calculation_Functions::NaN();
  1890. }
  1891. return self::NORMSDIST((log($value) - $mean) / $stdDev);
  1892. }
  1893. return PHPExcel_Calculation_Functions::VALUE();
  1894. } // function LOGNORMDIST()
  1895. /**
  1896. * MAX
  1897. *
  1898. * MAX returns the value of the element of the values passed that has the highest value,
  1899. * with negative numbers considered smaller than positive numbers.
  1900. *
  1901. * Excel Function:
  1902. * MAX(value1[,value2[, ...]])
  1903. *
  1904. * @access public
  1905. * @category Statistical Functions
  1906. * @param mixed $arg,... Data values
  1907. * @return float
  1908. */
  1909. public static function MAX() {
  1910. // Return value
  1911. $returnValue = null;
  1912. // Loop through arguments
  1913. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  1914. foreach ($aArgs as $arg) {
  1915. // Is it a numeric value?
  1916. if ((is_numeric($arg)) && (!is_string($arg))) {
  1917. if ((is_null($returnValue)) || ($arg > $returnValue)) {
  1918. $returnValue = $arg;
  1919. }
  1920. }
  1921. }
  1922. // Return
  1923. if(is_null($returnValue)) {
  1924. return 0;
  1925. }
  1926. return $returnValue;
  1927. } // function MAX()
  1928. /**
  1929. * MAXA
  1930. *
  1931. * Returns the greatest value in a list of arguments, including numbers, text, and logical values
  1932. *
  1933. * Excel Function:
  1934. * MAXA(value1[,value2[, ...]])
  1935. *
  1936. * @access public
  1937. * @category Statistical Functions
  1938. * @param mixed $arg,... Data values
  1939. * @return float
  1940. */
  1941. public static function MAXA() {
  1942. // Return value
  1943. $returnValue = null;
  1944. // Loop through arguments
  1945. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  1946. foreach ($aArgs as $arg) {
  1947. // Is it a numeric value?
  1948. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
  1949. if (is_bool($arg)) {
  1950. $arg = (integer) $arg;
  1951. } elseif (is_string($arg)) {
  1952. $arg = 0;
  1953. }
  1954. if ((is_null($returnValue)) || ($arg > $returnValue)) {
  1955. $returnValue = $arg;
  1956. }
  1957. }
  1958. }
  1959. // Return
  1960. if(is_null($returnValue)) {
  1961. return 0;
  1962. }
  1963. return $returnValue;
  1964. } // function MAXA()
  1965. /**
  1966. * MAXIF
  1967. *
  1968. * Counts the maximum value within a range of cells that contain numbers within the list of arguments
  1969. *
  1970. * Excel Function:
  1971. * MAXIF(value1[,value2[, ...]],condition)
  1972. *
  1973. * @access public
  1974. * @category Mathematical and Trigonometric Functions
  1975. * @param mixed $arg,... Data values
  1976. * @param string $condition The criteria that defines which cells will be checked.
  1977. * @return float
  1978. */
  1979. public static function MAXIF($aArgs,$condition,$sumArgs = array()) {
  1980. // Return value
  1981. $returnValue = null;
  1982. $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
  1983. $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
  1984. if (empty($sumArgs)) {
  1985. $sumArgs = $aArgs;
  1986. }
  1987. $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
  1988. // Loop through arguments
  1989. foreach ($aArgs as $key => $arg) {
  1990. if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
  1991. $testCondition = '='.$arg.$condition;
  1992. if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
  1993. if ((is_null($returnValue)) || ($arg > $returnValue)) {
  1994. $returnValue = $arg;
  1995. }
  1996. }
  1997. }
  1998. // Return
  1999. return $returnValue;
  2000. } // function MAXIF()
  2001. /**
  2002. * MEDIAN
  2003. *
  2004. * Returns the median of the given numbers. The median is the number in the middle of a set of numbers.
  2005. *
  2006. * Excel Function:
  2007. * MEDIAN(value1[,value2[, ...]])
  2008. *
  2009. * @access public
  2010. * @category Statistical Functions
  2011. * @param mixed $arg,... Data values
  2012. * @return float
  2013. */
  2014. public static function MEDIAN() {
  2015. // Return value
  2016. $returnValue = PHPExcel_Calculation_Functions::NaN();
  2017. $mArgs = array();
  2018. // Loop through arguments
  2019. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  2020. foreach ($aArgs as $arg) {
  2021. // Is it a numeric value?
  2022. if ((is_numeric($arg)) && (!is_string($arg))) {
  2023. $mArgs[] = $arg;
  2024. }
  2025. }
  2026. $mValueCount = count($mArgs);
  2027. if ($mValueCount > 0) {
  2028. sort($mArgs,SORT_NUMERIC);
  2029. $mValueCount = $mValueCount / 2;
  2030. if ($mValueCount == floor($mValueCount)) {
  2031. $returnValue = ($mArgs[$mValueCount--] + $mArgs[$mValueCount]) / 2;
  2032. } else {
  2033. $mValueCount == floor($mValueCount);
  2034. $returnValue = $mArgs[$mValueCount];
  2035. }
  2036. }
  2037. // Return
  2038. return $returnValue;
  2039. } // function MEDIAN()
  2040. /**
  2041. * MIN
  2042. *
  2043. * MIN returns the value of the element of the values passed that has the smallest value,
  2044. * with negative numbers considered smaller than positive numbers.
  2045. *
  2046. * Excel Function:
  2047. * MIN(value1[,value2[, ...]])
  2048. *
  2049. * @access public
  2050. * @category Statistical Functions
  2051. * @param mixed $arg,... Data values
  2052. * @return float
  2053. */
  2054. public static function MIN() {
  2055. // Return value
  2056. $returnValue = null;
  2057. // Loop through arguments
  2058. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  2059. foreach ($aArgs as $arg) {
  2060. // Is it a numeric value?
  2061. if ((is_numeric($arg)) && (!is_string($arg))) {
  2062. if ((is_null($returnValue)) || ($arg < $returnValue)) {
  2063. $returnValue = $arg;
  2064. }
  2065. }
  2066. }
  2067. // Return
  2068. if(is_null($returnValue)) {
  2069. return 0;
  2070. }
  2071. return $returnValue;
  2072. } // function MIN()
  2073. /**
  2074. * MINA
  2075. *
  2076. * Returns the smallest value in a list of arguments, including numbers, text, and logical values
  2077. *
  2078. * Excel Function:
  2079. * MINA(value1[,value2[, ...]])
  2080. *
  2081. * @access public
  2082. * @category Statistical Functions
  2083. * @param mixed $arg,... Data values
  2084. * @return float
  2085. */
  2086. public static function MINA() {
  2087. // Return value
  2088. $returnValue = null;
  2089. // Loop through arguments
  2090. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  2091. foreach ($aArgs as $arg) {
  2092. // Is it a numeric value?
  2093. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) && ($arg != '')))) {
  2094. if (is_bool($arg)) {
  2095. $arg = (integer) $arg;
  2096. } elseif (is_string($arg)) {
  2097. $arg = 0;
  2098. }
  2099. if ((is_null($returnValue)) || ($arg < $returnValue)) {
  2100. $returnValue = $arg;
  2101. }
  2102. }
  2103. }
  2104. // Return
  2105. if(is_null($returnValue)) {
  2106. return 0;
  2107. }
  2108. return $returnValue;
  2109. } // function MINA()
  2110. /**
  2111. * MINIF
  2112. *
  2113. * Returns the minimum value within a range of cells that contain numbers within the list of arguments
  2114. *
  2115. * Excel Function:
  2116. * MINIF(value1[,value2[, ...]],condition)
  2117. *
  2118. * @access public
  2119. * @category Mathematical and Trigonometric Functions
  2120. * @param mixed $arg,... Data values
  2121. * @param string $condition The criteria that defines which cells will be checked.
  2122. * @return float
  2123. */
  2124. public static function MINIF($aArgs,$condition,$sumArgs = array()) {
  2125. // Return value
  2126. $returnValue = null;
  2127. $aArgs = PHPExcel_Calculation_Functions::flattenArray($aArgs);
  2128. $sumArgs = PHPExcel_Calculation_Functions::flattenArray($sumArgs);
  2129. if (empty($sumArgs)) {
  2130. $sumArgs = $aArgs;
  2131. }
  2132. $condition = PHPExcel_Calculation_Functions::_ifCondition($condition);
  2133. // Loop through arguments
  2134. foreach ($aArgs as $key => $arg) {
  2135. if (!is_numeric($arg)) { $arg = PHPExcel_Calculation::_wrapResult(strtoupper($arg)); }
  2136. $testCondition = '='.$arg.$condition;
  2137. if (PHPExcel_Calculation::getInstance()->_calculateFormulaValue($testCondition)) {
  2138. if ((is_null($returnValue)) || ($arg < $returnValue)) {
  2139. $returnValue = $arg;
  2140. }
  2141. }
  2142. }
  2143. // Return
  2144. return $returnValue;
  2145. } // function MINIF()
  2146. //
  2147. // Special variant of array_count_values that isn't limited to strings and integers,
  2148. // but can work with floating point numbers as values
  2149. //
  2150. private static function _modeCalc($data) {
  2151. $frequencyArray = array();
  2152. foreach($data as $datum) {
  2153. $found = False;
  2154. foreach($frequencyArray as $key => $value) {
  2155. if ((string) $value['value'] == (string) $datum) {
  2156. ++$frequencyArray[$key]['frequency'];
  2157. $found = True;
  2158. break;
  2159. }
  2160. }
  2161. if (!$found) {
  2162. $frequencyArray[] = array('value' => $datum,
  2163. 'frequency' => 1 );
  2164. }
  2165. }
  2166. foreach($frequencyArray as $key => $value) {
  2167. $frequencyList[$key] = $value['frequency'];
  2168. $valueList[$key] = $value['value'];
  2169. }
  2170. array_multisort($frequencyList, SORT_DESC, $valueList, SORT_ASC, SORT_NUMERIC, $frequencyArray);
  2171. if ($frequencyArray[0]['frequency'] == 1) {
  2172. return PHPExcel_Calculation_Functions::NA();
  2173. }
  2174. return $frequencyArray[0]['value'];
  2175. } // function _modeCalc()
  2176. /**
  2177. * MODE
  2178. *
  2179. * Returns the most frequently occurring, or repetitive, value in an array or range of data
  2180. *
  2181. * Excel Function:
  2182. * MODE(value1[,value2[, ...]])
  2183. *
  2184. * @access public
  2185. * @category Statistical Functions
  2186. * @param mixed $arg,... Data values
  2187. * @return float
  2188. */
  2189. public static function MODE() {
  2190. // Return value
  2191. $returnValue = PHPExcel_Calculation_Functions::NA();
  2192. // Loop through arguments
  2193. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  2194. $mArgs = array();
  2195. foreach ($aArgs as $arg) {
  2196. // Is it a numeric value?
  2197. if ((is_numeric($arg)) && (!is_string($arg))) {
  2198. $mArgs[] = $arg;
  2199. }
  2200. }
  2201. if (!empty($mArgs)) {
  2202. return self::_modeCalc($mArgs);
  2203. }
  2204. // Return
  2205. return $returnValue;
  2206. } // function MODE()
  2207. /**
  2208. * NEGBINOMDIST
  2209. *
  2210. * Returns the negative binomial distribution. NEGBINOMDIST returns the probability that
  2211. * there will be number_f failures before the number_s-th success, when the constant
  2212. * probability of a success is probability_s. This function is similar to the binomial
  2213. * distribution, except that the number of successes is fixed, and the number of trials is
  2214. * variable. Like the binomial, trials are assumed to be independent.
  2215. *
  2216. * @param float $failures Number of Failures
  2217. * @param float $successes Threshold number of Successes
  2218. * @param float $probability Probability of success on each trial
  2219. * @return float
  2220. *
  2221. */
  2222. public static function NEGBINOMDIST($failures, $successes, $probability) {
  2223. $failures = floor(PHPExcel_Calculation_Functions::flattenSingleValue($failures));
  2224. $successes = floor(PHPExcel_Calculation_Functions::flattenSingleValue($successes));
  2225. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  2226. if ((is_numeric($failures)) && (is_numeric($successes)) && (is_numeric($probability))) {
  2227. if (($failures < 0) || ($successes < 1)) {
  2228. return PHPExcel_Calculation_Functions::NaN();
  2229. }
  2230. if (($probability < 0) || ($probability > 1)) {
  2231. return PHPExcel_Calculation_Functions::NaN();
  2232. }
  2233. if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
  2234. if (($failures + $successes - 1) <= 0) {
  2235. return PHPExcel_Calculation_Functions::NaN();
  2236. }
  2237. }
  2238. return (PHPExcel_Calculation_MathTrig::COMBIN($failures + $successes - 1,$successes - 1)) * (pow($probability,$successes)) * (pow(1 - $probability,$failures)) ;
  2239. }
  2240. return PHPExcel_Calculation_Functions::VALUE();
  2241. } // function NEGBINOMDIST()
  2242. /**
  2243. * NORMDIST
  2244. *
  2245. * Returns the normal distribution for the specified mean and standard deviation. This
  2246. * function has a very wide range of applications in statistics, including hypothesis
  2247. * testing.
  2248. *
  2249. * @param float $value
  2250. * @param float $mean Mean Value
  2251. * @param float $stdDev Standard Deviation
  2252. * @param boolean $cumulative
  2253. * @return float
  2254. *
  2255. */
  2256. public static function NORMDIST($value, $mean, $stdDev, $cumulative) {
  2257. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  2258. $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
  2259. $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
  2260. if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
  2261. if ($stdDev < 0) {
  2262. return PHPExcel_Calculation_Functions::NaN();
  2263. }
  2264. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  2265. if ($cumulative) {
  2266. return 0.5 * (1 + PHPExcel_Calculation_Engineering::_erfVal(($value - $mean) / ($stdDev * sqrt(2))));
  2267. } else {
  2268. return (1 / (SQRT2PI * $stdDev)) * exp(0 - (pow($value - $mean,2) / (2 * ($stdDev * $stdDev))));
  2269. }
  2270. }
  2271. }
  2272. return PHPExcel_Calculation_Functions::VALUE();
  2273. } // function NORMDIST()
  2274. /**
  2275. * NORMINV
  2276. *
  2277. * Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
  2278. *
  2279. * @param float $value
  2280. * @param float $mean Mean Value
  2281. * @param float $stdDev Standard Deviation
  2282. * @return float
  2283. *
  2284. */
  2285. public static function NORMINV($probability,$mean,$stdDev) {
  2286. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  2287. $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
  2288. $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
  2289. if ((is_numeric($probability)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
  2290. if (($probability < 0) || ($probability > 1)) {
  2291. return PHPExcel_Calculation_Functions::NaN();
  2292. }
  2293. if ($stdDev < 0) {
  2294. return PHPExcel_Calculation_Functions::NaN();
  2295. }
  2296. return (self::_inverse_ncdf($probability) * $stdDev) + $mean;
  2297. }
  2298. return PHPExcel_Calculation_Functions::VALUE();
  2299. } // function NORMINV()
  2300. /**
  2301. * NORMSDIST
  2302. *
  2303. * Returns the standard normal cumulative distribution function. The distribution has
  2304. * a mean of 0 (zero) and a standard deviation of one. Use this function in place of a
  2305. * table of standard normal curve areas.
  2306. *
  2307. * @param float $value
  2308. * @return float
  2309. */
  2310. public static function NORMSDIST($value) {
  2311. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  2312. return self::NORMDIST($value, 0, 1, True);
  2313. } // function NORMSDIST()
  2314. /**
  2315. * NORMSINV
  2316. *
  2317. * Returns the inverse of the standard normal cumulative distribution
  2318. *
  2319. * @param float $value
  2320. * @return float
  2321. */
  2322. public static function NORMSINV($value) {
  2323. return self::NORMINV($value, 0, 1);
  2324. } // function NORMSINV()
  2325. /**
  2326. * PERCENTILE
  2327. *
  2328. * Returns the nth percentile of values in a range..
  2329. *
  2330. * Excel Function:
  2331. * PERCENTILE(value1[,value2[, ...]],entry)
  2332. *
  2333. * @access public
  2334. * @category Statistical Functions
  2335. * @param mixed $arg,... Data values
  2336. * @param float $entry Percentile value in the range 0..1, inclusive.
  2337. * @return float
  2338. */
  2339. public static function PERCENTILE() {
  2340. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  2341. // Calculate
  2342. $entry = array_pop($aArgs);
  2343. if ((is_numeric($entry)) && (!is_string($entry))) {
  2344. if (($entry < 0) || ($entry > 1)) {
  2345. return PHPExcel_Calculation_Functions::NaN();
  2346. }
  2347. $mArgs = array();
  2348. foreach ($aArgs as $arg) {
  2349. // Is it a numeric value?
  2350. if ((is_numeric($arg)) && (!is_string($arg))) {
  2351. $mArgs[] = $arg;
  2352. }
  2353. }
  2354. $mValueCount = count($mArgs);
  2355. if ($mValueCount > 0) {
  2356. sort($mArgs);
  2357. $count = self::COUNT($mArgs);
  2358. $index = $entry * ($count-1);
  2359. $iBase = floor($index);
  2360. if ($index == $iBase) {
  2361. return $mArgs[$index];
  2362. } else {
  2363. $iNext = $iBase + 1;
  2364. $iProportion = $index - $iBase;
  2365. return $mArgs[$iBase] + (($mArgs[$iNext] - $mArgs[$iBase]) * $iProportion) ;
  2366. }
  2367. }
  2368. }
  2369. return PHPExcel_Calculation_Functions::VALUE();
  2370. } // function PERCENTILE()
  2371. /**
  2372. * PERCENTRANK
  2373. *
  2374. * Returns the rank of a value in a data set as a percentage of the data set.
  2375. *
  2376. * @param array of number An array of, or a reference to, a list of numbers.
  2377. * @param number The number whose rank you want to find.
  2378. * @param number The number of significant digits for the returned percentage value.
  2379. * @return float
  2380. */
  2381. public static function PERCENTRANK($valueSet,$value,$significance=3) {
  2382. $valueSet = PHPExcel_Calculation_Functions::flattenArray($valueSet);
  2383. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  2384. $significance = (is_null($significance)) ? 3 : (integer) PHPExcel_Calculation_Functions::flattenSingleValue($significance);
  2385. foreach($valueSet as $key => $valueEntry) {
  2386. if (!is_numeric($valueEntry)) {
  2387. unset($valueSet[$key]);
  2388. }
  2389. }
  2390. sort($valueSet,SORT_NUMERIC);
  2391. $valueCount = count($valueSet);
  2392. if ($valueCount == 0) {
  2393. return PHPExcel_Calculation_Functions::NaN();
  2394. }
  2395. $valueAdjustor = $valueCount - 1;
  2396. if (($value < $valueSet[0]) || ($value > $valueSet[$valueAdjustor])) {
  2397. return PHPExcel_Calculation_Functions::NA();
  2398. }
  2399. $pos = array_search($value,$valueSet);
  2400. if ($pos === False) {
  2401. $pos = 0;
  2402. $testValue = $valueSet[0];
  2403. while ($testValue < $value) {
  2404. $testValue = $valueSet[++$pos];
  2405. }
  2406. --$pos;
  2407. $pos += (($value - $valueSet[$pos]) / ($testValue - $valueSet[$pos]));
  2408. }
  2409. return round($pos / $valueAdjustor,$significance);
  2410. } // function PERCENTRANK()
  2411. /**
  2412. * PERMUT
  2413. *
  2414. * Returns the number of permutations for a given number of objects that can be
  2415. * selected from number objects. A permutation is any set or subset of objects or
  2416. * events where internal order is significant. Permutations are different from
  2417. * combinations, for which the internal order is not significant. Use this function
  2418. * for lottery-style probability calculations.
  2419. *
  2420. * @param int $numObjs Number of different objects
  2421. * @param int $numInSet Number of objects in each permutation
  2422. * @return int Number of permutations
  2423. */
  2424. public static function PERMUT($numObjs,$numInSet) {
  2425. $numObjs = PHPExcel_Calculation_Functions::flattenSingleValue($numObjs);
  2426. $numInSet = PHPExcel_Calculation_Functions::flattenSingleValue($numInSet);
  2427. if ((is_numeric($numObjs)) && (is_numeric($numInSet))) {
  2428. $numInSet = floor($numInSet);
  2429. if ($numObjs < $numInSet) {
  2430. return PHPExcel_Calculation_Functions::NaN();
  2431. }
  2432. return round(PHPExcel_Calculation_MathTrig::FACT($numObjs) / PHPExcel_Calculation_MathTrig::FACT($numObjs - $numInSet));
  2433. }
  2434. return PHPExcel_Calculation_Functions::VALUE();
  2435. } // function PERMUT()
  2436. /**
  2437. * POISSON
  2438. *
  2439. * Returns the Poisson distribution. A common application of the Poisson distribution
  2440. * is predicting the number of events over a specific time, such as the number of
  2441. * cars arriving at a toll plaza in 1 minute.
  2442. *
  2443. * @param float $value
  2444. * @param float $mean Mean Value
  2445. * @param boolean $cumulative
  2446. * @return float
  2447. *
  2448. */
  2449. public static function POISSON($value, $mean, $cumulative) {
  2450. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  2451. $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
  2452. if ((is_numeric($value)) && (is_numeric($mean))) {
  2453. if (($value < 0) || ($mean <= 0)) {
  2454. return PHPExcel_Calculation_Functions::NaN();
  2455. }
  2456. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  2457. if ($cumulative) {
  2458. $summer = 0;
  2459. for ($i = 0; $i <= floor($value); ++$i) {
  2460. $summer += pow($mean,$i) / PHPExcel_Calculation_MathTrig::FACT($i);
  2461. }
  2462. return exp(0-$mean) * $summer;
  2463. } else {
  2464. return (exp(0-$mean) * pow($mean,$value)) / PHPExcel_Calculation_MathTrig::FACT($value);
  2465. }
  2466. }
  2467. }
  2468. return PHPExcel_Calculation_Functions::VALUE();
  2469. } // function POISSON()
  2470. /**
  2471. * QUARTILE
  2472. *
  2473. * Returns the quartile of a data set.
  2474. *
  2475. * Excel Function:
  2476. * QUARTILE(value1[,value2[, ...]],entry)
  2477. *
  2478. * @access public
  2479. * @category Statistical Functions
  2480. * @param mixed $arg,... Data values
  2481. * @param int $entry Quartile value in the range 1..3, inclusive.
  2482. * @return float
  2483. */
  2484. public static function QUARTILE() {
  2485. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  2486. // Calculate
  2487. $entry = floor(array_pop($aArgs));
  2488. if ((is_numeric($entry)) && (!is_string($entry))) {
  2489. $entry /= 4;
  2490. if (($entry < 0) || ($entry > 1)) {
  2491. return PHPExcel_Calculation_Functions::NaN();
  2492. }
  2493. return self::PERCENTILE($aArgs,$entry);
  2494. }
  2495. return PHPExcel_Calculation_Functions::VALUE();
  2496. } // function QUARTILE()
  2497. /**
  2498. * RANK
  2499. *
  2500. * Returns the rank of a number in a list of numbers.
  2501. *
  2502. * @param number The number whose rank you want to find.
  2503. * @param array of number An array of, or a reference to, a list of numbers.
  2504. * @param mixed Order to sort the values in the value set
  2505. * @return float
  2506. */
  2507. public static function RANK($value,$valueSet,$order=0) {
  2508. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  2509. $valueSet = PHPExcel_Calculation_Functions::flattenArray($valueSet);
  2510. $order = (is_null($order)) ? 0 : (integer) PHPExcel_Calculation_Functions::flattenSingleValue($order);
  2511. foreach($valueSet as $key => $valueEntry) {
  2512. if (!is_numeric($valueEntry)) {
  2513. unset($valueSet[$key]);
  2514. }
  2515. }
  2516. if ($order == 0) {
  2517. rsort($valueSet,SORT_NUMERIC);
  2518. } else {
  2519. sort($valueSet,SORT_NUMERIC);
  2520. }
  2521. $pos = array_search($value,$valueSet);
  2522. if ($pos === False) {
  2523. return PHPExcel_Calculation_Functions::NA();
  2524. }
  2525. return ++$pos;
  2526. } // function RANK()
  2527. /**
  2528. * RSQ
  2529. *
  2530. * Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.
  2531. *
  2532. * @param array of mixed Data Series Y
  2533. * @param array of mixed Data Series X
  2534. * @return float
  2535. */
  2536. public static function RSQ($yValues,$xValues) {
  2537. if (!self::_checkTrendArrays($yValues,$xValues)) {
  2538. return PHPExcel_Calculation_Functions::VALUE();
  2539. }
  2540. $yValueCount = count($yValues);
  2541. $xValueCount = count($xValues);
  2542. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  2543. return PHPExcel_Calculation_Functions::NA();
  2544. } elseif ($yValueCount == 1) {
  2545. return PHPExcel_Calculation_Functions::DIV0();
  2546. }
  2547. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
  2548. return $bestFitLinear->getGoodnessOfFit();
  2549. } // function RSQ()
  2550. /**
  2551. * SKEW
  2552. *
  2553. * Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry
  2554. * of a distribution around its mean. Positive skewness indicates a distribution with an
  2555. * asymmetric tail extending toward more positive values. Negative skewness indicates a
  2556. * distribution with an asymmetric tail extending toward more negative values.
  2557. *
  2558. * @param array Data Series
  2559. * @return float
  2560. */
  2561. public static function SKEW() {
  2562. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  2563. $mean = self::AVERAGE($aArgs);
  2564. $stdDev = self::STDEV($aArgs);
  2565. $count = $summer = 0;
  2566. // Loop through arguments
  2567. foreach ($aArgs as $k => $arg) {
  2568. if ((is_bool($arg)) &&
  2569. (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
  2570. } else {
  2571. // Is it a numeric value?
  2572. if ((is_numeric($arg)) && (!is_string($arg))) {
  2573. $summer += pow((($arg - $mean) / $stdDev),3) ;
  2574. ++$count;
  2575. }
  2576. }
  2577. }
  2578. // Return
  2579. if ($count > 2) {
  2580. return $summer * ($count / (($count-1) * ($count-2)));
  2581. }
  2582. return PHPExcel_Calculation_Functions::DIV0();
  2583. } // function SKEW()
  2584. /**
  2585. * SLOPE
  2586. *
  2587. * Returns the slope of the linear regression line through data points in known_y's and known_x's.
  2588. *
  2589. * @param array of mixed Data Series Y
  2590. * @param array of mixed Data Series X
  2591. * @return float
  2592. */
  2593. public static function SLOPE($yValues,$xValues) {
  2594. if (!self::_checkTrendArrays($yValues,$xValues)) {
  2595. return PHPExcel_Calculation_Functions::VALUE();
  2596. }
  2597. $yValueCount = count($yValues);
  2598. $xValueCount = count($xValues);
  2599. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  2600. return PHPExcel_Calculation_Functions::NA();
  2601. } elseif ($yValueCount == 1) {
  2602. return PHPExcel_Calculation_Functions::DIV0();
  2603. }
  2604. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
  2605. return $bestFitLinear->getSlope();
  2606. } // function SLOPE()
  2607. /**
  2608. * SMALL
  2609. *
  2610. * Returns the nth smallest value in a data set. You can use this function to
  2611. * select a value based on its relative standing.
  2612. *
  2613. * Excel Function:
  2614. * SMALL(value1[,value2[, ...]],entry)
  2615. *
  2616. * @access public
  2617. * @category Statistical Functions
  2618. * @param mixed $arg,... Data values
  2619. * @param int $entry Position (ordered from the smallest) in the array or range of data to return
  2620. * @return float
  2621. */
  2622. public static function SMALL() {
  2623. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  2624. // Calculate
  2625. $entry = array_pop($aArgs);
  2626. if ((is_numeric($entry)) && (!is_string($entry))) {
  2627. $mArgs = array();
  2628. foreach ($aArgs as $arg) {
  2629. // Is it a numeric value?
  2630. if ((is_numeric($arg)) && (!is_string($arg))) {
  2631. $mArgs[] = $arg;
  2632. }
  2633. }
  2634. $count = self::COUNT($mArgs);
  2635. $entry = floor(--$entry);
  2636. if (($entry < 0) || ($entry >= $count) || ($count == 0)) {
  2637. return PHPExcel_Calculation_Functions::NaN();
  2638. }
  2639. sort($mArgs);
  2640. return $mArgs[$entry];
  2641. }
  2642. return PHPExcel_Calculation_Functions::VALUE();
  2643. } // function SMALL()
  2644. /**
  2645. * STANDARDIZE
  2646. *
  2647. * Returns a normalized value from a distribution characterized by mean and standard_dev.
  2648. *
  2649. * @param float $value Value to normalize
  2650. * @param float $mean Mean Value
  2651. * @param float $stdDev Standard Deviation
  2652. * @return float Standardized value
  2653. */
  2654. public static function STANDARDIZE($value,$mean,$stdDev) {
  2655. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  2656. $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
  2657. $stdDev = PHPExcel_Calculation_Functions::flattenSingleValue($stdDev);
  2658. if ((is_numeric($value)) && (is_numeric($mean)) && (is_numeric($stdDev))) {
  2659. if ($stdDev <= 0) {
  2660. return PHPExcel_Calculation_Functions::NaN();
  2661. }
  2662. return ($value - $mean) / $stdDev ;
  2663. }
  2664. return PHPExcel_Calculation_Functions::VALUE();
  2665. } // function STANDARDIZE()
  2666. /**
  2667. * STDEV
  2668. *
  2669. * Estimates standard deviation based on a sample. The standard deviation is a measure of how
  2670. * widely values are dispersed from the average value (the mean).
  2671. *
  2672. * Excel Function:
  2673. * STDEV(value1[,value2[, ...]])
  2674. *
  2675. * @access public
  2676. * @category Statistical Functions
  2677. * @param mixed $arg,... Data values
  2678. * @return float
  2679. */
  2680. public static function STDEV() {
  2681. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  2682. // Return value
  2683. $returnValue = null;
  2684. $aMean = self::AVERAGE($aArgs);
  2685. if (!is_null($aMean)) {
  2686. $aCount = -1;
  2687. foreach ($aArgs as $k => $arg) {
  2688. if ((is_bool($arg)) &&
  2689. ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
  2690. $arg = (integer) $arg;
  2691. }
  2692. // Is it a numeric value?
  2693. if ((is_numeric($arg)) && (!is_string($arg))) {
  2694. if (is_null($returnValue)) {
  2695. $returnValue = pow(($arg - $aMean),2);
  2696. } else {
  2697. $returnValue += pow(($arg - $aMean),2);
  2698. }
  2699. ++$aCount;
  2700. }
  2701. }
  2702. // Return
  2703. if (($aCount > 0) && ($returnValue >= 0)) {
  2704. return sqrt($returnValue / $aCount);
  2705. }
  2706. }
  2707. return PHPExcel_Calculation_Functions::DIV0();
  2708. } // function STDEV()
  2709. /**
  2710. * STDEVA
  2711. *
  2712. * Estimates standard deviation based on a sample, including numbers, text, and logical values
  2713. *
  2714. * Excel Function:
  2715. * STDEVA(value1[,value2[, ...]])
  2716. *
  2717. * @access public
  2718. * @category Statistical Functions
  2719. * @param mixed $arg,... Data values
  2720. * @return float
  2721. */
  2722. public static function STDEVA() {
  2723. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  2724. // Return value
  2725. $returnValue = null;
  2726. $aMean = self::AVERAGEA($aArgs);
  2727. if (!is_null($aMean)) {
  2728. $aCount = -1;
  2729. foreach ($aArgs as $k => $arg) {
  2730. if ((is_bool($arg)) &&
  2731. (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
  2732. } else {
  2733. // Is it a numeric value?
  2734. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
  2735. if (is_bool($arg)) {
  2736. $arg = (integer) $arg;
  2737. } elseif (is_string($arg)) {
  2738. $arg = 0;
  2739. }
  2740. if (is_null($returnValue)) {
  2741. $returnValue = pow(($arg - $aMean),2);
  2742. } else {
  2743. $returnValue += pow(($arg - $aMean),2);
  2744. }
  2745. ++$aCount;
  2746. }
  2747. }
  2748. }
  2749. // Return
  2750. if (($aCount > 0) && ($returnValue >= 0)) {
  2751. return sqrt($returnValue / $aCount);
  2752. }
  2753. }
  2754. return PHPExcel_Calculation_Functions::DIV0();
  2755. } // function STDEVA()
  2756. /**
  2757. * STDEVP
  2758. *
  2759. * Calculates standard deviation based on the entire population
  2760. *
  2761. * Excel Function:
  2762. * STDEVP(value1[,value2[, ...]])
  2763. *
  2764. * @access public
  2765. * @category Statistical Functions
  2766. * @param mixed $arg,... Data values
  2767. * @return float
  2768. */
  2769. public static function STDEVP() {
  2770. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  2771. // Return value
  2772. $returnValue = null;
  2773. $aMean = self::AVERAGE($aArgs);
  2774. if (!is_null($aMean)) {
  2775. $aCount = 0;
  2776. foreach ($aArgs as $k => $arg) {
  2777. if ((is_bool($arg)) &&
  2778. ((!PHPExcel_Calculation_Functions::isCellValue($k)) || (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE))) {
  2779. $arg = (integer) $arg;
  2780. }
  2781. // Is it a numeric value?
  2782. if ((is_numeric($arg)) && (!is_string($arg))) {
  2783. if (is_null($returnValue)) {
  2784. $returnValue = pow(($arg - $aMean),2);
  2785. } else {
  2786. $returnValue += pow(($arg - $aMean),2);
  2787. }
  2788. ++$aCount;
  2789. }
  2790. }
  2791. // Return
  2792. if (($aCount > 0) && ($returnValue >= 0)) {
  2793. return sqrt($returnValue / $aCount);
  2794. }
  2795. }
  2796. return PHPExcel_Calculation_Functions::DIV0();
  2797. } // function STDEVP()
  2798. /**
  2799. * STDEVPA
  2800. *
  2801. * Calculates standard deviation based on the entire population, including numbers, text, and logical values
  2802. *
  2803. * Excel Function:
  2804. * STDEVPA(value1[,value2[, ...]])
  2805. *
  2806. * @access public
  2807. * @category Statistical Functions
  2808. * @param mixed $arg,... Data values
  2809. * @return float
  2810. */
  2811. public static function STDEVPA() {
  2812. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  2813. // Return value
  2814. $returnValue = null;
  2815. $aMean = self::AVERAGEA($aArgs);
  2816. if (!is_null($aMean)) {
  2817. $aCount = 0;
  2818. foreach ($aArgs as $k => $arg) {
  2819. if ((is_bool($arg)) &&
  2820. (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
  2821. } else {
  2822. // Is it a numeric value?
  2823. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
  2824. if (is_bool($arg)) {
  2825. $arg = (integer) $arg;
  2826. } elseif (is_string($arg)) {
  2827. $arg = 0;
  2828. }
  2829. if (is_null($returnValue)) {
  2830. $returnValue = pow(($arg - $aMean),2);
  2831. } else {
  2832. $returnValue += pow(($arg - $aMean),2);
  2833. }
  2834. ++$aCount;
  2835. }
  2836. }
  2837. }
  2838. // Return
  2839. if (($aCount > 0) && ($returnValue >= 0)) {
  2840. return sqrt($returnValue / $aCount);
  2841. }
  2842. }
  2843. return PHPExcel_Calculation_Functions::DIV0();
  2844. } // function STDEVPA()
  2845. /**
  2846. * STEYX
  2847. *
  2848. * Returns the standard error of the predicted y-value for each x in the regression.
  2849. *
  2850. * @param array of mixed Data Series Y
  2851. * @param array of mixed Data Series X
  2852. * @return float
  2853. */
  2854. public static function STEYX($yValues,$xValues) {
  2855. if (!self::_checkTrendArrays($yValues,$xValues)) {
  2856. return PHPExcel_Calculation_Functions::VALUE();
  2857. }
  2858. $yValueCount = count($yValues);
  2859. $xValueCount = count($xValues);
  2860. if (($yValueCount == 0) || ($yValueCount != $xValueCount)) {
  2861. return PHPExcel_Calculation_Functions::NA();
  2862. } elseif ($yValueCount == 1) {
  2863. return PHPExcel_Calculation_Functions::DIV0();
  2864. }
  2865. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues);
  2866. return $bestFitLinear->getStdevOfResiduals();
  2867. } // function STEYX()
  2868. /**
  2869. * TDIST
  2870. *
  2871. * Returns the probability of Student's T distribution.
  2872. *
  2873. * @param float $value Value for the function
  2874. * @param float $degrees degrees of freedom
  2875. * @param float $tails number of tails (1 or 2)
  2876. * @return float
  2877. */
  2878. public static function TDIST($value, $degrees, $tails) {
  2879. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  2880. $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
  2881. $tails = floor(PHPExcel_Calculation_Functions::flattenSingleValue($tails));
  2882. if ((is_numeric($value)) && (is_numeric($degrees)) && (is_numeric($tails))) {
  2883. if (($value < 0) || ($degrees < 1) || ($tails < 1) || ($tails > 2)) {
  2884. return PHPExcel_Calculation_Functions::NaN();
  2885. }
  2886. // tdist, which finds the probability that corresponds to a given value
  2887. // of t with k degrees of freedom. This algorithm is translated from a
  2888. // pascal function on p81 of "Statistical Computing in Pascal" by D
  2889. // Cooke, A H Craven & G M Clark (1985: Edward Arnold (Pubs.) Ltd:
  2890. // London). The above Pascal algorithm is itself a translation of the
  2891. // fortran algoritm "AS 3" by B E Cooper of the Atlas Computer
  2892. // Laboratory as reported in (among other places) "Applied Statistics
  2893. // Algorithms", editied by P Griffiths and I D Hill (1985; Ellis
  2894. // Horwood Ltd.; W. Sussex, England).
  2895. $tterm = $degrees;
  2896. $ttheta = atan2($value,sqrt($tterm));
  2897. $tc = cos($ttheta);
  2898. $ts = sin($ttheta);
  2899. $tsum = 0;
  2900. if (($degrees % 2) == 1) {
  2901. $ti = 3;
  2902. $tterm = $tc;
  2903. } else {
  2904. $ti = 2;
  2905. $tterm = 1;
  2906. }
  2907. $tsum = $tterm;
  2908. while ($ti < $degrees) {
  2909. $tterm *= $tc * $tc * ($ti - 1) / $ti;
  2910. $tsum += $tterm;
  2911. $ti += 2;
  2912. }
  2913. $tsum *= $ts;
  2914. if (($degrees % 2) == 1) { $tsum = M_2DIVPI * ($tsum + $ttheta); }
  2915. $tValue = 0.5 * (1 + $tsum);
  2916. if ($tails == 1) {
  2917. return 1 - abs($tValue);
  2918. } else {
  2919. return 1 - abs((1 - $tValue) - $tValue);
  2920. }
  2921. }
  2922. return PHPExcel_Calculation_Functions::VALUE();
  2923. } // function TDIST()
  2924. /**
  2925. * TINV
  2926. *
  2927. * Returns the one-tailed probability of the chi-squared distribution.
  2928. *
  2929. * @param float $probability Probability for the function
  2930. * @param float $degrees degrees of freedom
  2931. * @return float
  2932. */
  2933. public static function TINV($probability, $degrees) {
  2934. $probability = PHPExcel_Calculation_Functions::flattenSingleValue($probability);
  2935. $degrees = floor(PHPExcel_Calculation_Functions::flattenSingleValue($degrees));
  2936. if ((is_numeric($probability)) && (is_numeric($degrees))) {
  2937. $xLo = 100;
  2938. $xHi = 0;
  2939. $x = $xNew = 1;
  2940. $dx = 1;
  2941. $i = 0;
  2942. while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
  2943. // Apply Newton-Raphson step
  2944. $result = self::TDIST($x, $degrees, 2);
  2945. $error = $result - $probability;
  2946. if ($error == 0.0) {
  2947. $dx = 0;
  2948. } elseif ($error < 0.0) {
  2949. $xLo = $x;
  2950. } else {
  2951. $xHi = $x;
  2952. }
  2953. // Avoid division by zero
  2954. if ($result != 0.0) {
  2955. $dx = $error / $result;
  2956. $xNew = $x - $dx;
  2957. }
  2958. // If the NR fails to converge (which for example may be the
  2959. // case if the initial guess is too rough) we apply a bisection
  2960. // step to determine a more narrow interval around the root.
  2961. if (($xNew < $xLo) || ($xNew > $xHi) || ($result == 0.0)) {
  2962. $xNew = ($xLo + $xHi) / 2;
  2963. $dx = $xNew - $x;
  2964. }
  2965. $x = $xNew;
  2966. }
  2967. if ($i == MAX_ITERATIONS) {
  2968. return PHPExcel_Calculation_Functions::NA();
  2969. }
  2970. return round($x,12);
  2971. }
  2972. return PHPExcel_Calculation_Functions::VALUE();
  2973. } // function TINV()
  2974. /**
  2975. * TREND
  2976. *
  2977. * Returns values along a linear trend
  2978. *
  2979. * @param array of mixed Data Series Y
  2980. * @param array of mixed Data Series X
  2981. * @param array of mixed Values of X for which we want to find Y
  2982. * @param boolean A logical value specifying whether to force the intersect to equal 0.
  2983. * @return array of float
  2984. */
  2985. public static function TREND($yValues,$xValues=array(),$newValues=array(),$const=True) {
  2986. $yValues = PHPExcel_Calculation_Functions::flattenArray($yValues);
  2987. $xValues = PHPExcel_Calculation_Functions::flattenArray($xValues);
  2988. $newValues = PHPExcel_Calculation_Functions::flattenArray($newValues);
  2989. $const = (is_null($const)) ? True : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($const);
  2990. $bestFitLinear = trendClass::calculate(trendClass::TREND_LINEAR,$yValues,$xValues,$const);
  2991. if (empty($newValues)) {
  2992. $newValues = $bestFitLinear->getXValues();
  2993. }
  2994. $returnArray = array();
  2995. foreach($newValues as $xValue) {
  2996. $returnArray[0][] = $bestFitLinear->getValueOfYForX($xValue);
  2997. }
  2998. return $returnArray;
  2999. } // function TREND()
  3000. /**
  3001. * TRIMMEAN
  3002. *
  3003. * Returns the mean of the interior of a data set. TRIMMEAN calculates the mean
  3004. * taken by excluding a percentage of data points from the top and bottom tails
  3005. * of a data set.
  3006. *
  3007. * Excel Function:
  3008. * TRIMEAN(value1[,value2[, ...]],$discard)
  3009. *
  3010. * @access public
  3011. * @category Statistical Functions
  3012. * @param mixed $arg,... Data values
  3013. * @param float $discard Percentage to discard
  3014. * @return float
  3015. */
  3016. public static function TRIMMEAN() {
  3017. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  3018. // Calculate
  3019. $percent = array_pop($aArgs);
  3020. if ((is_numeric($percent)) && (!is_string($percent))) {
  3021. if (($percent < 0) || ($percent > 1)) {
  3022. return PHPExcel_Calculation_Functions::NaN();
  3023. }
  3024. $mArgs = array();
  3025. foreach ($aArgs as $arg) {
  3026. // Is it a numeric value?
  3027. if ((is_numeric($arg)) && (!is_string($arg))) {
  3028. $mArgs[] = $arg;
  3029. }
  3030. }
  3031. $discard = floor(self::COUNT($mArgs) * $percent / 2);
  3032. sort($mArgs);
  3033. for ($i=0; $i < $discard; ++$i) {
  3034. array_pop($mArgs);
  3035. array_shift($mArgs);
  3036. }
  3037. return self::AVERAGE($mArgs);
  3038. }
  3039. return PHPExcel_Calculation_Functions::VALUE();
  3040. } // function TRIMMEAN()
  3041. /**
  3042. * VARFunc
  3043. *
  3044. * Estimates variance based on a sample.
  3045. *
  3046. * Excel Function:
  3047. * VAR(value1[,value2[, ...]])
  3048. *
  3049. * @access public
  3050. * @category Statistical Functions
  3051. * @param mixed $arg,... Data values
  3052. * @return float
  3053. */
  3054. public static function VARFunc() {
  3055. // Return value
  3056. $returnValue = PHPExcel_Calculation_Functions::DIV0();
  3057. $summerA = $summerB = 0;
  3058. // Loop through arguments
  3059. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  3060. $aCount = 0;
  3061. foreach ($aArgs as $arg) {
  3062. if (is_bool($arg)) { $arg = (integer) $arg; }
  3063. // Is it a numeric value?
  3064. if ((is_numeric($arg)) && (!is_string($arg))) {
  3065. $summerA += ($arg * $arg);
  3066. $summerB += $arg;
  3067. ++$aCount;
  3068. }
  3069. }
  3070. // Return
  3071. if ($aCount > 1) {
  3072. $summerA *= $aCount;
  3073. $summerB *= $summerB;
  3074. $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
  3075. }
  3076. return $returnValue;
  3077. } // function VARFunc()
  3078. /**
  3079. * VARA
  3080. *
  3081. * Estimates variance based on a sample, including numbers, text, and logical values
  3082. *
  3083. * Excel Function:
  3084. * VARA(value1[,value2[, ...]])
  3085. *
  3086. * @access public
  3087. * @category Statistical Functions
  3088. * @param mixed $arg,... Data values
  3089. * @return float
  3090. */
  3091. public static function VARA() {
  3092. // Return value
  3093. $returnValue = PHPExcel_Calculation_Functions::DIV0();
  3094. $summerA = $summerB = 0;
  3095. // Loop through arguments
  3096. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  3097. $aCount = 0;
  3098. foreach ($aArgs as $k => $arg) {
  3099. if ((is_string($arg)) &&
  3100. (PHPExcel_Calculation_Functions::isValue($k))) {
  3101. return PHPExcel_Calculation_Functions::VALUE();
  3102. } elseif ((is_string($arg)) &&
  3103. (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
  3104. } else {
  3105. // Is it a numeric value?
  3106. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
  3107. if (is_bool($arg)) {
  3108. $arg = (integer) $arg;
  3109. } elseif (is_string($arg)) {
  3110. $arg = 0;
  3111. }
  3112. $summerA += ($arg * $arg);
  3113. $summerB += $arg;
  3114. ++$aCount;
  3115. }
  3116. }
  3117. }
  3118. // Return
  3119. if ($aCount > 1) {
  3120. $summerA *= $aCount;
  3121. $summerB *= $summerB;
  3122. $returnValue = ($summerA - $summerB) / ($aCount * ($aCount - 1));
  3123. }
  3124. return $returnValue;
  3125. } // function VARA()
  3126. /**
  3127. * VARP
  3128. *
  3129. * Calculates variance based on the entire population
  3130. *
  3131. * Excel Function:
  3132. * VARP(value1[,value2[, ...]])
  3133. *
  3134. * @access public
  3135. * @category Statistical Functions
  3136. * @param mixed $arg,... Data values
  3137. * @return float
  3138. */
  3139. public static function VARP() {
  3140. // Return value
  3141. $returnValue = PHPExcel_Calculation_Functions::DIV0();
  3142. $summerA = $summerB = 0;
  3143. // Loop through arguments
  3144. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  3145. $aCount = 0;
  3146. foreach ($aArgs as $arg) {
  3147. if (is_bool($arg)) { $arg = (integer) $arg; }
  3148. // Is it a numeric value?
  3149. if ((is_numeric($arg)) && (!is_string($arg))) {
  3150. $summerA += ($arg * $arg);
  3151. $summerB += $arg;
  3152. ++$aCount;
  3153. }
  3154. }
  3155. // Return
  3156. if ($aCount > 0) {
  3157. $summerA *= $aCount;
  3158. $summerB *= $summerB;
  3159. $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
  3160. }
  3161. return $returnValue;
  3162. } // function VARP()
  3163. /**
  3164. * VARPA
  3165. *
  3166. * Calculates variance based on the entire population, including numbers, text, and logical values
  3167. *
  3168. * Excel Function:
  3169. * VARPA(value1[,value2[, ...]])
  3170. *
  3171. * @access public
  3172. * @category Statistical Functions
  3173. * @param mixed $arg,... Data values
  3174. * @return float
  3175. */
  3176. public static function VARPA() {
  3177. // Return value
  3178. $returnValue = PHPExcel_Calculation_Functions::DIV0();
  3179. $summerA = $summerB = 0;
  3180. // Loop through arguments
  3181. $aArgs = PHPExcel_Calculation_Functions::flattenArrayIndexed(func_get_args());
  3182. $aCount = 0;
  3183. foreach ($aArgs as $k => $arg) {
  3184. if ((is_string($arg)) &&
  3185. (PHPExcel_Calculation_Functions::isValue($k))) {
  3186. return PHPExcel_Calculation_Functions::VALUE();
  3187. } elseif ((is_string($arg)) &&
  3188. (!PHPExcel_Calculation_Functions::isMatrixValue($k))) {
  3189. } else {
  3190. // Is it a numeric value?
  3191. if ((is_numeric($arg)) || (is_bool($arg)) || ((is_string($arg) & ($arg != '')))) {
  3192. if (is_bool($arg)) {
  3193. $arg = (integer) $arg;
  3194. } elseif (is_string($arg)) {
  3195. $arg = 0;
  3196. }
  3197. $summerA += ($arg * $arg);
  3198. $summerB += $arg;
  3199. ++$aCount;
  3200. }
  3201. }
  3202. }
  3203. // Return
  3204. if ($aCount > 0) {
  3205. $summerA *= $aCount;
  3206. $summerB *= $summerB;
  3207. $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
  3208. }
  3209. return $returnValue;
  3210. } // function VARPA()
  3211. /**
  3212. * WEIBULL
  3213. *
  3214. * Returns the Weibull distribution. Use this distribution in reliability
  3215. * analysis, such as calculating a device's mean time to failure.
  3216. *
  3217. * @param float $value
  3218. * @param float $alpha Alpha Parameter
  3219. * @param float $beta Beta Parameter
  3220. * @param boolean $cumulative
  3221. * @return float
  3222. *
  3223. */
  3224. public static function WEIBULL($value, $alpha, $beta, $cumulative) {
  3225. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  3226. $alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
  3227. $beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
  3228. if ((is_numeric($value)) && (is_numeric($alpha)) && (is_numeric($beta))) {
  3229. if (($value < 0) || ($alpha <= 0) || ($beta <= 0)) {
  3230. return PHPExcel_Calculation_Functions::NaN();
  3231. }
  3232. if ((is_numeric($cumulative)) || (is_bool($cumulative))) {
  3233. if ($cumulative) {
  3234. return 1 - exp(0 - pow($value / $beta,$alpha));
  3235. } else {
  3236. return ($alpha / pow($beta,$alpha)) * pow($value,$alpha - 1) * exp(0 - pow($value / $beta,$alpha));
  3237. }
  3238. }
  3239. }
  3240. return PHPExcel_Calculation_Functions::VALUE();
  3241. } // function WEIBULL()
  3242. /**
  3243. * ZTEST
  3244. *
  3245. * Returns the Weibull distribution. Use this distribution in reliability
  3246. * analysis, such as calculating a device's mean time to failure.
  3247. *
  3248. * @param float $dataSet
  3249. * @param float $m0 Alpha Parameter
  3250. * @param float $sigma Beta Parameter
  3251. * @param boolean $cumulative
  3252. * @return float
  3253. *
  3254. */
  3255. public static function ZTEST($dataSet, $m0, $sigma = NULL) {
  3256. $dataSet = PHPExcel_Calculation_Functions::flattenArrayIndexed($dataSet);
  3257. $m0 = PHPExcel_Calculation_Functions::flattenSingleValue($m0);
  3258. $sigma = PHPExcel_Calculation_Functions::flattenSingleValue($sigma);
  3259. if (is_null($sigma)) {
  3260. $sigma = self::STDEV($dataSet);
  3261. }
  3262. $n = count($dataSet);
  3263. return 1 - self::NORMSDIST((self::AVERAGE($dataSet) - $m0)/($sigma/SQRT($n)));
  3264. } // function ZTEST()
  3265. } // class PHPExcel_Calculation_Statistical