Hi,

I'm having an issue with Excel. I am using Windows 7 (don't know if this matters) and Excel 2007. I recently changed to a new laptop and notice a change in the way Excel behaves, and I don't understand why. When I have a simple formula (in my budget, as an example), and move a line, my formula shows a #REF error (or errors). For example:

Bank $257.60 (on hand)

Electricity $209.00Paypal $9.00 (bills)

Balance $39.60The formula adds the cells between "electricity" and "Paypal" including the blank one in the middle and subtracts from the cell containing "257.60" to give the balance ($39.60). If I delete the row between (or cut and paste the "Paypal" line, I get the #REF error. On my last computer, it would automatically adjust to the new settings. Why would that be? And is there a simple fix for this? (The last laptop was also windows 7 and Excel 2007).

Thanks,

Patrick

✔ Best Answer

Guess what? it's your formula. :-) Your problem is in how you have your formula constructed.

When you specify each cell in a range as in:

D6+D7+D8+D9

If you remove or delete one of the cells you will get a #REF error because the formula is trying to access an invalid cell.

Try this and see how it works:

=SUM(C4:C5)-SUM(D6:D8)

The SUM function will ignore any blanks or text.

MIKE

I think we could be of more help if you provided the actual formula that you are using instead of just describing what it does. Cell references for your example data would be helpful also.

The following link contains instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Actually, Derbydad3, the formula is irrelevant. I wasn't asking for help with a formula. I was asking for help with a setting change that would allow me to move something within the cells contained in a formula (any formula) without causing an error, as it was with an identical setup before I switched to a new computer. I don't need the condescention and I'll find the answer elsewhere.

Have you checked your options? Click the Microsoft Office button

Click on the Excel/Word Options button to bring up the Excel Options dialog box (at the bottom of window)

Click on the Formulas button in the left hand pane

Under "Error Checking Rules"

Make sure you have Un-Checked the box "Formula Referring to Empty Cells"See if that helps.

Also, using your data I am unable to duplicate your problem.

Perhaps it is your formula that is causing the problem.

A #REF error occurs when a spreadsheet formula contains incorrect cell references.

MIKE

I'm not sure why you found my response condensending. It certainly wasn't meant to be. By posting the formula, data and cell references, you would allow us to test your exact set up and perhaps offer a solution.

Since, as Mike said, reproducing the error wasn't possible with just your data, having the rest of the details might help us help you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi Mike. Thanks for the reply. Here is the actual formula used: =SUM((C4+C5)-(D6+D7+D8+D9+D10)) where C4+C5 are our incomes and the rest are the outgoes. Try the formula with this:

C4=500

C5=500

D6=100

D7=blank

D8=blank

D9=250

D10=60

Row 11 is blank.

The formula is in cell E12. If I delete the entire row 7 (and tell it to move cells up), instead of adapting the formula and automatically just deleting D7 out of it (like it used to do on the previous install), it gives me the #REF in the formula. I used to get =SUM((C4+C5)-(D6+D7+D8+D9)) (as it would even adapt that when it moved the rows up, it renamed them within the formula and left out the last row before the total). NOW I get =SUM((C4+C5)-(D6+#REF!+D7+D8+D9)). ALSO, if I go back to the original and drag D9 (250) to D8, I get this: =SUM((C4+C5)-(D6+D7+#REF!+D8+D10)). In order to preserve the formula, I have to copy and paste and then go back to the original cell and "clear contents". Cutting or deleting will result in the #REF as well.And Derbydad03, perhaps it wasn't as condescending as it appeared after a generally long day, looking at it fresh with a new set of eyes this morning. I apologize for that comment. It was wrong. I know you were just trying to help.

Thanks,

Patrick

Guess what? it's your formula. :-) Your problem is in how you have your formula constructed.

When you specify each cell in a range as in:

D6+D7+D8+D9

If you remove or delete one of the cells you will get a #REF error because the formula is trying to access an invalid cell.

Try this and see how it works:

=SUM(C4:C5)-SUM(D6:D8)

The SUM function will ignore any blanks or text.

MIKE

Just by way of a more complete explanation of why your formula is incorrect: Excel calculates from the "inside out",

=SUM((C4+C5)-(D6+D7+D8+D9))

So with your formula, the first thing it tries to calculate are the two expressions in parentheses ( ):

(C4+C5)

and

(D6+D7+D8+D9)Your adding specific cells, if you remove one of the cells, one of the formula parameters is removed, and up pops the #REF error.

The SUM() function does not come into play until after the inner expressions have been evaluated.

MIKE

Yannow, now that I look at it, ALL of my formulas before were "between this and that" with the colon. Didn't even occur to me! NOW I get it. I didn't even realize I was doing it differently when I set this formula up. Thanks!

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History