Need help with expense/savings program formula

Hi all,

I'm new to this forum so please forgive me if I posted this in the wrong section. I am trying to create a program that will organize the payoff of expenses. It’s pretty basic and I have it setup on two separate worksheets. There is one specific column of cells (Column D in ,y example) with which I need help. I do not know if what I want to do can even be done or not, so bear with me!

There are four columns in total. One column (D) references the other three columns (A, B & C):

Column A (Worksheet 1) has 15 rows, Column B (Worksheet 1) has 15 rows, and both Column C (Worksheet 2) & Column D (Worksheet 2) have 48 rows.

Column A is the name of the expense, Column B is the dollar amount of the expense, Column C is accumulated savings and Column D is where the formula is located in each cell and where the results are displayed.

What I need it to do is have the first cell in Column D read each of the 15 cells in Column B and compare them to each cell in Column C. Once the cell in Column D finds the cell in Column C that has enough to pay Column B it displays that expense. Once that expense is paid, the remaining cells in Column D should not include that particular cell in its search since it’s already paid and should no longer be a part of the equation.


Here is a small sampling (I tried formatting this chart but having problems in this forum. I attached a JPEG if anyone wants to download it):

Worksheet 1 Worksheet 2

Column A Column B Column C Column D
Expense Name Amount Savings Exp Paid

Exp1 $500 $250
Exp 2 $1000 $500 Exp 1
Exp 3 $1200 $250
Exp 4 $1800 $500
Exp 5 $2500 $750
Exp 6 $3500 $1000 Exp 2


I hope this is clear, if not please let me know. As I stated earlier, I don’t know if this can even be accomplished. If it can, I would really appreciate any input on the formula!

Thanks
Steve

Hi Steve and welcome to the forum,

You might change the title of your thread to less generic problem. Something like "Reconsile Expenses and Savings Problem" as per the Forum Rules.

See the attached where I've done it a little different way. I've made expenses negative and savings positive and made a running total column. Is this good enough for your needs?

Hi Marvin,

Thanks for the input, but what you provided is not what I'm looking for!

Thanks
Steve

Originally Posted by MarvinP

Hi Steve and welcome to the forum,

You might change the title of your thread to less generic problem. Something like "Reconsile Expenses and Savings Problem" as per the Forum Rules.

See the attached where I've done it a little different way. I've made expenses negative and savings positive and made a running total column. Is this good enough for your needs?

Hi Steve,

If you really want help with this you need to make a sample file and attach it to a post. You can do this by clicking on the "Go Advanced" below the message area and then on the Paper Clip Icon above the advanced message area.

Be very specific on what your expected results will be.

This is your best bet for someone understanding the question and getting you a great answer.

My explanation in the opening post was pretty detailed, I've attached a sample of what i'm looking to do.

Hi Steven,

I must have my dumb hat on today. I don't see how your first post relates to the sample you posted. Then I'm not sure what you want as your desired result. I just can't figure out what you are looking to do.

Maybe one of the smart gurus can figure it out.

Originally Posted by Steven P

Column A is the name of the expense, Column B is the dollar amount of the expense, Column C is accumulated savings and Column D is where the formula is located in each cell and where the results are displayed.

I don't see that anywhere in your workbook example. A is Month, B is account to pay, C is Balance in Account and D is empty.

Hey Steven,

The new sample.xls still doesn't have anything in Column D.

See if watersev has figured out what you need. He is one of the Smart Gurus. He also speaks multiple languages. Maybe that's why he understands this problem.

To further that MarvinP, Column C is completely empty. I am still confused here.

Look at his most recent sample.xls (Post #11) where he inserted two columns on Sheet 2 so there is now data in Column D.

What I want to know is what is Watersev smoking that he was able to understand the problem well enough to come up with some answer.

Then I want to know from Steven if his answer is right!!

Do we have a Troll here?

Another way, perhaps.

A fomula for col N is simple enugh; add the amounts in cols D:I.

A formula for col L would be more complicated, because it could be a catenation of several values if several debts were paid the same month.

In L4 and down,

=IF(COUNT(D4:I4), LOOKUP(9E+307, D4:I4, $D$2:$I$2), "")

You're welcome, good luck.

Insert columns to the left of col J, copy col I to the right, then copy all formulas down.