pythonexcelexcel-formulaopenpyxlmaxifs

Excel incorrectly processes formulas cross-referencing each other

I have generated an Excel file, with the following columns (cf. minimal code below):

Formulas are OR-based for column Resolved, MAXIFS-based for column Any in ID resolved (cf. minimal code below).

Example formulas (note: German locale for installed Excel - does not matter; English formula names are recognized):

Issue:

I would expect that at the beginning both the columns Resolved and Any in ID resolved are all FALSE and that values are auto-set by the formulas to TRUE as soon as the user sets the column Manually-set resolved to TRUE.

Unfortunately, that is not what happens - rather, when opening the generated Excel file, the cells all display #NAME? (cf. screenshot below), as if there was an issue with the formulas - but I cannot figure out what it is. And sometimes, when fiddling around with the generated Excel, 0 is displayed (which can be seen as representing FALSE) - but then column Resolved does not auto-update when Manually-set resolved is set to TRUE.

Screenshot of issue

Notes:

Questions:

Minimal example code to generate a test Excel file:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Example"

# Column headers
ws['A1'] = "ID"
ws['B1'] = "MANUALLY_SET_RESOLVED"
ws['C1'] = "ANY_IN_ID_RESOLVED"
ws['D1'] = "RESOLVED"

num_rows = 10

# Populate example data for ID (e.g. 3 groups: 1,2,3 repeated)
for row in range(2, num_rows + 2):
    ws[f"A{row}"] = (row - 2) % 3 + 1  # Cycle through 1,2,3

    # MANUALLY_SET_RESOLVED starts empty (FALSE/blank)
    ws[f"B{row}"] = None

    # ANY_IN_ID_RESOLVED: MAXIFS over RESOLVED column for group
    ws[f"C{row}"].value = f'=MAXIFS($D$2:$D${num_rows + 1},$A$2:$A${num_rows + 1},A{row})'

    # RESOLVED: OR of MANUALLY_SET_RESOLVED and ANY_IN_ID_RESOLVED
    ws[f"D{row}"].value = f'=OR(B{row}=TRUE,C{row}=TRUE)'

wb.save("example_file.xlsx")

Update for @MGonet:

The MAXIFS appears to have issues somehow even with numbers rather than booleans - see this screenshot:

enter image description here

But when using MAXWENNS instead, the formula returns a result - not the correct one though, since it will always return 0 for any booleans (even TRUE). This indicates that there might be a localization issue, and that booleans might need to be converted to numbers via -- inside the formula:

enter image description here

Also, in such a case, without overwriting the Resolved column manually with booleans, MAXWENNS returns 0, Excel displays circularity warnings and also visualizes that via a blue-red double arrow (cf. screenshot), and setting Manually-set resolved to TRUE does NOT update Resolved - which it should, however.

enter image description here

Then, only when double-clicking into a Resolved cell (but I want auto-re-calculation) and pressing Enter, is the value set - to 0 - which is incorrect, since it should be TRUE. I do actually not understand how =OR(TRUE;0) returns 0 rather than TRUE - that makes no sense to me. Apart from that returning 0 for =NOT(0) is also plain wrong:

enter image description here enter image description here

Solution

  • Answer to the question about the #NAME? error:

    The Excel function MAXIFS was introduced after publishing the Office Open XML file system in 2007. Thus not all spreadsheet applications will support this function. To mark this, such functions are prefixed by _xlfn in file storage.

    In your case the Excel version supports this function MAXIFS but it is not marked by _xlfn prefix in file storage because openpyxl directly writes to file storage. Excel marks this problem with @ in formula string in GUI and your German GUI will not translate it to MAXWENNS. That's why the #NAME? error in your German Excel while trying to evaluate.

    Since OpenPyXL directly writes into file storage that prefix must be set in formula string. Wrong is formula =...MAXIFS(.... Correct is formula = ..._xlfn.MAXIFS(....

    So in your code example

    ...
ws[f"C{row}"].value = f'=_xlfn.MAXIFS($D$2:$D${num_rows + 1},$A$2:$A${num_rows + 1},A{row})'
...

    will avoid the #NAME? errors.