IF formula for budget purposes

Hello all,

I am setting up a personal budget spreadsheet to track my monthly income and expenses and there are a couple of dynamic features I would like to add, if possible.

First, under each category (Housing, Transportation, etc), I have 2 columns; 1 for actual expenditures, 1 for my budget limit for each item for that month. I would like to set up a formula that affects the actual expenditure column in relation to the budget limit column. For instance, under Transportation, I am budgeting $50 per month for taxis. When the actual expenditure for taxis is between 0-90% of that $50 budget (so, $0-45), I would like the cell to be green. When the actual expenditure is between 91-100%, I would like the cell to turn yellow. And, when the actual expenditure goes over my monthly budget, I would like the cell to turn red.

Second, I would like the monthly spreadsheets to work with one other. I would like the difference between the actual expenditure for an item and its budget to roll over to the next month's budget for the same item. So, say I budget $20 every month for gifts. If I spend $10 on gifts in June, I'd like the budget for gifts in July to bump up to $30, to include the $10 that wasn't spent in June. Conversely, if I overspend on gifts, say $30 in June, I'd like that to affect the following month's budget, so that July would be $10.

If anybody can help with either or both, I'd greatly appreciate it. Also, if anyone knows of another thread or two that offer really useful tips for building out a dynamic budget with great features, I'd love to be put onto that as well.

Thanks in advance for your help,
Brett

try to see if any of the templates available through MS Office Online. i have see a few that cater to Home Budgets; you may be able to reuse and expand on some of those.

with your Excel 2007 open:

ALT + F + N > Microsoft Office Online > Budgets > Home Budgets

sometimes it is best not to reinvent the wheel :-).

for the 1st part, you can use conditional formatting to getthe cells to change color based on the value of another cell.

for the 2nd part, do you have anything set up yet for your workbook? what you want sounds simple enough, but it will help greatly if you have a sample workbook to upload for us to work with.

in the mean time, take a look at the attached. its not a budget sheet, its a vehicle mileage log, but would work on the same principles, and might get you started in the right direction

probably better to take this a step at a time, so here if the CF that you want. you were almostthere with what you had, you just needed to use "use formula" for the CF

btw, are you from SA and did you play cricket?

the formulas that i used were standard excel formulas. if you type them into a worksheet cell, just the way i did in the CF, they will return a TRUE or FALSE anwer, and thats how CF works. it just checks for a TRUE or FALSE to do its thing. if i am working on a complex CF formula, i often do it IN the worksheet, since its easier to edit and play with rgere, and then copy it to the CF when it works.

did the vehicle log file i sent you offer any insights on how to set up the rest of your file?

i grew up in SA (PE and klerksdorp), didnt play cricket much, preferred rugby. always good to meet another ex-pat

shout if you need any other help/advice

sometimes all the courses in the world cant replace hands on practice, so if you have any questions, just shout

the only reason i sent you the log was to give you an idea of how a monthly "system" could work for you.

and i got turned off the blou bulle from when nasty naas played for them (bet that takes you back some huh?