Some formulas in excel copy but don't compute.

[Sandra Blank]

I am having a situation in editing in Excel, Office 365.  I can copy and paste a formula =SUM for a column or row.  The formula shows but the column or row doesn't compute.  This doesn't happen for every row or column.  Some do and a few don't.  I have tried deleting the formulas and retyping them.  Is there something else I can try?

[#2]

Hi Sandra,

could you please double check if the cells with non-computing formulas are text formatted (right click->format cells? This would cause formulas showing and not calculating. Another possiblilty is if you have circular references in the workbook. You can double check it in Formulas->Formula Auditing->Error Checking->Circular References. Please let me know if any of these is present in your workbook

Thanks

Yury 

[#3]

Yuri

     They are currency formulated.  Where do I access a tab for Formulas and Formula auditing?   I have not found any circular references.  I appreaciate your assistance in this problem of mine.  I have been using the spreadsheet for my cash flow for going on three years and use the total amounts to do my taxes and those of my daughter.  The system has been almost flawless until now.  I have had some circular references and been able to correct them.  Nothing like the present situation have I any experience with.

Thank you!

Sandy

[#4]

Hi Sandra,

would you mind sharing the part of the file, where the formulas do not work? 

In relation to circular references, if you select the 'Formulas' tab on the ribbon, then in the 'Formula Auditing' area there is an 'Error Checking' button with a small arrow on the right side pointing down. If you click the arrow, it will expand to 3 options, including 'Circular References'. 

Thanks

Yury

[#5]

Here is the file, I think.  My sharing abilities are not practiced.  I hope this helps.

Sandra

[#6]

Circular references is in row 251 (at least). See AC251, etc.

In general, here is much better to use SUBTOTAL instead of SUM

[#7]

Hi Sandra,

as Sergey mentioned, there are multiple circular references in the file. You need to resolve them all for the formulas in the worksheet to work correctly

Yury 

[#8]

Thank you so much for the imput.  I will look into SUBTOTAL.  

[#9]

Because of your help I am searching through the file and correcting my formulas.  I know my sheet is sophomoric and would gladly accept any instruction on improving it.  Thanks again!

Sandy

[#10]

Hi Sandra,

Even better to separate records with the transactions and analytics on them (in your case subtotals on periods).

Transactions could be in Excel Table, analytics in PivotTable. In attached file is an example for one of transactions sets in 2016 (see 2016(2) sheet). Figures slightly differ from your summary, it looks like i deleted few records when transform your data.

And it will be much easier in maintenance - you just add the date and sum into the table, all calculations will be done in PivotTable after you refresh the data. No headache with formulas.