Ask Experts Questions for FREE Help !
Ask
    babraham's Avatar
    babraham Posts: 6, Reputation: 1
    New Member
     
    #1

    Nov 14, 2011, 12:39 PM
    Excel formula for running balance
    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.
    Attached Files
  1. File Type: xls Available projects funding formula.xls (18.5 KB, 201 views)
  2. ebaines's Avatar
    ebaines Posts: 12,131, Reputation: 1307
    Expert
     
    #2

    Nov 14, 2011, 12:49 PM
    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?
    babraham's Avatar
    babraham Posts: 6, Reputation: 1
    New Member
     
    #3

    Nov 14, 2011, 01:09 PM
    I would like C2 to remain $0 as no $'s are being applied to that project since it exceeded running available balance.

    babraham's Avatar
    babraham Posts: 6, Reputation: 1
    New Member
     
    #4

    Nov 14, 2011, 01:12 PM
    Sorry missed your latter part of the question, I put quotes around the zero because of my unfamiliarity what I'm trying to accomplish.
    ebaines's Avatar
    ebaines Posts: 12,131, Reputation: 1307
    Expert
     
    #5

    Nov 14, 2011, 01:19 PM
    Quote Originally Posted by babraham View Post
    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 =??
    babraham's Avatar
    babraham Posts: 6, Reputation: 1
    New Member
     
    #6

    Nov 14, 2011, 01:23 PM
    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)
    ebaines's Avatar
    ebaines Posts: 12,131, Reputation: 1307
    Expert
     
    #7

    Nov 14, 2011, 01:51 PM
    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!
    babraham's Avatar
    babraham Posts: 6, Reputation: 1
    New Member
     
    #8

    Nov 14, 2011, 02:07 PM
    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #9

    Nov 15, 2011, 07:47 AM
    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.
    babraham's Avatar
    babraham Posts: 6, Reputation: 1
    New Member
     
    #10

    Nov 15, 2011, 07:11 PM
    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #11

    Nov 16, 2011, 09:00 AM
    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".

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Need an Excel Formula! [ 2 Answers ]

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

Excel formula [ 15 Answers ]

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

Balance the formula with equations,formula,balancing and form [ 2 Answers ]

When silver nitrate solution combine with calcium chloride solution,white precipitate of silver nitrate was formed in calcium nitrate solution

Excel formula [ 3 Answers ]

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

Excel running balance formula [ 5 Answers ]

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!


View more questions Search