Excel budget question

[Mike Lowe]

I love this forum because I can ask questions like this and get an actual, working response!

So in my excel budget sheet, I have a column which calculates my anticipated bill totals for the month with each row being a different bill.

In the next column, I enter in the ACTUAL amount that was paid. I'd like a formula that would calculate the difference by the end of the month, either + or -.

It's easy enough to take the sum differences of each column, but when I do that, the output cell starts at like negative $2,000 or whatever. I'd like for THIS column, to start and stay at zero until I exceed the anticipated total in which case it would turn into a positive number.

On the contrary, I'd like it to also tell me at the end of the month, once all columns are filled, if I was "short" (which just means I'd have spent less than anticipated).

Does this even make sense? Is there a better way that I'm missing? I used to use Mint a lot, but it's just more hassle than it's worth. I don't really care how much I'm spending on napkins each year.

[tyketime]

For the first part, lets assume the following:

Column A = "estimate"
Column B = "actual"

I think this formula (placed in column C) will do what you want:

=IF(B2>A1,B2-A2,0)

This says if Actual exceeds Estimate, then display difference. Otherwise, display 0.

[bryce]

So if your actual is less than budget, you don't want to know the surplus until after all bills are paid, but if your actual exceeds your budget, you want to see by how much as soon as it happens, am I understanding that right? This formula assumes your budget is in A1:A9 and actual are in B1:B9, obviously adjust accordingly:

=IF(SUM(B1:B9)>SUM(A1:A9),SUM(B1:B9)-SUM(A1:A9),IF(COUNT(B1:B9)=9,SUM(B1:B9),""))

edit to add this assumes you are summing columns (all bills in total), not rows (each individual bill).

[tyketime]

Are you calculating the difference per row (for each bill), or the difference per column (overall amount per month), or both?

[SteveMax58]

Originally Posted by tyketime

For the first part, lets assume the following:

Column A = "estimate"
Column B = "actual"

I think this formula (placed in column C) will do what you want:

=IF(B2>A1,B2-A2,0)

This says if Actual exceeds Estimate, then display difference. Otherwise, display 0.

I would do this but change the Column C formula to the following:

=IF(B2>0,B2-A2,0)

Then use the cell properties to display negative values in the desired fashion (i.e. whether you show it as red or in parenthesis, both, etc.). You could also do conditional formatting if you want the format to be dramatic.

[tyketime]

Originally Posted by SteveMax58

I would do this but change the Column C formula to the following:

=IF(B2>0,B2-A2,0)

Then use the cell properties to display negative values in the desired fashion (i.e. whether you show it as red or in parenthesis, both, etc.). You could also do conditional formatting if you want the format to be dramatic.

Except he says this:

It's easy enough to take the sum differences of each column, but when I do that, the output cell starts at like negative $2,000 or whatever. I'd like for THIS column, to start and stay at zero until I exceed the anticipated total in which case it would turn into a positive number.

So he doesn't want to see negative value, right?

[Mike Lowe]

Originally Posted by bryce

So if your actual is less than budget, you don't want to know the surplus until after all bills are paid, but if your actual exceeds your budget, you want to see by how much as soon as it happens, am I understanding that right? This formula assumes your budget is in A1:A9 and actual are in B1:B9, obviously adjust accordingly:

=IF(SUM(B1:B9)>SUM(A1:A9),SUM(B1:B9)-SUM(A1:A9),IF(COUNT(B1:B9)=9,SUM(B1:B9),""))

edit to add this assumes you are summing columns (all bills in total), not rows (each individual bill).

This is good. I adjusted like this:
=IF(SUM(D227)>SUM(C2:C27),SUM(D227)-SUM(C2:C27),IF(COUNT(D227)=27,SUM(D227),""))

What is that last crazy part about? FWIW, nothing is showing up now in the cell I want this answer to reveal.

[Mike Lowe]

Yeah, I'd prefer not to see negative value until everything is entered by the end of the month. I use this particular cell to also calcuate what should be transferred into savings by the end of each month, so having a -2000 number obviously has it looking fubar for 29 of the 30 days.

[tyketime]

I'm not sure I understand the second IF(COUNT) statement either. Since he only wants to see a number if the actual exceeds the estimate, wouldn't the "false" part of the IF statement just be 0?

[SteveMax58]

Originally Posted by tyketime

Except he says this:

So he doesn't want to see negative value, right?

I read that to mean he didnt want it to be negative if he had not entered a value in B yet. So B would never be less than 0 (since it is the amount he paid), then column C would not sum until B is greater than 0 (i.e. when he has paid something).

[SteveMax58]

For the monthly total (in C)..I'd go this route if using the above formula for the individual bills.

Monthly total:
=IF(COUNT(C2:C4)-COUNTA(C2:C4)=0,SUM(C2:C4),"")

Row totals:
=IF(B2>0,B2-A2,"")


EDIT: Added both formulas. I go "" instead of 0 personally because I prefer to see nothing if there is nothing entered yet.

[bryce]

Originally Posted by Mike Lowe

This is good. I adjusted like this:
=IF(SUM(D227)>SUM(C2:C27),SUM(D227)-SUM(C2:C27),IF(COUNT(D227)=27,SUM(D227),""))

What is that last crazy part about? FWIW, nothing is showing up now in the cell I want this answer to reveal.

Yea, that is by design. The last part is counting how many bills have been paid (assuming you have 9 total - change this if it's something else), and when all 9 have been paid, it will do the summations. If not all bills have been paid, it returns a blank, which is the "". If you want 0 instead, change the "" to 0.

[Mike Lowe]

When I use this code, nothing happens. No error, and no entry:
"=IF(SUM(D227)>SUM(C2:C27),SUM(D227)-SUM(C2:C27),IF(COUNT(D227)=14,SUM(D227),""))"
Ignoring the quotes obviously at beg and end.

Thanks everyone for the help!

[bryce]

Originally Posted by Mike Lowe

When I use this code, nothing happens. No error, and no entry:
"=IF(SUM(D227)>SUM(C2:C27),SUM(D227)-SUM(C2:C27),IF(COUNT(D227)=14,SUM(D227),""))"
Ignoring the quotes obviously at beg and end.

Thanks everyone for the help!

It might be working and just returning a blank cell. Change the "" at the end to 0. If you get 0, then it's working. If you are getting the blank and/or 0, then that means you have entered fewer than 14 actual payments in column D (thus, not all bills paid yet).

[Mike Lowe]

Ok yeah, it was working then. Thanks again everyone!

[Mike Lowe]

Ok, looking to advance this a bit more. How do I make this:
=IF(SUM(D227)>SUM(C2:C27),SUM(D227)-SUM(C2:C27),IF(COUNT(D227)=1,SUM(D227),"0"))

Do the following:

So right now, I have just two bills entered for April. Both of these bills were a bit higher than planned. Although the other bills are not entered yet, how do I tell Excel to "until an actual amount is entered, assume the expected amount" so that I can see, even with just two entries in my April budget, that I'm over by about $20 or whatever.

Thanks to you geniuses!

[bryce]

This should do the trick:

IF(SUMIF(D227,">0",D227)-SUMIF(D227,">0",C2:C27)>0,SUMIF(D227,">0",D227)-SUMIF(D227,">0",C2:C27),IF(COUNT(D227)=COUNT(C2:C27),SUM(D227)-SUM(C2:C27),"0"))

[Mike Lowe]

Hmmm, that doesn't seem to work as it just enters the text and not an actual function/equation.

And yes, I replaced the smileys with a ":" so it would read 2:27.

[Suburban Rhythm]

need an = sign in front of the first IF

Additionally, not sure you need " " around the >0 criteria

[Passacaglia]

Also, it should be D2 to D27, not D2 to 27, when you replace the smilies.

[britrock88]

I have a different Excel question that could open up a world of possible answers. I'd like to create a chart that indicates the status of colleges as members or non-members of NCAA Division I in much the same way that the below chart tracks the membership of the Southern Conference. It'd also be awesome if the bars could be color-coded for specific conference membership.

I have all the data necessary (thanks, Sports Reference); I'd just like a clear way of seeing when schools jump in or drop out.

[Mike Lowe]

Works, awesome thanks guys!

[Mike Lowe]

Ok, things are working great. Now what I'm noticing...when I'm updating a budget that is added on each week, like groceries or fuel, when the sum of that category doesn't add up to the budgeted amount yet, the number in my "over/under" cell goes to zero (probably because there is enough to compensate).

How should I fix this in your guys' opinion? What I mean is, should I create a switch or something to trigger these few bills that aren't "one and done" sort of things? I'd like Excel to just assume I'll max the category I'm adding to eventually meet the expected total....but even then, I'll need a trigger of sorts to let it know when the month is up or something?

Does that make sense what I'm trying to ask/do?

[Mike Lowe]

I have another Excel question in relation to part of a roster & seating chart I am making for school.

I list the student's names and then I always end up having to copy and paste their names down into the seating chart. It doesn't sound like much, but it's tedious believe me. I alterate seating boy/girl and what I'd like to do is this:

Assign each student a number or letter. Perhaps #'s for boys, letters for girls or whatever. Anyway, based on the number/letter they are assigned, their name will then automatically appear in the area where I have the seating chart.

I don't know the code very well for Excel but it'd be something like this (in idiot terms)

For the actual cells where I have the desk listed:
If # from C3 is 1, enter cell A3 (student's roster name).

This would have me enter in a number NEXT to the student's roster name, to which by placing that particular number/letter, a copying of the roster name down to the desk cells would take place.

Does that even make sense?!?

[Suburban Rhythm]

You could do IF statements, but you'd end up nesting multiple statements within each other to fill out an entire class of say 30 students.

You'd have to say IF C3=1, then A3, (this next piece of the IF is the false statement). Within that false statemnt, you'd have another IF saying IF C3=A, then A4 (next false statement here, which will be another nested item).

Better option would be create a list of male and female names. Each is numbered M1, M2, etc and F1, F2, etc.

Then do Lookups to that list, and input M1, F1, M2, F2, etc in the desk assignments