You can insert, delete and move rows and the balance formula will always reference the cell above the current balance. The OFFSET function does not directly reference cell F14, so if you delete row 14, no problem.
To refer to the previous balance, we can use the current balance (F15) as the reference and use -1 for the offset rows and 0 for the offset columns like this: =OFFSET(F15,-1,0). Syntax: = OFFSET( reference, rows, columns,) The OFFSET function allows you to create a reference by specifying the number of rows and columns offset from a particular reference. Solution #1: Create a Running Balance using the OFFSET Function There are two fairly simple solutions for creating a robust running balance that don't break when you insert, delete or move rows. So, you need to use formulas that don't cause hidden or hard-to-detect errors. If you are designing a spreadsheet with a running balance, it's likely that you or somebody else may want to insert, delete, or move rows. If I have an error in a spreadsheet, I would much rather see a glaring #VALUE! or #REF! error than have errors I can't see. In fact, the running balance is wrong in both rows 2 and 3, because the formulas are not referencing the correct previous balance. The scary thing about this example is that there is no obvious indicator of the errors. In this example, row 3 was cut and inserted above row 2.
Problem #3: Moving a Row Causes Hard-to-detect Errors It isn't wise to rely on the error checking feature because it doesn't catch all errors. In this case, a little green triangle indicates an inconsistent formula. The error checking feature in Excel was designed to help identify these types of errors. The problem is that the balance formula in row 3 is still referencing the balance from row 2.
In this example, a new row was inserted above row 3 and the balance formula was then copied down. Problem #2: Inserting a Row Causes an Incorrect Running Balance While annoying to fix, it's not as scary as the errors that are harder to detect. This error is very obvious, and can be fixed fairly easily by fixing the balance formula in row 3. All dependent formulas also contain #REF! errors. The balance formula in row 3 now contains a #REF! error because the reference to the balance in row 2 was deleted. Problem #1: Deleting a Row Causes a #REF! Error Another approach is to leave a blank row underneath the columns labels, or use the first row to enter the carry over balance instead of using a formula. NOTE Why use SUM instead of =D15-E15+F14? Answer: The formula in the first row would lead to a #VALUE! error because of trying to add a text value (=5-1+"Balance").
0 kommentar(er)
