Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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))
share|improve this question

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.