Show correct balance - Excel 2

[thefourthwall]

Hello,

Having searched through the forum already, I have seen what look nearly like a solution that would work, but likely am messing up the implementation of it.

I am trying to create a budget that looks like:

Col A[tab][tab]Col B[tab][tab]Col C[tab]Col D
"Spending"[tab][tab]"Income"
1[tab][tab][tab][tab]06/15/2012
2[tab][tab][tab][tab][tab][tab][tab]$1,000
3 Car[tab][tab]$200[tab][tab]$800
4 Home[tab]$400[tab][tab]$400
5 Dining[tab]$100[tab][tab]$300

So that, like a checkbook register, an amount is entered under the "Income" heading, and as I enter an amount for a category for that week in col B, the resulting balance displays in col C.

Easy so far. But if I have no expense in row 4 that week, and still have an expense in row 5, I would like the balance in row 5 look up col C to get the first available balance, and update it accurately - so if I have $1,000 in income for 15 June, spend $200 on the car, leaving $800, spend nothing on the home, and spend $100 dining out, I'll have $700. How can I have an expression in C5 display the balance accurately if some spending rows are skipped for any given week?

Does this make sense?

 

[SkipVought]

hi,

You DO have an expense value on row 4, if you nave no expences and that value is ZERO (0).

I do not understand your issue.

What is your FORMULA in column C?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[thefourthwall]

hi,

Ah, I see your point re: an expense even if the value is Zero (0).

My issue - oh boy, there's LOTS of those! But for this, if I have a zero expense for row 4, I want the value in the next row where there is a non-zero expense to reflect the balance from row 3. Ummm ... yeah .. let's try this - if I "skip" a row of expenses in a category, I want the formula in Col C to "look past" the zero expense up the column to the next non-zero expense.

The formula I have had so far is:

=IF(B3=0," ",($C$2-B3))

 

[SkipVought]

I've done it like this at times...
[tt]
PostingDate Category Amount Balance
6/15/2012 Balance $1,000.00 $1,000.00
6/15/2012 Car -$200.00 $800.00
6/15/2012 Home -$400.00 $400.00
6/15/2012 Dining -$100.00 $300.00
6/17/2012 Income $2,000.00 $2,300.00
[/tt]
and the formula in column D
[tt]
D2: =IF(ISNUMBER(D1),D1,0)+C2
[/tt]
and if your are using Excel 2007+, if you make the table a Structured Table via Insert > Tables > Table, then the formula in column D will ALWAYS be up to date.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Your BALANCE should never be " "!!! A SPACE does not belong in a NUMERIC column!!!

If there is a ZERO expense, then THAT Balance should be the SAME as the previous Balance.

Also, how do you handle adding in some sort of Income? I like showing Expenses as negative and Income as positive.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[thefourthwall]

hi Skip,

Work and then taking care of issues with my son have kept me from using your advice - until later this morning, at least. It seems like that would make sense, and I'll let you know if its working (assuming I adjust the formula correctly).

-thefourthwall

 

[thefourthwall]

Ah, I concede your point: a balance should never be " ", agreed. That was sloppiness on my part. Thank you for catching it!

 

[SkipVought]

Something that users do at times, is to pre-fill future rows with a formula, in anticipation of future data. This is not a particularly sound practice.

If you have Excel 2007+, you can make your table a Structured Table via Insert > Tables > Table. Structured Tables have many new features that are really neat, including the propagarion of formulas to new rows and Structured Table References. I would urge you to explore this feature.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[thefourthwall]

I am using 2007, and the Structured Table to which you refer is new to me. Will look at that this afternoon.

Your expression using isnumber is exactly what was needed - thank you very much!

I was thinking of "pre-filling" in the rows in anticipation of data, actually because some pay periods may not have any spending for Car, but would for Dining. Or, am I misinterpreting what you said?

 

[SkipVought]

Pre-filling cells refers ONLY to formulas, not other literal values in a row.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[thefourthwall]

Hey this looks like a powerful tool, the structured tables tools. Thanks for that!

I so hope this next query doesn't "do me in" here, but ... because my wife might be using this, I was thinking that a cleaner look might help; to that end, was wondering if, when a value for an expense, say, dining, is blank for a pay period, can the balance on the same row appear blank also ... have played with conditional formatting but that got me nowhere, am I wasting energy here?

 

[SkipVought]

You can use CF by doing this...
[tt]
1) Select the range you want Conditional Formatted (NEVER select row 1)
2) Open the CF Wizard
3) Use FORMULAS
4) Make the formula reference(s) relative to the UPPER-LH corner of the selected range. In your case, assuming that the format range is D29 the forumla might be

=isblank(C2)

format as WHITE FONT
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[thefourthwall]

Ever had someone show you how to do something (aka my last request re: conditional formatting) and then have a "i KNEW that!" moment?

Well, thank you Skip. Your help has been most appreciated, I am grateful for it.

 

[SkipVought]

Glad to be of assistance.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]