11

I know about math.ceil and numpy.ceil, but both of them lack of significance parameter. For example in Excel:

=Ceiling(210.63, 0.05) -> 210.65

numpy.ceil and math.ceil in other hand:

numpy.ceil(210.63) -> 211.0

math.ceil(210.63) -> 211.0

So, I wonder, Is there some similar to Excel's solution already?

Vladimiroff
  • 473
  • 4
  • 11

3 Answers3

11

I don't know of any python function to do so, but you can easily code one :

import math

def ceil(x, s):
    return s * math.ceil(float(x)/s)

The conversion to float is necessary in python 2 to avoid the integer division if both arguments are integers. You can also use from __future__ import division. This is not needed with python 3.

madjar
  • 12,691
  • 2
  • 44
  • 52
  • 1
    How could that be so obvious and I didn't thought of it o_O. Thank you. – Vladimiroff Oct 06 '11 at 09:10
  • 1
    `ceil(212, 10)` returns `210` even it should return `220`. In python 2.x, the integer division is default on integeres. I had a similiar problem (but not quite the same) in my answer and had to force float division by using 1.0. – rplnt Oct 06 '11 at 09:12
  • You're right, I didn't think about it because I mainly code with python3 now. I edited the answer to force the conversion to float. – madjar Oct 06 '11 at 09:16
0

There are a number of libraries which have implemented a lot of Excel formulas in Python.

Libraries such as PyCel, Formulas, xlcalculator and Koala use an AST to translate Excel formulas into Python and so usually have ready-made Python implementation of functions to varying degrees of "how Excel calculates things" (as opposed to how it might be done elsewhere). At the very least there are ideas on how to implement those functions, or potentially use the ones which have the functions already defined.

I am the project owner of xlcalculator so I will use that library in a demonstration. That said the other libraries are well capable of this particular task. Each library has different heritage and so they have different strengths and support different Excel functions.

Usually the above mentioned libraries read an Excel file, translates the formulas into Python and then provides functionality to evaluate. Xlcalculator can also parse a specially crafted dict which is what I'm taking advantage of here.

stackoverflow.py:

input_dict = {
        "Sheet1!A1" : "=Ceiling(210.63, 0.05)"
    }

from xlcalculator import ModelCompiler
from xlcalculator import Model
from xlcalculator import Evaluator

compiler = ModelCompiler()
my_model = compiler.read_and_parse_dict(input_dict)
evaluator = Evaluator(my_model)

print("Sheet1!A1", evaluator.evaluate("Sheet1!A1"))

results in;

>python stackoverflow.py
Sheet1!A1 210.66
bradbase
  • 409
  • 6
  • 9
0

What you can do is this.

ceil = lambda x,y: math.ceil(x*(1.0/y))/(1.0/y)

But it is not foolproof.

rplnt
  • 2,341
  • 16
  • 14