Table AutoExpansion incorrectly changes formula

Question

I have a table in an Excel sheet where one column is calculated. When adding a new row by typing, the table automatically expands, but wrongly changes the formula of that column in the row above. It then marks the cell with a green triangle and offers me to 'Restore to Calculated Column Formula'.

How can I stop Excel from changing the formula like that?

In case it matters, this is the formula in cell M15. The data starts at row 12. It's function is to show a day total next to the last entry for that day. If required I can upload a sample sheet, but I'd have to remove sensitive data first. When I add a new entry in row 16, L15 changes to L16 in both the new entry and the previous entry.

=IF(DAY(I15)=DAY(I16);"";SUM($L$12:L15)-SUM($M$11:M14))

Answer 1

I tried to replicate your issue, but it work OK for me and does not modify the formulas as described.

Here's an alternative formula for column M anyway which may help

=IF(DAY([@Date])=DAY(OFFSET([@Date],1,0)),"",SUMIFS([Data],[Date],[@Date]))

I have used table column names of Date for column I and Data for column L, which you will need to change to match your table