Excel Table changes 2nd to last line when I add new line

[Domroy]

I have a table that has formulas in column B and D that are based on data in column C. It's not complicated at all. However, I have formatted the data in the table as Franklin Gothic and size 14. And here's the strangest thing: when I add a new line, it becomes a part of the table like it should, but changes the format in what is now the 2nd to last line, and ONLY in the columns with formulas...to Ariel 12. I've tried reformatting the table. I've tried rebuilding the table in a new workbook. Still does it. Has anyone ever heard of this? Thanks!

 

[jasonb75]

I've tried the same formats on a table with formulas and nothing is changing. I have seen problems with certain types of formula in tables, but formatting is a new one.

Do you have any vba in the workbook? Something there could be applying formatting changes.

 

[Domroy]

Nope. No VBA.

I even rebuilt the table in a new workbook, and it did it again!

 

[jasonb75]

Can you upload a copy of the workbook to dropbox (or simillar) and post the link here?

Please remember to remove any personal / confidential data or replace it with fictional data before uploading.

 

[Domroy]

Here's the link to DB. Let me know if there's an issue. A friend of mine asked me to fix this for him, and I'm baffled. It's on the CA Stats By Date tab and the US Stats by Date tab.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[jasonb75]

I'm seeing the problem that I mentioned earlier,

I have seen problems with certain types of formula in tables,

the formula is being changed as well, I'm guessing that the font change is related.

The formula problem occurs when an expanding range is used in a table formula with normal references used instead of table references. When you add new rows the formula appears to get confused as to where the table ends. It can be fixed by setting the range using a different format or formula. This one is for D4, to be filled down after if it doesn't populate automatically.

=AVERAGE($C$4:[@[NEW
DAILY
CASES]])

This one for J4

=AVERAGE($I$4:[@[NEW
DAILY
DEATHS]])

 

[Domroy]

Amazing. That worked for those columns. But columns B and H are still doing the format change thing when I add a line. Ideas?

 

[jasonb75]

Strange, the format didn't change for me on those columns, but the fix is the same, just change AVERAGE to SUM.

 

[Domroy]

Yup! Thank you!