Need some assistance. I am trying to build a spreadsheet with a running balance. I have a field with the initial $ value (C1), I would like the spreadsheet to check if the column with the $'s being taken away (column B)is < (less) then available running balance(Column D), if value is less then deduct if not then move on to the next item until it identifies a value less then the running available balance.
I am not that great with formulas, My starting formula is =IF(B2<C1,(C1-B2),"0"). I can make the spreadsheet do the first part of my requirement to subtract the balance if it is less then available balance, but I can't figure out for the life of me how to make it move to next value if previous is greater.

Please clarify: if the entry in B2 is greater than the amount in C1, do you want cell C2 to read as 0, or do you want it repeat cell C1? If the latter, then change the formula in C2 to:

=IF(B2<C1,(C1-B2),C1)

Also - you put quotes around the "0", which makes that entry text rarther than a number. Why did you do that?

We'd like to understand what you find wrong with ebaines's answer:

What's inaccurate about this answer? Say it in 25 words or less here and/or reply in the thread with more detail.
Please focus on the content not the person!

Link to a credible and well-known source. You can provide a URL or simply describe the source.

We'd like to understand what you find wrong with babraham's answer:

What's inaccurate about this answer? Say it in 25 words or less here and/or reply in the thread with more detail.
Please focus on the content not the person!

Link to a credible and well-known source. You can provide a URL or simply describe the source.

We'd like to understand what you find wrong with babraham's answer:

What's inaccurate about this answer? Say it in 25 words or less here and/or reply in the thread with more detail.
Please focus on the content not the person!

Link to a credible and well-known source. You can provide a URL or simply describe the source.

I would like C2 to remain $0 as no $'s are being applied to that project since it exceeded running available balance.

And so if the entry in C is $0, what would you expect to happen on the next line? Example, given the initial balance in C1 of A$1000 and items entered in column B as follows, are the results in C2, C3, ad C4 as you expect, and what do you want to have happen in C5?

start with C1 = 1000
B2 = 100, so C2 = 900
B3 = 100, so C3 = 800
B4= 2000, so C4 = 0
B5 = 100, C5 =??

We'd like to understand what you find wrong with ebaines's answer:

What's inaccurate about this answer? Say it in 25 words or less here and/or reply in the thread with more detail.
Please focus on the content not the person!

Link to a credible and well-known source. You can provide a URL or simply describe the source.

start with C1 = 1000
B2 = 100, so C2 = 900
B3 = 100, so C3 = 800
B4= 2000, so C4 = 0 (skip since it exceeded value available $'s (800)
B5 = 100, C5 = 700 (look at previous available balance and deducts since amount to expend is < then available funds which can be applied)

We'd like to understand what you find wrong with babraham's answer:

What's inaccurate about this answer? Say it in 25 words or less here and/or reply in the thread with more detail.
Please focus on the content not the person!

Link to a credible and well-known source. You can provide a URL or simply describe the source.

OK - I would have thought that you would want $800 in cell C4 since that's the remaining balance (having not spent anything against that $2000 bill).

Two suggestions:

1. I would really recommend that you create a three column sheet:

Column B = bill amount
Column C = amount spent against the bill
Column D - remaning balance

With this approach the entry in C2 is =IF(B2<D1,B2,0), and the entry in D is is =D1-C2. Simple and straight forward.

2. But if you really want to maintain just two columns this would work in C2:

=IF(B2<MIN(C$1:C1),MIN(C$1:C1)-B2,"0")

You can copy and paste this into cells C2 and below. This will cause the spreadsheet to place a "0" character as text if there isn't sufficient funds, otherwise it will subtract B2 from the smallest number in column C above it (not counting the "0" characters.) This works even if you have several entries in sequence in column B that are all too big. Note that this assumes that the amounts being subtracted in column B are all positive - if you have any negative entries in B it falls apart. Hope this helps!

We'd like to understand what you find wrong with ebaines's answer:

What's inaccurate about this answer? Say it in 25 words or less here and/or reply in the thread with more detail.
Please focus on the content not the person!

Link to a credible and well-known source. You can provide a URL or simply describe the source.

Thank you very much for your assistance. I would like to create additional columns but I am trying to create the worksheet for another co-worker who wants to leave it as 2 columns.

We'd like to understand what you find wrong with babraham's answer:

What's inaccurate about this answer? Say it in 25 words or less here and/or reply in the thread with more detail.
Please focus on the content not the person!

Link to a credible and well-known source. You can provide a URL or simply describe the source.

This is how I would do it... first by converting your 0 back into text strings like you had originally so excel no longer views them as numbers. Then your formula can use a "find the last valid number above" approach to test for if the number can be subtracted or not.

We'd like to understand what you find wrong with JBeaucaire's answer:

What's inaccurate about this answer? Say it in 25 words or less here and/or reply in the thread with more detail.
Please focus on the content not the person!

Link to a credible and well-known source. You can provide a URL or simply describe the source.

JBeaucaire, So my assumption is if my initial input changes from 99, then my lookup value changes to that correct? Forgive me for my ignorance, Can you tell me what 99^99 does in the formula, I understood the methodology for the first formula provided but was unclear on that part of yours.

We'd like to understand what you find wrong with babraham's answer:

What's inaccurate about this answer? Say it in 25 words or less here and/or reply in the thread with more detail.
Please focus on the content not the person!

Link to a credible and well-known source. You can provide a URL or simply describe the source.

The Lookup function is by design a "fuzzy match" function. If a searched value cannot be found, the next value below is returned. That is why typically the LOOKUP function is only used on an ordered lookup table. But we can still use it in this case.

99^99 is one way to say "search for a massively large number"... and since this will never be found, LOOKUP will keep searching through the list until all values are searched, then by default it will simply return the last value in the list.

So, the LOOKUP() construct I showed is effectively... "find the last "number" in the list above".

We'd like to understand what you find wrong with JBeaucaire's answer:

What's inaccurate about this answer? Say it in 25 words or less here and/or reply in the thread with more detail.
Please focus on the content not the person!

Link to a credible and well-known source. You can provide a URL or simply describe the source.

Hi, I know this will probably be easy to most of you? But I need to have a formula so that I can show amount of hours allocated to a task in collumn A amount used in Collumn B and the totals in Collumn C However I want it to work whether the Collumn A is greater or lower than collumn B and show...

Download Attachment, first.
I need:
-If B14=empty, then A and D colones both empty
-If only B14=selected, then A14=TREZOR, D14=1, D15=1, bold underline between C14-L14 and C15-L15, others A and D empty
-If B14=selected and B15=selected, then A14=TREZOR, A15=-II-, D14=1, D15=1, D16=2,...

I am using Excel 2007.
I have 2 Sheets.
In first sheet there are 3 coloumns, first one contains the employes number, second coloumn contains the name of employes and third one contains the department he works in.
In the second sheet I have drop down list of the names of employes and the...

In Excel's help they have a running balance "how to" (below) but it requires the entry of a zero for fields not used.
I'd like to do it like a checkbook register. How can I do it so that whether I enter a deposit or a withdrawal, the balance column will be updated?
Thanks!