• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Initial value in calculated column

dolcem

New Member
I'm an Excel beginner working my way through the Chandoo videos and I have a really simple question that I haven't been able to find the answer to. I'm really sorry to post such a simple question on here but I've spent over an hour on google searching and still can't find the answer.

I am using a table in an Excel (2007) spreadsheet to calculate an expense report. Row 1 has the titles (date, balance, cost, etc.). Column A is the date, Column B is my balance, and Column C is the cost of the item, etc. So B2 is my initial balance and C2 is the cost of the first item I purchased. I record the cost of each item in the C column, and B3 is the formula =B2-C2, B4 is =B3-C3, and so on and so forth. The problem is that every time I add a new expense in, a new row in the table is created and the default value is my initial balance (the value in B2), and then I have to click on the previous B cell and drag it down to make the formula carry over. I don't want to have to do this every time I add a new expense--I want to add the new expense in the C column and have the B cell in the same new row automatically calculate the formula rather than display the initial value (B2).

Thanks!
 
When you get to the last row in the table, with your cursor in the last right hand data cell press the tab key, this will insert a new row to your table but also will copy down all formulas/functions, if you manually create a new row this does not happen
 
When you get to the last row in the table, with your cursor in the last right hand data cell press the tab key, this will insert a new row to your table but also will copy down all formulas/functions, if you manually create a new row this does not happen

I'm sorry, I tried that but it still displays the initial value (from B2).
 

Attachments

  • initial cell.png
    initial cell.png
    203.7 KB · Views: 6
Your PNG shows a different value in B426, also you have no data in A426, if your cursor was in F425 and you hit tab your cursor would have carried on from A426.
 
Your PNG shows a different value in B426, also you have no data in A426, if your cursor was in F425 and you hit tab your cursor would have carried on from A426.

The value in B426, 7349, is my initial value (in B2). I put my cursor on F425, hit tab, and it carried over to A426, automatically creating a blank new table row with value '7349' in cell B426. I want B426 to have a value of '377.1,' since that is B425-C425. The only way I get this is by selecting B425 and clicking and dragging the little black square in the bottom right corner to B426.

I've attached another .png (just in case I did the first one wrong) and a .png of what the top of the table looks like.
 

Attachments

  • initial cell2.png
    initial cell2.png
    191.5 KB · Views: 2
  • initial cell3.png
    initial cell3.png
    230.4 KB · Views: 3
You must have a formula/function that is forcing the value. Can you use the "Upload a File" button to upload the workbook.
 
@dolcem,

I'm not sure how this error was possible. My understanding was that Excel Tables were supposed to maintain self-integrity and force the entire column to have the same formula. However, the issue seems to come from the absence of a formula in first row in the table.

Please see my proposed changes attached. I replaced all of the formulas in column B with a consistent formula and inserted a new row at the top of the table to provide the "initial balance" (as a negative cost based on some other entries).

I hope that helps.

Regards,
Ken
 

Attachments

  • Gastos_KenU.xlsx
    34.2 KB · Views: 2
Back
Top