Link to home
Start Free TrialLog in
Avatar of angelfromabove
angelfromaboveFlag for United States of America

asked on

How do I calculate a conditional running balance

Hello --Note: Please read carefully before responding, as it gets a little tricky:

I need to calculate a running balance of items charged to our expense account from a base budget amount for each category. For example, if I spend $500 on groceries once a week and my budget is $5,000, I'd like to subtract the $500 or whatever I spend from the running total balance of groceries, which started at $5,000.  I'd also like to do the same for other categories of expenses also on the same spreadsheet: so I might have a budget of $1,000 for medical expenses, and a budget of $400 for clothing, etc.

It sounds simple, but the issue I'm having on my worksheet is that my categories are not always in consecutive rows; for instance, I may have some grocery expenses in consecutive rows, but then I may have medical expenses and then back to grocery expenses, you get the point, yes?  So I would need some sort of conditional sumif formula, (I think). To further complicate matters however, groceries is not my only criteria, I have a different base budget amount for about 600 different categories, which I have in a drop-down selection list in a column.  With all of these different categories and base budget amounts, would I have to create a table of the starting budgets matched with the 600 different categories and then do a Vlookup to my main sheet? That's fine, but how would I then combine that with a conditional sum formula to keep a running balance for these categories on the same sheet? Lastly, I'd like to be able to pull all of this into a pivot table which would sum the individual amounts spent for each category, subtracted from the running balances by category, to give me my remaining spend/overage. I've got everything figured out except how to keep the running balance piece for each category and each indidvidual amount spent within that category without it double counting the starting balance in the pivot table.

HELP!!!
Cheers!
Avatar of arnold
arnold
Flag of United States of America image

your design is the means to achieve what you want, you have a Ashely that includes your transactions.

You might benefit by using an sql server express to store the data in tables with excel functioning as the front end data entry as well as the means by which data is extracted.


Back to yours.
Using  ms query, you will go through the sheet where the purchases are recorded
And sum if the category is groceries as groceries. And repeat for e Rey other category.

This will only say how much you spent, but will not show how much is left in the budget.

Reverting back to using the DB, in that you would create a view which will combine the annual budget for every category and an ongoing adjustment based on purchases

You have a budget table and how much is allocated to each category

You would join tables/sheets based a common column, categories in your case.
Avatar of angelfromabove

ASKER

Arnold -

Thank you very much, but this will not be my solution.  I need to do this in an Excel spreadsheet and ultimately pivot tables and I need detailed instructions based on my question to accomplish this.

Does anyone else have a possible solution? Time is also of the essence here.
You can still use excel with your tables/worksheet in one file while the other uses the external db query using the excel spreadsheet as the source of data.

You would then use ms query to build the query and the rules to give you the display you want.

When using excel, make sure to include system tables so that you will get the tables available in each worksheet.  

You can the combine the tables on a common column parameter (join)

The result will be the table with data.  You can the run the pivot table on this data set.
I don't know how to do what you are describing, I only know Excel. I will need a different solution.
Ok, in excel, you have a workbook with tables spanning multiple worksheets?
Close this file. Open new emply workbook.
Select column/row A1
Go to the data menu select import external data from db
On that selection use excel files as the source for tables.
The next stage should list the tables it found among the workbook's worksheets. If not look at options to make sure system tables are also selected. Each table will include references to the column heathers.



If you want, can you upload the workbook with just the column heathers as well as how each worksheet/table relates to the other.
I.e. Worksheet1 columnname is the Id for the table on worksheet 3.

I'll combine them
You can then see if that is what you are looking for. I.e. Update the workbook and then see the results on the "report workbook"
No, I just have one worksheet with multiple columns: locations, dates, expense amounts, expense types and account numbers.

I don't want to over complicate this, I just want to be able to keep a running balance, subtracted from various budget amounts, and the budgets will be different depending on the location and/or expense type.

The only thing I'm having trouble figuring out as I state in my original question is how to automate the subtraction of the expenses from the various budget amounts based on the location/expense type.  The formula would need to be able to detect that ABC Expense, at XYZ Location has a budget of $5,000, but the individual expenses could be on any row in the spreadsheet, , not necessarily consecutive rows.  I thought that a combination of Sumif, Match, Index or vlookups might work somehow, but the challenge is that I can't copy the budget amounts to every row, because they will be summed multiple times in the pivot table.  The budgeted amounts have to be in one spot, the automation would have to know which budget goes with which category, and subsequently detect and subtract the expenses on each row associated with each budget amount.

Ideally, on my pivot table I would have the Expense Type, Location, Date, Expense Amount, Spent to Date, and Budget.  Perhaps, a formula can be written to detect when the budget amount has already been populated once on the worksheet, and for every subsequent expense associated with that Expense Type/Location, it knows to go to the same row for the budget. I don't know, but either I need someone to build something for me, perhaps using the method you described, or give me some formulas to work through Excel, the way I feel comfortable doing it.  I know this can be accomplished through Formulas, I just don't know the right combination, but an Excel Expert should.

Thanks for your help, but I would prefer someone else took a stab because I'm not sure I can achieve what I want by your method because I still don't understand how to accomplish it.
I will also consider Code, if someone can write something for me.
You would either have to use VBA macro that will go through the cells and extract the data or you have to use the external query mode.
Without seeing how your data is organized,
You basically want the following
column1,column2,category,column4

you want a sum that looks at the column category and sums their values and this value is used to reduce the badgetery balance which is value -the cell where the respective sum is.
I am not sure you can do a sum (if category='food';expense;0) which will be the Food_expense.
In order to write anything one has to know the basis on which you want these determinations made and how your data is arranged.

Can you provide a sample/empty excel with the setup you have
i.e.
columns A1-h1 40 rows are your heading that layout your yearly budget on the budget worksheet.
Your second worksheet is your expense/data enrty
Columns A1-g1 describe the
date,category,store, method of payment,amount,etc.

This is the basis on which one can write code, test functionality etc. .
Hi A,

This can probably be achieved without any VBA; you just need to configure a proper running SUMIF function to return YTD expenses for each category as it is entered and then subtract that amount from the budget to get the balance.

See the attached example workbook and let me know if that has the general behavior you're looking for.  (Note that the Expense Category is a drop list-data validation to the Budget table like you described and the Date column is limited to 2015 dates).

Regards,
-Glenn
EE-RunningBalances.xlsx
I think I know what you want but it will make things a lot easier if you could post a sample worksheet of what you've already done also with some sample data.
SUMIF certainly sounds right but it will depend on the layout if the data and integration with the categories. By having a sample sheet I'm not going to go down the wrong track only to turn around and start again
Glenn!!!!!!!

Merry Christmas! Perfect! Exactly what I wanted, I just couldn't organize my Excel knowledge to get there, because I had the "SUMIf" part right, just couldn't figure out how to subtract it from the budget using a Vlookup, but you did it in 1 columns.  Good thinking!  The only thing left is  I Just need to be able to pull the budget for each category into a pivot table though, so we can see everything side by side. That's the toughest part for me.
In the budget include a column reflecting the expenses from the category, then have a total column that reflects the available remaining total.

You can then gave the allocated, the spent and the remaining columns to graph as you see fit. I.e allocated is blue, spent is ref, green, is what is still left.
Thank you Arnold, but I will also await Glenn's response.
I don't believe you'll need a PivotTable; the data shown on my example Expenses sheet/table could be expanded to accommodate locations and original budget amount, if desired.  See the modified workbook attached.

Since both the Expenses and Budget sheets are using Excel Tables, you'll see structured referencing in the formulas that pull the data.  Don't let it scare you off; it's just another way of referring to the fields in each table.

-Glenn
EE-RunningBalances.xlsx
Glenn, you are certainly right, I don't need a pivot table to accomplish the tracking, but for reporting purposes, I just want the functionality of it, i.e. I can slice and dice the data by month, expense type, location, budget and whatever else I have on my original spreadsheet. It's a neat way of summing and filtering at the same time. I'm the queen of pivot tables!

So, again your new table makes perfect sense, and we're almost there except it looks like the total Budget for Supplies in Location 100ABC is 10,000 if we add $5,000 + $5,000. In actuality, the budget is only $5,000 for Supplies in that location.

The challenge is , how can we show the correct total budget without duplication, when an expense is posted to the same location and expense type?
Sorry, I should have clarified what each column is showing:
Spent to Date - Year to date sum of amounts for that Location and Expense Type
Balance - Budget minus Spent to Date
Budget - Original Budget for Expense Type for that location (this value is strictly informational; will not change)

-Glenn
Exactly, no I get it Glenn, it was very clear what each of your columns was doing and that's what I need but do you get how I want to show the budget column? Read my earlier posts, can this be accomplished?
Just to chime in here... the budget column shouldn't be aggregated in your pivot table.  It is essentially a label it should appear either in the column or row but not in the "value" section.  Same goes for the Balance.  You've already essentially done the aggregation on those values in the data table
You won't be able to insert the annual budget into a PivotTable.  You'd have to set up some separate lookup to retrieve it.  By extension, the "Spent to Date" and "Balance" data wouldn't be valid in a PivotTable either.

In that instance, you might want to consider using something like GETPIVOTDATA to pull summarized data and display it side-by-side with the annual budget data.

-Glenn
That's a good point.  You can show the individual budgets but not the overall budget total easily in the pivot.

This is where you may want to look at a BI tool that can do much more than Excel was designed for.  I have extensively used Qlikview due to its ease of use, though it may be a bit pricey.   Have a look if you're looking to do this kind of reporting more often and need a lot of flexibility.
ASKER CERTIFIED SOLUTION
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glenn! Thank you.  Glenn, your pivot table looks identical to the pivot table I set up except you were clever enough to try the budget as a row label!  Exactly what I had in mind! I did the same with the Balances.  This is as good as it's going to get and it will work for me.

Rob - Thank you, and no disrespect, butI get that Spent to Date and Balance have already been aggregated. I am very good with pivot tables and we can also accomplish the aggregation within the pivot table by summing the individual amounts spent. I wouldn't necessarily need to use the aggregated column.  We really need to think about what I need to do here though. As I mentioned in earlier threads, I need to report exactly what Glenn has set up, but against a constant budget for each category. Glenn  has essentially done what I need, and if I need to also report on the total budget for that category, I need to be able to quickly filter it and  say, *** was $5,000 and here's what we have spent thus far----- If we repeat it in the table it will be hard for someone to quickly determine what the budget was. I need to be able to group by expense type, date, etc. as I mentioned. So while it may seem obvious to you, I know what I need to do. I already have a pivot table set up and it's working exactly like I need it, Just needed to figure out how to report the budget where it makes sense.... This will be for many business expense accounts, groceries and gas are just generic examples of what I need. I need to make it foolproof for my whole department to use.

Thanks again for all of your suggestions!
I thinks there's a glitch somewhere, I tried to accept Glenn's solution and assign a grade twice, and it doesn't look like it took. Is there a delay in closing out the question?
It should be as simple as clicking Accept as Solution on the post you want, or use the accept multiple and select all the posts that helped with the point value.

If you think in the future you would want a comparative historical report about the various data points, consider looking at using an mssql server express as the data store.
Arnold, Thank you. It should be that simple, but obviously trying to click Accept as Solution didn't work for some reason.  I will call Customer Service on Monday.  As I mentioned previously, I don't know how to use an mssql server, but I think I will learn going forward.
MS is already offering SQL server 201.
you can download it from http://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/sql-server-express.aspx
make sure to also get/install the SQL server management studio express which is the graphical tool through which you can manage the data within.  You can then use excel using the data external query connect to the ms sql server to generate the queries i.e. select the database/tables and then come through the options you want etc. as well as excel being the data entry into the database.

It also includes reporting if needed, that can also be configured to generate the reports you want emailed to you ........
Thank you. Power Pivot which is an add-in works similarly in Excel and can also connect to External servers like SQL. I am now trying to close this question out with Glenn's last response as soon as I can figure out what is preventing me from doing so.
When hitting Accept answers as solution, assigning a grade, do you hit the accept immediately option or the other option to the right?
Perfect
Worked on my phone not my laptop for some reason. Thanks!
angelfromabove,

Glad you've got your solution and Glenn has done some great work for you.  My suggestion regarding the BI tool comes from doing extensive reporting and pivot tables in Excel and getting frustrated with the amount of time needed to create a report or PT that did everything I needed.  Especially around the aggregated values like budget not getting aggregated again but being able to appear within the aggregated area of a PT (and having complex formulas to determine exact budget amounts per reporting period), exactly as you have indicated.  It also allows for incredibly easy filtering of data by just clicking the options in any number of filters.

On top of that, the formatting was always a pain.  These tools are designed to do what you want.

I'll stop banging on about it but I'm more or less saying to not dismiss the idea of a BI tool (for me it was Qlikview) that essentially makes setting up a report like this, take only a matter of minutes, rather than hours or days.  

After all this, if you can do what you want in Excel then that's great too.  I'm just letting you know what else is out there and how much it has benefited me in similar situations.
Arnold -

Sorry, I am not against your BI solution, I believe I already said I would look into it. I just needed something right now for this purpose that I could easily implement. Where it was frustrating or time consuming for you to use pivot tables, it's easy for me because I am pretty advanced with using them, it's my expertise. I was just too busy to think about it and I chose to utilize my option to enlist the Experts this time. I also mentioned Power Pivot in my last post, not Pivot Tables as we were discussing earlier. Power Pivot is quite different and functions like an SQL database.

I really hope you have a great evening and upcoming New Year, but I am going to sign off from this discussion now.

Thank you.
Sorry, Rob, not Arnold.
Sorry, didn't want to give you the wrong impression here, was just trying to say how much I've gotten out of another tool as I'm also very proficient with excel and pivot tables.  I was also aware you were referring to Power Pivot, which is also a good tool to use.

I'll also sign off but good to hear that you'll look into it.

Rob