Wrong formula calculated cell value

Issue #825 invalid
Artur Zych created an issue

Hi,

I'm having a problem reading cell value that is calculated based on a formula. Even though I've passed the data_only argument and openpyxl returns a calculated value, it's not being calculated correctly. I'm suspecting that this could be due to the formula elements refering to cells with another formulas.

Example: cell "A1" with formula "=SUM(D36:G36)", evaluating in excel to 24.50 cell "D36" with formula =IF('Source WS'!F9>60,IF('Source WS'!F9<=90,'Source WS '!C9,0),0)/1000, evaluating in excel to 24.50 rest of the cells in range with similar formulas to "D36", evaluating to 0.

When trying to read value without data_only=True, I get a "=SUM(D36:G36)", so row/column given is correct.

With data_only = True, openpyxl returns a calculated value of "A1", but it's not the correct value i.e. not "24.50" in this case.

This is roughly the code I'm using (also tried internal_value, iter_rows etc.): wb = openpyxl.load_workbook(path_to_file, data_only=True) ws = wb.worksheets[0] print(ws.cell(row=39, column=4).value)

Thanks in advance!

Comments (4)

  1. CharlieC

    This has nothing to do with openpyxl but with whichever application last evaluated the formula, all we can do is return the cached value.

  2. Log in to comment