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

1. ## 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!

2. ## 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. ## Re: What am I doing wrong? It won't carry over and deduct properly :(

Originally Posted by JohnTopley
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. ## Re: What am I doing wrong? It won't carry over and deduct properly :(

Originally Posted by JohnTopley
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. ## 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. ## Re: What am I doing wrong? It won't carry over and deduct properly :(

Originally Posted by JohnTopley
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. ## 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. ## Re: What am I doing wrong? It won't carry over and deduct properly :(

Originally Posted by JohnTopley
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. ## 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. ## Re: What am I doing wrong? It won't carry over and deduct properly :(

Originally Posted by JohnTopley
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.

Originally Posted by JohnTopley
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. ## 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. ## Re: What am I doing wrong? It won't carry over and deduct properly :(

Originally Posted by JohnTopley
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. ## 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.

##### Users Browsing this Thread

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

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