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.
