AVERAGEIF formula doesn't work in xls

[mferraricloudsurfers]

NPOI Version

2.7.0

File Type

• XLSX
• XLS
• DOCX
• XLSM
• OTHER

NPOI File

5f88c7e2-e3d7-4594-a8a6-f8d17516f177.xls

Reproduce Steps

Issue Description

var fs = new FileStream(file, FileMode.Create, FileAccess.Write)
IWorkbook workbook = new HSSFWorkbook();
IRow row = excelSheet.CreateRow(0);
row.CreateCell(1).SetCellFormula($"SUM(B5:B10)");
row.CreateCell(2).SetCellFormula($"AVERAGEIF(B5:B10,\"<> 0\")");
workbook.Write(fs);
fs.Close();

Opening the result xls file with Microsoft® Excel® per Microsoft 365 MSO (Version 2403 Build 16.0.17425.20176) 64 bit (Italian), the SUM formula is correctly translated and works. Instead, AVERAGEIF is not translated and the cell is marked in Excel how error with #NAME not recognized.

[Bykiev]

Can you please attach the Excel file produced by NPOI? In Excel file there shouldn't be any localized data

[mferraricloudsurfers]

Opening the file in protected view mode, will display correctly the result

While, if I open the file in edit mode and I replace the "AVERAGEIF" with the corresponding localized command version, it works.

[Bykiev]

can you please test with xlsx?

[mferraricloudsurfers]

can you please test with xlsx?

it works with xlsx

[Bykiev]

Seems to be a bug, I'll try to research it later

[Bykiev]

I did a research and AVERAGEIF formula is not supported by excel 97-2003, when you saving the workbook with MS Excel it warns that this function is not supported by versions prior 2007. When opening such file with MS Excel 2007+ it is opened in compatibility mode. I believe it's not supported by POI and the workbook always saved in EXCEL97 format for xls and it won't be fixed in NPOI.

@tonyqus, what do you think?

[tonyqus]

Since AverageIf function is not supported in Old Excel 97-2003, this bug is invalid.