IWORK NUMBERS TEMPLATES FULL

Click the green checkmark on the formula editor to confirm the formula, close the editor and jump back to the Budget - Jan sheet.Ĭell B3 in the (monthly) budget table will display the amount from the annual budget table divided by 12.type '/12' (no quotes) after the cell reference token in the formula.Click cell B2 (first data cell in the Budget column) in the (annual) budget table to insert the cell reference in the formula.Click the Budget tab to bring the annual budget to the front,.In cell B3 (first data cell in the Budget column), enter = to open the formula editor.Ĭlick on ƒx at the left end of the formula editor and drag it up to the top of the table.Ĭlick in the formula editor to ensure the insertion point is in the editor box, then 'Category), then press option-up arrow to add a second Header row above the first.Įnter the text 'From: ' (without the quotes) in cell A1, and the text 'To: ' in cell C1 Click the v that appears toward the right end of the tab and choose Duplicate.*Ĭlick the tab to bring the new Sheet to the front.Ĭlick any cell in Row 1 of the table (eg. Hover the mouse pointer over the Budget tab. To create the first Monthly Sheet and table: The Difference column and the totals will use the same formulas as on the Annual budget table. The Actual column will use SUMIFS to return the actual expenditures on in each category, and in this time period. The Budget column on that table will draw its data from the annual budget table, modified to show the amounts applicable to the period shown by this table.

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.

IWORK NUMBERS TEMPLATES FULL

(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.

Iwork numbers templates