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.

Any help would be greatly appreciated.

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?

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

Sorry missed your latter part of the question, I put quotes around the zero because of my unfamiliarity what I'm trying to accomplish.

Quote:

---

Originally Posted by babraham

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 =??

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)

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!

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.

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.

So the formula in C2, then copied down is:

=IF(LOOKUP(99^99, $C$1:$C1) > B2, (LOOKUP(99^99, $C$1:$C1) - B2), "0")


Another benefit of the "text zeros" is that it will align them differently, too.

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.

Thanks for your help.

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".