0

I have a dataset of expenses as rows and a monthly budget $ in a cell.

I want to plot two bar horizontal bar charts where the X-axis is the days of the month (all of them, not just the one we're up to now).

One bar chart will be a running total of the expenses, whilst the second bar chart will be what the average dollar amount would be at the current day.

The end goal here is to have an idea of "if I had $2000 for the month how much (visually) can I be spending a day vs what I've actually spent".

I've used a budget tool that had this sort of visual representation and I found it very useful

  • pls share a copy of your sheet – user0 Mar 2 at 5:31
  • @Dan are days of the month columns? – ahorn Mar 3 at 17:02
1

Preliminary answer

You said that expenses are "rows" and mentioned that you want a chart showing the running total per day. So, I assume you are using a new column for every day of the month, and filling in expenditures in the corresponding cells. However, this would take up too much space on the sheet, because you will end up with 31 columns spanning each month (and presumably a new tab for every month). I recommend rather using the columns for expenditures, then inputting data on new rows, as then the 31 rows will take up less space.

If you are not so concerned about all the admin of creating a chart showing the running total per day, you can rather use each cell as a total per month. In this case, using one column for every month, and rows for expenditures, is perfectly fine, as you may have many categories for expenditure. You can use the equals sign to add up your expenditures within a month. For example, if you spent $100, $40 and $35 on a particular category of expenditure in one month, you would type = 100 + 40 + 35 into the corresponding cell. This will make the entire sheet more compact.

Now I'll answer your actual question

Let's assume you have headers in row 1, expenditure categories from rows 2 to 20, and totals in row 21 (e.g. in B21 type =SUM(B2:B20) and copy rightwards). Then, it is easier to view all the important information at the front, so put the names of the expenses in column A, the budget for each expense in column B, the running totals for each expenditure category in column C (simply a SUM of columns E to AI), the running average in column D (use the formula =IF(C2>0,(C2/DAY(TODAY()) )*(365.25/12),"") in D2 and copy down), and the days in columns E onwards.

Let me explain that formula: If C2 is positive (which avoids an error when dividing by 0), then display the running total divided by the day of the month (DAY(TODAY())), which is the average per day, then multiply that by the number of days per month (365.25/12) to get the average per month. If C2 is nonpositive, display nothing (""), instead of an ugly error.

So, row 21 shows the overall total for each day. Then, you can easily create another row of the running total. For example, if E21 is the total for the first day and F21 is the second, then you could let E22 = E21 and F22 = E22 + F21, then copy F22 rightwards, and thus 22 is the row containing the running total.

In a similar fashion to column D, you can create a running average for row 23 (type =E22/E1*(365.25/12)) and copy rightwards, but I don't think this running average will be particularly helpful, for the following reason.

Expenditures are 'lumpy', which means that you won't have a constant outflow of money in each category. For example, you can spend a huge amount in one category, then not spend in that category again for the rest of the month. So, this will mess with your averages, especially towards the beginning of the month. Thus, I recommend rather graphing how the running total differs from your budget. I.e. if running total = T, the budget = B and the day of the month = d, then graph T - (B/d)*(365.25/12). This is easier to look at compared to the running average. You want the line to be less than zero, and it can thus be helpful to set an upper limit, and an aim, for your budget. You can then put the upper limit on your graph to help you not berate yourself for going over your aim, by creating ( (U-B) /d)*(365.25/12) in a row on your spreadsheet (where U is the upper budget limit). Remember that d = DAY(TODAY()). Here's a screenshot of how I do that when monitoring my data usage:

Running total minus aim

and you can see that's easier to look at compared to the running total:

Running total

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.