Monthly Budget Template not being Monthly?

Question

Level 1

0 points

Monthly Budget Template not being Monthly?

Hi!

I am trying to make a monthly budget and I liked the template offered to me in Numbers. have changed the names I wanted and everything it fine - except I don't understand how it becomes monthly?

I fill in my transactions, put a date but then they all end up in the same "monthly budget" form! How would I go about to fill in all my transactions in the "Transactions" file, choose a date and dependent on that date it will end up in separate sheets i.e. one called "July", "August" etc.? As I don't want a combined result, I want to be able to compare it month by month, sheet by sheet - not having to open and save Numbers month by month.

It would be fine to just have a separate Transaction sheet for each month linking to it's own Monthly Sheet, if not possible to use the same.

Anyone knows how to do this?

Thank you in advance!

MacBook Pro (Retina, 13-inch, Mid 2014), iOS 8.3

Posted on Jun 12, 2015 3:21 AM

Reply

Answer 1

SGIII

Level 8

36,128 points

Jun 12, 2015 5:02 AM in response to Huggebugge

I'm not sure if this is the kind of thing you are looking for, but here (Dropbox download) is "expansion" experimentation I did to the template a while back that may give you some ideas.

SG

Reply

Answer 2

Huggebugge Author

Level 1

0 points

Jun 12, 2015 6:31 AM in response to SGIII

Thank you for you quick reply SGII!

What you posted are interesting and it looks like a possible solution!

However, here is more of what I was thinking: (with made up numbers)

https://www.dropbox.com/s/6t9y5a0q5w08auk/Budget.numbers?dl=0

As you can see I have different sheet for each month but when adding the transactions it add it in all the sheets.

How/what formula is it to distinguish so only upload the transactions with "July" or number 7 in the date to July sheet and

vice versa for the other months?

Thank you in advance.

Reply

Answer 3

SGIII

Level 8

36,128 points

Jun 12, 2015 9:21 AM in response to Huggebugge

Rather than keep the data in separate tables and then try to consolidate it, I generally find it easier to keep transaction data in one table and extract summaries (sums, counts) and subsets (using built-in Numbers filters).

An advantage of a separate data table for each month would be if different people have to input values. Another advantage might be if you are working with very large datasets.

Otherwise, recommend trying the one data table approach, as you see in that template (and also in some of the other templates).

SG

Reply

Answer 4

Huggebugge Author

Level 1

0 points

Jun 13, 2015 1:08 PM in response to SGIII

Thank you once again for you reply.

I will experiment with the file you send me, but would you know the formula that would do what I am trying to do?

Just so I can experiment with that and also learn!

Reply

Answer 5

Huggebugge Author

Level 1

0 points

Jun 13, 2015 1:36 PM in response to Huggebugge

Hi again,

Or what would the formula be if I added a final column in the transaction sheet called "month"? Would that help it to only show the numbers with the month July and then change this for every sheet to match the month? I have change it to SUMIFS instead of SUMIF - but it says it requires an odd number of arguments... This is my formula: SUMIFS(Category;A1;$Amount;Month)

Reply

Answer 6

Huggebugge Author

Level 1

0 points

Jun 13, 2015 2:07 PM in response to Huggebugge

Hi again,

Or what would the formula be if I added a final column in the transaction sheet called "month"? Would that help it to only show the numbers with the month July and then change this for every sheet to match the month? This is my current formula : IF(Month="July";SUMIFS(Category;A1;$Amount);0) however it only returns 0!

Reply

Answer 7

SGIII

Level 8

36,128 points

Jun 13, 2015 4:38 PM in response to Huggebugge

Huggebugge wrote:

Or what would the formula be if I added a final column in the transaction sheet called "month"?

Not sure I follow. In the document I linked to the 'Transactions' sheet already contains a 'Month' column, which is referenced by SUMIFS formulas.

A description of SUMIFS is here. You can also find a description in languages other than English.

SG

Reply

Answer 8

Huggebugge Author

Level 1

0 points

Jun 14, 2015 4:01 AM in response to SGIII

Sorry about the confusion.

Here is my current sheet : https://www.dropbox.com/s/6t9y5a0q5w08auk/Budget.numbers?dl=0

Under the July sheet I have tried the IF-formula, but it only returns 0.

Under the August sheet I have tried the SUMIFS-formula and tried to replicate the sheet you provided - but it says "SUMIFS requires that all range arguments are the same size."

I know I am making a mistake in both formulas but I don't understand where and why?

As I am trying to say: If the name in C1 = (is the same as) the name in [Sheet Transactions:Column B] (July=July, August=August) then return the Value inputted under the right category (Amount in Category 'Rent' under Row 'Rent' in July Sheet), otherwise count it as 0.

_____

The reason I don't want the months in the same table is that it gets weird with the Budget/Actual numbers - as it adds up for every month but I only want my Actual/Budget by Month not summed up. For example here: the Budget of 350 is a monthly budget but it then adds month 1, 2, 3....+ under actuals. And it also looks messy and confusing as I now start in July as well.

I hope you understand and can help me.

Reply

Answer 9

Huggebugge Author

Level 1

0 points

Jun 14, 2015 8:17 AM in response to SGIII

HI SGII!

Thank you for you help and time on this matter. With your sheet I managed to solve it with this formula: SUMIFS($Amount;Category;A2;$Month;$C$1) - my mistake in the last post was that I had the Amount on the wrong position.

Reply