**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need to help on IF formulas to calculate year to year (deducted, adding, combining, etc) automatically from one year to the next.

Solved by E. H. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
04/05/2018 - 05:18

Hi Welcome to Gotit

User
04/05/2018 - 05:19

Hi there!

Excelchat Expert
04/05/2018 - 05:19

yes

Excelchat Expert
04/05/2018 - 05:19

Please explain your problem

User
04/05/2018 - 05:19

So if you look I have banked, bankable, borrowed, used, remaining.

Excelchat Expert
04/05/2018 - 05:20

yes

User
04/05/2018 - 05:21

I need each year to work together. Every year C2 is the amount that is deposited into the Bankable column each year and that doesn't change except for when the previous year has something in the "borrowed" column

User
04/05/2018 - 05:22

The banked amount and bankable amount is reflected in the remaining and if I was to put a figure in the borrowed amount or used amount it deducts or adds to the remaining.

Excelchat Expert
04/05/2018 - 05:22

So remaining should have banked+bankable amount

User
04/05/2018 - 05:23

Hold on the formulas didn't copy over.

User
04/05/2018 - 05:24

Okay so example: If I but 50 in the used, it'll deduct the "banked amount first until thats all gone then it'll take the bankable amount.

User
04/05/2018 - 05:25

150

User
04/05/2018 - 05:25

So now what I need is the Borrowed amount in the next year "2019" and on going to deduct the "borrowed" column but it starts off the C2

Excelchat Expert
04/05/2018 - 05:25

then remaining should be 100 right?

Excelchat Expert
04/05/2018 - 05:26

instead of C2 you what it to be remaining right?

User
04/05/2018 - 05:27

No okay so the formulas in there so far is correct for that year, it's when I need to "Borrow" from the next year where the next "2019" formulas are not working

User
04/05/2018 - 05:28

Ever year the "Bankable amount starts with C2 (200) but depending on what happens in the previous year or the next year, the number changes.

Excelchat Expert
04/05/2018 - 05:28

2019 should use remaining amount of 2018 right?

Excelchat Expert
04/05/2018 - 05:29

I understood the formula you wrote for 2018

User
04/05/2018 - 05:29

I have borrowed 50 in G6 and so Bankable should say "150" which is the C2-g6 BUT it needs to combine the Bankable formula in F6

Excelchat Expert
04/05/2018 - 05:31

used should first deduct from banked and borrowed should deduct from bankable?

User
04/05/2018 - 05:32

Remaining would show the total.

User
04/05/2018 - 05:32

Banked amount needs to be deducted first in the Used before the bankable can be touched

Excelchat Expert
04/05/2018 - 05:32

ok I got that

Excelchat Expert
04/05/2018 - 05:32

what about borrowed?

User
04/05/2018 - 05:33

Example: I take 50 from 2019 L6 so now that amount in 2019 should show 150 instead of the 200 since I borrowed from C2 which is deposited every year

User
04/05/2018 - 05:33

BUT because I borrowed now my bankable needs to be reduced and so forth.

User
04/05/2018 - 05:34

Remaining will be Banked + Bankable

Excelchat Expert
04/05/2018 - 05:34

same for 2018 also right?

User
04/05/2018 - 05:35

Well 2018 has no 2017 to gather from so I guess the new formulas need to start with 2019

User
04/05/2018 - 05:36

The "borrowed" and "Used" column should be formula free so I can enter what I borrowed or used per year. The tricky part is what if I "Borrow" from the next year then my "Bankable" amount will change

Excelchat Expert
04/05/2018 - 05:36

I will make a formula and let you know see it correct or not

User
04/05/2018 - 05:37

Thank you so much for your help!

User
04/05/2018 - 05:38

So basically 2019 Bankable should be the formula of F6- what I used in the Borrowed column for G6

Excelchat Expert
04/05/2018 - 05:40

Can you please check formula in K6

User
04/05/2018 - 05:41

So I deleted Borrowed in the 2018 column so bankable should be 200 since every year I start off with 200.

Excelchat Expert
04/05/2018 - 05:41

Hello

User
04/05/2018 - 05:42

The 2019 is basically grabbing info from the previous year so that the info is rolling each year

Excelchat Expert
04/05/2018 - 05:42

Should I use borrowed of 2018 or 2019?

Excelchat Expert
04/05/2018 - 05:42

I used 2018

User
04/05/2018 - 05:42

If i put an amount in G6 that should effect my bankable amount of K6

Excelchat Expert
04/05/2018 - 05:42

Now check

User
04/05/2018 - 05:43

So now the remaining in N6 will be what? The same formula as I6?

Excelchat Expert
04/05/2018 - 05:44

yes

Excelchat Expert
04/05/2018 - 05:44

But reference columns should change

User
04/05/2018 - 05:44

Ok

Excelchat Expert
04/05/2018 - 05:45

Any more queries?

Excelchat Expert
04/05/2018 - 05:45

Please end the session if you don't have queries

Excelchat Expert
04/05/2018 - 05:45

Have a nice day

User
04/05/2018 - 05:45

Yes

User
04/05/2018 - 05:45

It's not working

Excelchat Expert
04/05/2018 - 05:45

which?

User
04/05/2018 - 05:46

So in my Used column it should take from my banked first then my bankable

Excelchat Expert
04/05/2018 - 05:46

yes

Excelchat Expert
04/05/2018 - 05:46

in 2018 I can see banked as 100 and bankable as 200

Excelchat Expert
04/05/2018 - 05:47

when you added 100 in H6

Excelchat Expert
04/05/2018 - 05:47

I modified formula in K6

User
04/05/2018 - 05:48

No

Excelchat Expert
04/05/2018 - 05:49

what happened

User
04/05/2018 - 05:49

My used should not change my bankable unless I put an item in the Borrowed from the previous year

User
04/05/2018 - 05:49

so like right now ... I have zero borrowed from 2018 so my 2019 should say 200

User
04/05/2018 - 05:50

which is the C2 amount each year is deposited

Excelchat Expert
04/05/2018 - 05:50

ok

User
04/05/2018 - 05:50

It's only if I "borrow" from the previous year does this bankable amount change

User
04/05/2018 - 05:52

Am I confusing you?

User
04/05/2018 - 05:52

I'm so sorry!

Excelchat Expert
04/05/2018 - 05:53

ok

Excelchat Expert
04/05/2018 - 05:55

I question

Excelchat Expert
04/05/2018 - 05:55

If used is more than banked

Excelchat Expert
04/05/2018 - 05:55

it should deduct from bankable

Excelchat Expert
04/05/2018 - 05:55

you made a formula for that

Excelchat Expert
04/05/2018 - 05:56

we have to use the same formula for 2019 right?

User
04/05/2018 - 05:58

Okay ... 2018 ... I have banked 200, and every year I receive 200 so it gave me a remaining balance for 2018 as 400. If I put a number in the Used then it'll take my banked amount first then my bankable. If I put something in the borrowed column then it'll deduct the amount that I have bankable for 2019. The remaining balance will be what I have in the banked and bankable each year.

Excelchat Expert
04/05/2018 - 06:00

Now check

User
04/05/2018 - 06:01

Let's do an example: 2018 I will use 450 so my remaining balance should be a negative "red" amount of 50 until I add 50 in the borrowed. When I put the amount borrowed in 2018 (G6) that should change the amount I have in my bankable in 2019 which I would think now my Remaining formula in 2019 will differ from 2018??

User
04/05/2018 - 06:01

Okay let me check

User
04/05/2018 - 06:02

No mu 2018 Banked should say 200 as that is the starting point from 2017 which isn't in this spreadsheet

User
04/05/2018 - 06:04

In the 2019 "Banked" it should be zero until I enter the amount that I will bank which will most likely be the bankable from 2018. I guess I need to make a column between each year that says the date I banked and when that cell has a date in it, then it'll generate the amount banked?

Excelchat Expert
04/05/2018 - 06:04

I code for 2019 also

Excelchat Expert
04/05/2018 - 06:05

SO when I added used in 2019 it go deducted

Excelchat Expert
04/05/2018 - 06:05

test for both 2018 and 2019

User
04/05/2018 - 06:06

No so in 2019 where it says "Banked" J6 it says 200 ... I don't have anything to bank because I'm at -100 in 2018

Excelchat Expert
04/05/2018 - 06:07

you said it should 200 which come from c2

User
04/05/2018 - 06:07

So I don' have an amount that I am able to bank into the following year. The bankable is basically C2 (my start off amount per year) minus anything borrowed from the previous year

Excelchat Expert
04/05/2018 - 06:08

what banked should have in J6

User
04/05/2018 - 06:08

In E6 the "banked amount came from 2017 (which I don't have on this spread sheet). It's starting from 2019 where the formula needs to include more than what my 2018 formulas include.

User
04/05/2018 - 06:09

So J6 because I have a negative 100 in bankable F6 banked should be zero in J6

Excelchat Expert
04/05/2018 - 06:09

oh so J6= E6 right?

User
04/05/2018 - 06:10

Nope

Excelchat Expert
04/05/2018 - 06:10

if bankable has positive then?

User
04/05/2018 - 06:10

Sorry so confusing ... let me try again

Excelchat Expert
04/05/2018 - 06:11

You said bankable is negative so J6 should be E6 if bankable is not negative then what should J6 have

Excelchat Expert
04/05/2018 - 06:12

Time is running out

Excelchat Expert
04/05/2018 - 06:13

Hello are you there?

User
04/05/2018 - 06:13

2017 I had banked 200 which is shown in E6 (200), the bankable in 2018 is equal to C2 because it's my starting year. Now fast forward to 2019 formulas ... The banked J6 will only have an amount if I actually put in an amount so I think I need a column between the years so that IF the cell has a date in it, then the "bankable F6" amount should fill into the "Banked" J6 and so forth. The Bankable in 2019 reflect my starting of C2 (200) minus anything I enter in the "Borrowed" G6,

Excelchat Expert
04/05/2018 - 06:17

I think Banked should be a input

Excelchat Expert
04/05/2018 - 06:17

Or it should be remaining of 2018

User
04/05/2018 - 06:17

Ok

Excelchat Expert
04/05/2018 - 06:17

K6 is working correctly

Excelchat Expert
04/05/2018 - 06:17

Have a nice day

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*