# Excel - IF Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.