Python openpyxl formula incorrectly displayed in Excel

Question

I have a problem with the correct export of a formula via Python openpyxl in Excel. I want to export the formula for the average and the 75% quartile over a range in Excel. I am using the German Excel version. This works quite well for the average, here the English formula "=AVERAGE(A2:A7)" is shown as the German formula "=MITTELWERT(A2:A7)". Unfortunately, this does not work for the 75% quartile, here the English formula "=QUARTILE.INC(A2:A7,75%)" is shown as "[email protected](A2:A7;75%)", which leads to an error in Excel, and not as the German formula "=QUARTILE.INKL(A2:A7; 0,75)", which would work. Here is a sample code:

import pandas as pd
import os

# Change directory
directory = r'C:\Users\abc\Desktop\Test Formula'
os.chdir(directory)

# Create dataframe
data = {
    'Test1': [1, 2, 3, 4, 5, 6],
    'Test2': [16.46, 17.21, 14.98, 21.3, 89.1, 71.77]
}

data['Test1'].append('=AVERAGE(A2:A7)')
data['Test2'].append('=AVERAGE(B2:B7)')

data['Test1'].append('=QUARTILE.INC(A2:A7,75%)')
data['Test2'].append('=QUARTILE.INC(B2:B7,75%)')

df = pd.DataFrame(data)

# Create an Excel file
output_file = os.path.join(directory, "Output.xlsx")
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:  # Specify engine='openpyxl'
    df.to_excel(writer, sheet_name='Test_Output', index=False)

In (the German) Excel, this code will produce the following formulas/evaluations:

=MITTELWERT(A2:A7) (evaluates correctly to 3.5),

[email protected](A2:A7;75%) (evaluates incorrectly to #NAME?)

Instead of [email protected](A2:A7;75%), there should be =QUARTILE.INKL(A2:A7; 0,75) (which would correctly evaluate to 1). Does anyone have an idea why this code does not produce the correct formula in Excel?

Answer 1

You need to add _xlfn. Below should work:

data['Test1'].append('=_xlfn.QUARTILE.INC(A2:A7,75%)')
data['Test2'].append('=_xlfn.QUARTILE.INC(B2:B7,75%)')

Here is the note from openpyxl: openpyxl doc: