The Category column on that table will contain the same categories as listed on the annual budget table
Your added Sheets ('tabs' are just the 'index tabs' that hold the name of the Sheet and allow you to bring that Sheet forward) will each contain a Table similar to the one on the Budget sheet. (Numbers will replace the * with the multiplication sign ( × ), which is not a lower case letter x)Įdit the dates in B1 and D1 to show the first and last dates of Q1.ĭuplicate the Sheet 4 times, renaming the first three to Budget - Q2, Q3 and Q4, and the last to Budget - First half.Įdit the dates in B1 and D1 of the Q tables to fit the beginning and ending dates of the quarters.Įdit the dates on the last table to match the first half start and end dates.Įdit the formula in B3 to replace 3 with 6:Įdit the dates to match the start and end of the first half, the second half, and the full year, and rename the sheets to match.Įdit the formula in B3 of the full year table: The twelfth Sheet's table will be edited to become the Q1 summary.Įdit the formula in B3 to add *3, making the budget amounts cover the three month period. This Sheet may now be duplicated 12 times, with eleven copies renamed to match the coming months, and their From and To dates edited to match the first and last date for the respective month. As we want the amounts to be in the correct category, on or after the beginning date, and on or before the ending date, we will use SUMIFS to match those three conditions.Ĭ3: =SUMIFS(Transactions::D,Transactions::C,A3,Transactions::A,">="&B$1,Transaction s::A,"<="&D$1)
The formula in column C of the template uses SUMIF, as the amounts need to match only the category to be included in the sum. (In the version shown, I used Nov 15 and Nov 30 of 2014 to include only some of the dates on the table supplied with the Numbers 3.6.2 template's sample transactions.) On January's table, these dates would be and Jan 31, 2017. If that is the case, the formula in column B of the new table should be a simple transfer and eliminate the /12 part.Ĭolumn C: This column will contain the expenditures occurring in the period between the dates in B2 and D2 in each of the categories. The instructions below assume that you will KEEP the original Budget table and use it to ENTER your MONTHLY budgeted amounts for each month.
During the pause, I reread the Budget page of the template, and realized the table there was intended to contain a monthly budget, not an annual budget.
0 kommentar(er)
