1

Anyone has ever programmed a PHP (or Perl) function to get the ceiling value Excel style?

Improve this question
2
  • What does "Excel style" mean? There is a built-in ceil() function in PHP... Commented Sep 18, 2008 at 0:11
  • To help prevent more of the 'Use the ceil() function' responses ... here is a definition of how the ceil function in M$Excel returns "Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel." Commented Nov 16, 2012 at 19:59

3 Answers 3

Reset to default
8

This should be the answer, from php.net comments:

// MS Excel function: Ceiling( number, significance ) // duplicates m$ excel's ceiling function if( !function_exists('ceiling') ) { function ceiling($number, $significance = 1) { return ( is_numeric($number) && is_numeric($significance) ) ? (ceil($number/$significance)*$significance) : false; } } echo ceiling(0, 1000); // 0 echo ceiling(1, 1); // 1000 echo ceiling(1001, 1000); // 2000 echo ceiling(1.27, 0.05); // 1.30
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Doesn't handle negatives the same way as Excel. To correct that, you could use round() with PHP_ROUND_HALF_UP instead of ceil(). Note that round() also has a precision parameter, although it might only allow rounding to a power of 10.
7

"Microsoft Excel's ceiling function does not follow the mathematical definition, but rather as with (int) operator in C, it is a mixture of the floor and ceiling function: for x ≥ 0 it returns ceiling(x), and for x < 0 it returns floor(x). This has followed through to the Office Open XML file format. For example, CEILING(-4.5) returns -5. A mathematical ceiling function can be emulated in Excel by using the formula "-INT(-value)" (please note that this is not a general rule, as it depends on Excel's INT function, which behaves differently that most programming languages)." - from wikipedia

If php's built in ceil function isn't working right you could make a new function like

function excel_ceil($num){ return ($num>0)?ceil($num):floor($num); }

Hope that helps

Improve this answer

1 Comment

not that simple, excel ceiling function has two parameters.. which ceil to significance. Ceiling( number, significance )
2

Sorry, not quite clear what 'Excel style' is, but PHP has a ceil function.

Improve this answer

1 Comment

As per the M$ Excel documentation ... Ceiling in excel is quite different that the standard Ceiling math function:"Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel."