+ Reply to Thread
Results 1 to 13 of 13

What am I doing wrong? It won't carry over and deduct properly :(

  1. #1
    Registered User
    Join Date
    01-03-2015
    Location
    Pensacola, FL
    MS-Off Ver
    MS 2008 for Mac
    Posts
    29

    What am I doing wrong? It won't carry over and deduct properly :(

    I am not sure what I am doing wrong. If someone can take a look, that would be greatly appreciated (See attached)!

    1. When I enter an amount in D10, it should deduct that amount from C14 and also add the amount to F10.

    2. When I enter an amount in E14, it should deduct from B14 first before it deducts from C14.

    Thank you for your time!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    Formula is C14

    IF(E14<B14,MAX(0,A2-D10),MAX(A2-E14))

    but I note the formulas in C vary

    e.g.

    =IF(E22<A2,A2-D18,A2+A2-E22)

    So I don't really understand what you are doing

  3. #3
    Registered User
    Join Date
    01-03-2015
    Location
    Pensacola, FL
    MS-Off Ver
    MS 2008 for Mac
    Posts
    29

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    Quote Originally Posted by JohnTopley View Post
    Formula is C14

    IF(E14<B14,MAX(0,A2-D10),MAX(A2-E14))
    So my formula above in C14 doesn't work. When I enter say "50" in E14, it should deduct from B14 as the value in there is 74. If I were to enter 200, then it should take all of the 74 in B14 and then 126 from C14 but it gives me a FALSE instead in C14 and does not deduct properly.

  4. #4
    Registered User
    Join Date
    01-03-2015
    Location
    Pensacola, FL
    MS-Off Ver
    MS 2008 for Mac
    Posts
    29

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    Quote Originally Posted by JohnTopley View Post
    but I note the formulas in C vary

    e.g.

    =IF(E22<A2,A2-D18,A2+A2-E22)

    So I don't really understand what you are doing
    I was trying to play around with formulas. The correct formulas will be duplicated in each year. This one doesn't work LOL

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    You need to explain your "business" logic; what do all your "terms" (Banked, Bankable ..) mean.

    Normally, you have an "Opening Balance"

    For any year n:

    Current Balance ("Remaining" ?)= Opening Balance + Income ("Borrowed"?) - Expenditure ("Used"?)

    Opening Balance for year n+1 is "Closing Balance" for year n: is this "Remaining" for year n?

    And is there just one line per year (or is this a much simplified example)?


  6. #6
    Registered User
    Join Date
    01-03-2015
    Location
    Pensacola, FL
    MS-Off Ver
    MS 2008 for Mac
    Posts
    29

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    Quote Originally Posted by JohnTopley View Post
    You need to explain your "business" logic; what do all your "terms" (Banked, Bankable ..) mean.

    And is there just one line per year (or is this a much simplified example)?

    Sorry - it IS a very confusing spreadsheet but it makes sense in our business as long as I can get the formulas to work property.

    Terms
    Banked (B) = the amount leftover from the previous year
    Bankable (C) = the amount of the contract (contract one is 200 (A2) and contract two is 180 (H2))
    Borrowed (D) = when entering an amount in this column, it deducts from the contract amount for the next year (column C).
    Used (E) = the amount used for the year - when entering an amount in here, it should deduct from B before C, but only if B has an amount in it.
    Remaining (F) = the amount remaining at the end of each year
    Banked Date (G) = is the date the amount was banked to the next year (if there's an amount to bank).

    Example of a scenario for two years:

    In 2018 (line 10) - 200 (B10) was Banked from the previous year and only showed up when I entered a date in Banked Date(G6). 326 was Used towards this contract (E10) so it should deduct B10 Banked first and then deduct from C10 Bankable

    In 2019 (line 14) - let's say there's nothing to "Bank" (B), then when I enter an amount in E14, it should only deduct from Bankable (C14), BUT it's not doing that

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    it should deduct from B before C,
    Both B and C use E in their formulas so B cannot be calculated before C: they will change simultaneously.

    You will need VBA if this is required i.e, B calculated before C

  8. #8
    Registered User
    Join Date
    01-03-2015
    Location
    Pensacola, FL
    MS-Off Ver
    MS 2008 for Mac
    Posts
    29

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    Quote Originally Posted by JohnTopley View Post
    Both B and C use E in their formulas so B cannot be calculated before C: they will change simultaneously.

    You will need VBA if this is required i.e, B calculated before C
    Oh goodness ... I'm not sure how to write a VBA.

    In line 10 - I have =IF(G6="",0,MAX(0,C6-E10)) in B10 and =IF(E10<A2,MAX(0,A2-D6),MAX(0,A2+A2-E10)) in C10 and when I enter the amount in E10 it does take from B before taking from C.

    What I can't figure out now is when I have ZERO in B and type in an amount in E, it doesn't "see" that B is zero and it won't deduct from C.

    Example: When I enter 100 in E10 it takes from B10 first when I reach 300 then it will take all of B10 and 100 from C10 (This is correct and what I want), HOWEVER, if I don't enter the date under G6 and now B10 says "0", it won't deduct any amount in E10 where it should deduct the amount now from C10 since B10 is "0".

    Not sure what to do ... any help is appreciated!!

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    It just appears to do so:

    =IF(E10<B10,MAX(0,A2-D6),MAX(0,A2+A2-E10))

    when B=0, E=100 then the result of the above is MAX(0,A2+A2-E10)) =200+200-100 =300

    Why A2+A2 ?

  10. #10
    Registered User
    Join Date
    01-03-2015
    Location
    Pensacola, FL
    MS-Off Ver
    MS 2008 for Mac
    Posts
    29

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    Quote Originally Posted by JohnTopley View Post
    It just appears to do so:

    =IF(E10<B10,MAX(0,A2-D6),MAX(0,A2+A2-E10))

    when B=0, E=100 then the result of the above is MAX(0,A2+A2-E10)) =200+200-100 =300
    SORRY currently it is =IF(E10<A2,MAX(0,A2-D6),MAX(0,A2+A2-E10))

    Basically what I want the formula to do in C10 is take the annual amount, which is 200 (A2) + deduct any borrowed amount from the previous year (D6) so that when I enter an amount in the Used (E10), it will deduct from B10 FIRST (unless it's zero and I did not enter a Banked Date" (G6) before deducting from C10.

    Quote Originally Posted by JohnTopley View Post
    Why A2+A2 ?
    It was the only thing that worked LOL, I don't know ... guessing through it all I guess. When I didn't put A2+A2, it didn't show 200 and did not deduct from B10 first before C10 when entering amount in E10 and some how screwed up the Remaining balance (F10).

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,520

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    Well, if you are "guessing" what hope for us!?

    Looking a post #6

    Banked= Opening balance (for any year) :

    Remaining: Closing Balance

    Bankable ??? other than a fixed (Contract) value

    Used: expenditure for a given year

    Borrowed: ???

    So for any year (in my view)

    Opening Balance (year 1) is Contract Amount

    Closing Balance (year 1) = Opening Balance(year 1) - Expenditure ("Used")- Borrowed (not sure what "borrowed" is - (Brought Forward expenditure?)

    Opening Balance (Year 2)=Closing Balance (year 1)


    Enough for today: too tired to go further ! but it the above should be a model.

  12. #12
    Registered User
    Join Date
    01-03-2015
    Location
    Pensacola, FL
    MS-Off Ver
    MS 2008 for Mac
    Posts
    29

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    Quote Originally Posted by JohnTopley View Post
    Well, if you are "guessing" what hope for us!?

    Looking a post #6

    Banked= Opening balance (for any year) :

    Remaining: Closing Balance

    Bankable ??? other than a fixed (Contract) value

    Used: expenditure for a given year

    Borrowed: ???

    So for any year (in my view)

    Opening Balance (year 1) is Contract Amount

    Closing Balance (year 1) = Opening Balance(year 1) - Expenditure ("Used")- Borrowed (not sure what "borrowed" is - (Brought Forward expenditure?)

    Opening Balance (Year 2)=Closing Balance (year 1)


    Enough for today: too tired to go further ! but it the above should be a model.
    No worries - I'm not sure how else to explain it. I'll figure it out. Was just hoping for someone smarter than me to figure out what I did wrong in my formula that it won't work when I have zero in the Banked column (B)


    1. B10 needs to pull the amount from C6 when a date is entered in G6
    2. B10 amount is then deducted first before the amount in C10 when a value is entered into E10.
    3. C10 starts with 200 (A2) then subtracts value from D6, and then is deducted after the value in B10 has reached zero. If there is no value in B10 then C10 should be deducted right away when a value is entered into E10.
    4. F10 should be that year's remaining amount so B10+C10+D10-E10=

    Everything else in the worksheet is just duplicated formula (changing the cell number, of course).

    Anyways, thanks for trying. Again, no worries. I'll figure a workaround.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    20,140

    Re: What am I doing wrong? It won't carry over and deduct properly :(

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1