In the Running Balance Sheet post, I said I would do a post to allow you to group outgoings.
So this post will cover that, aimed at a balance sheet. I will create a simple balance sheet as in example 1 in the running balance sheet post, but will add a column for a category. The category use a drop down list as explained in the last post - drop down list.. This will ensure the User enters the exact category in which a withdrawal is assigned to, it prevents misspelling and abbreviation which permits correct calculations on each category..
All we are going to do is sum as per normal, but add "if" and enter the category title, the function will then only sum the values of that specified category.. All really simple, so lets get started.
Example 1: Summing values of a specified category, in this post I will record exactly each step while creating the spreadsheet, as always there are more options to play with..
So this post will cover that, aimed at a balance sheet. I will create a simple balance sheet as in example 1 in the running balance sheet post, but will add a column for a category. The category use a drop down list as explained in the last post - drop down list.. This will ensure the User enters the exact category in which a withdrawal is assigned to, it prevents misspelling and abbreviation which permits correct calculations on each category..
All we are going to do is sum as per normal, but add "if" and enter the category title, the function will then only sum the values of that specified category.. All really simple, so lets get started.
Example 1: Summing values of a specified category, in this post I will record exactly each step while creating the spreadsheet, as always there are more options to play with..
- Open a new spreadsheet and add the titles in cells A3 through to E3, then bold (ctrl & b): Date, Deposits, Withdrawals, Category & Comments
- Now click on the the A column header and drag mouse to the E column header, this highlights all rows in these columns. Now resize the columns by placing the mouse pointer between say B & C headers, click and drag to the right to make all columns wider..
- Formatting the cells - highlight the cells B4 to say, C400 (I am only formatting to row 10 in this example), now right click and select Format, and format as "Accounting"..
- In cell H3 type Cat 1, then right click on the lower right corner of the cell and drag down to cell H5, when released select the "Fill Series" from the pop up menu, now we have Cat 1 to Cat 5 in a list.
- Click in cell D4 and click on the Data Tab, then Data Validation and list from the window that appears. Now click in the "Source:" input box and then click on the cell H4 and drag to H7. Now click OK and you will see a drop down icon next to cell D4...
- Now click on the cell D4 & copy (ctrl & c), then select cells D5 to say D400 and paste (ctrl & v).. These cells will all now have a drop down list with an absolute reference to our create list..
- Now just populate the table with some data..
- With the data entered, we can now calculate the Total cash out and the Running Total.. I have put the labels in cells D1 & D2, shaded them grey and added borders so it stands out.
- In cell E1 type: =SUM(C4:C400) or just copy and paste this.. The Total Withdrawals is now displayed in cell E1..
- In cell E2 type: =SUM(B4:B400)-SUM(C4:C400) or just copy and paste again.. The current balance is now displayed in cell E2..
- So next we want to know how much was spent on each category... In this example, I will just use Cat 1 & Cat 2.. As above, put labels in cells A1 & A2, format as above..
- To calculate what was spent in Cat 1 use the sumif formula, so it is only adding if the category is Cat 1.. As you start to type sumif, excel will advise you of how the formula is made up.. Range is the where your Categories are named, Criteria is what you want to sum on, ie Cat 1, put this in quotes - "Cat 1" and [sum_range] is where you want to calculate from, ie the Withdrawals column
- Type or copy and paste the formula: =SUMIF(D4:D1400, "Cat 1",C4:C400) This formula is looking in the cells D4 to D400 for the string "Cat 1", each time it finds a Cat 1 it will then look in the relative C column and add if there is a value.
- To do the same for Cat 2, just copy the formula and change Cat 1 to Cat 2..
- Note: When writing a formula, if you click in any cell it will map that cell to the formula, this has cause a lot of frustration to a lot of people that thought they could just click off the cell they are writing the formula in.. You cant without changing the formula, best either press enter or click on the green tick to left of the formula bar..
No comments:
Post a Comment