1
Vote

Cannot re-save due to Formula Length Longer than 8192 characters

description

I've created a spreadsheet using EPPlus. It opens fine (I use Excel 2016), but when I try to save it I get the error message:

"One or more formulas in this workbook are longer than the allowed limit of 8192 characters. Cell 'Rooms!B2' contains one of these formulas. To avoid this limitation, save the workbook in the Excel Binary Workbook'"

The formula in Rooms!B2 is:
=IF(A2<>"", COUNTIFS(Participants!D2:D112, A2, Participants!I2:I112, "Boy"), "")
which is obviously less than 8192 characters long.

If I delete and re-enter the formula then I can save the spreadsheet, so it appears to be some thing about how EPPlus is writing the formula.

file attachments

comments

ianthomas_uk wrote Apr 12 at 4:37 PM

I've been able to work around this by using a for loop to apply similar formulas to each cell, instead of applying the formula to a range of cells.

ianthomas_uk wrote Apr 12 at 4:39 PM

RoomingList-17-98486-31.xlsx is my original test file that shows the problem.
RoomingList-17-98486-7.xlsx is the newer file with the workaround.