Excel formula bug with adjacent cells This is on an excel budget sheet I\'ve bee
ID: 3560795 • Letter: E
Question
Excel formula bug with adjacent cells
This is on an excel budget sheet I've been using.
Column B is for the price of the item (-45 for gas money, 1700 for paycheck, etc).
B1 is the current amount of money in my checking account.
Column E has the following formula:
=SUM($B$1:$B1)
auto-filled down from E1 to the last item I have currently planned ahead for (currently down 115 rows).
As the formula moves down, it does a sum operation from B1 to the current row, effectively taking my current account balance against all planned changes in money.
Example values:
Current Balance
150.00
The odd thing is this: on cells E2 to E14 I get a "Formula Omits adjacent Cells" error. Why it stops at E14 I don't know, and that's what makes me think it's a bug.
If I correct the formula, it just changes it from =SUM($B$1:$B14) to =SUM($B$1:$B15) (which isn't what I need at all), but then tells me that the formula doesn't match the pattern of the others around it.
Current Balance
150.00
8/23/14 Current Cash in Checking =SUM($B$1:$B1) is 150.00 Recurring -15.00 8/24/14 Haircut =SUM($B$1:$B2) is 135.00 Recurring -17.72 8/27/14 Dental Insurance =SUM($B$1:$B3) is 117.28 Recurring 52.30 8/30/14 Check for Miles Driven =SUM($B$1:$B4) is 169.58Explanation / Answer
.Hi., As a side note, select the error box for one of your cells, say E5.,/
Look at the error box., The second option is to "Update Formula to Include Cells"
What I think you now have is a Formula that refers to your column of data.,
(Most Likely B1:B14).@
On the Excel Sum Formula, you had "Sum(B1:B4)", and Excel is thinking you meant to sum the entire column of data (B1:B14). That's what the "adjacent cells" means. Excel is giving a "Heads up" that your Sum formula "Could" be in error.
>> Why it stops at E14 I don't know.,
I believe because your Sum formula in Cells E15 and lower now actually include the data that is in B1:B14. This is what makes be believe you only have data in B1:B14 at the moment.
I don't think it's a "bug" per say. It's just trying to help.
On a clean sheet, if you have numbers in A1:A5, and elsewhere have a formula
=Sum(A1:A3), you will get the same error, as it thinks the Sum formula is missing A4:A5.
However, the formula =A1+A2+A3 will not generate that error.
Hence, that's one of the advantages of the Sum formula.
Knowing this behavior, an alternative if you wish is to change the formula in E2 to :
= E1 + B2, and copy down.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.