I have three (3) types of apples in three equal size buckets. - Eatable, Bankable, and Borrowable.
I can eat the apples in the buckets but only the contents of one bucket per day.
I can bank the apples in the buckets if I don’t want to eat any each day. If I don’t eat them or bank them I lose them.
I can borrow the apples in the next day’s buckets if I want to eat more current day.
Each bucket has 160 Eatable apples in them. A, B, and C
I am allowed to eat however many apples are in each bucket, one bucket per day.
I am only allowed to Borrow from A to B, B to A, B to C, or C to B.
The maximum number of apples per bucket that can be “Borrowed” is 160. Day 1 Bucket A:
I take 10 Borrowable apples from Bucket B and place them in Bucket A.
Bucket B now has 150 Borrowable apples that I can still borrow from. I cannot put the 10 “borrowed” apples back into bucket B from A.
Bucket A now has 170 apples that are Eatable. 160 Bankable and 10 Borrowed Day 1 Bucket A
I only eat 30 of the 170 Eatable apples. I eat the 10 borrowed apples first then 20 of the Eatable/Bankable 160.
In Bucket A I now have 140 of the original Eatable/Bankable apples. Day 2 Bucket B: I am hungry
I take 100 Bankable apples from Bucket A and place them in Bucket B. I now have 40 Bankable apples in Bucket A.
Bucket B now has 250 Eatable. 150 Bankable (remember we borrowed 10 yesterday) and 100 Banked.
I’m having friends over tomorrow and we will eat apples from Bucket C.
BUT if I want to take apples from Bucket B and bank them into Bucket C I am only allowed to take up to 150. This is because 160 is all that I am allowed to - Bank/Borrow from bucket B and 10 of the original 160 were Borrowed to bucket A yesterday.
So, I Bank 150 Bankable apples from Bucket B to Bucket C leaving me with 100 (Banked in) Eatable apples in Bucket B from Bucket A.
I now have 310 Eatable apples in Bucket C for my party tomorrow.
So the spreadsheet:
Bucket A is Column A
Bucket B is Column B
Bucket C is Column C
Row 16 is the number of Eatable apples B16.
Row 17 is where our original number of apples of 160 is B17.
Row 18 is the number of apples eaten B18.
Row 19 is the number of Borrowable In apples A19.
Row 20 is the number of Bankable Out apples B20.
Row 22 is another subtraction. Let’s just say it is for giving apples away.
This one is the problem: Row 23 is the number of Banked in apples A20 from the previous days bucket.
The default value of B17 is 160
B17s default value of 160 goes down when =SUM(160-B18-A19-B20-B22)
Each one of the subtractions makes B17 go down. That is desired.
When A20 has a number value this indicates that you have more/extra apples to eat but it cannot make B17s value go up.
B23 now reflects the new value of A20.
As B18s value increases B23s value decreases to 0 when B18s value is greater than or equal to A20.
This is the formula that is used in B23 =IF(SUM(A20-B18)<=0,"",SUM(A20-B18))
B17s value of =SUM(160-B18-A19-B20-B22) is desired except when B23 has a value greater than 0.
What I need is this:
1. Subtract B18 from the 160 default unless the value of B23 greater than 0.
2. When B23 is greater than 0 Don't subtract B18 from 160 until value on B23 equal to
HOWEVER: During that time we still need =SUM(160-A19-B20-B22) to happen in B17.
When the value of B23 is met in B18 only then can B18s value (Including the value that made B23 go down) be subtracted in B17. BUT the value that caused B23 in B18 to go down must remain in B18 plus whatever amount is also in B18.
I understand this is thicker than mud, but this is what I'm dealing with.
These are the steps for the calculation:
B17 equal to 160 (default)
B18 equal to 0 (default)
B23 changes to 50 (Banked In from yesterdays bucket)
B17 still equal to 160
B22 changes to 30 (transferred 50 apples to tomorrows bucket)
B17 now equal to 130
B18 is increased from 0 to the number of apples eaten to 45
B18 now equal to 45
B23 now equal to 5 (50-45=5)
B17 still equal to 130 (because B23 is >0)
B18 is increased by the number of other apples eaten by 20
B18 now equal to 65
B17 now equal to 115 (because 130-15=115 again not subtracting B23s 50)
B23 now equal to 0
Any help is welcome. I can provide a copy of the spreadsheet if that helps.
I can eat the apples in the buckets but only the contents of one bucket per day.
I can bank the apples in the buckets if I don’t want to eat any each day. If I don’t eat them or bank them I lose them.
I can borrow the apples in the next day’s buckets if I want to eat more current day.
Each bucket has 160 Eatable apples in them. A, B, and C
I am allowed to eat however many apples are in each bucket, one bucket per day.
I am only allowed to Borrow from A to B, B to A, B to C, or C to B.
The maximum number of apples per bucket that can be “Borrowed” is 160. Day 1 Bucket A:
I take 10 Borrowable apples from Bucket B and place them in Bucket A.
Bucket B now has 150 Borrowable apples that I can still borrow from. I cannot put the 10 “borrowed” apples back into bucket B from A.
Bucket A now has 170 apples that are Eatable. 160 Bankable and 10 Borrowed Day 1 Bucket A
I only eat 30 of the 170 Eatable apples. I eat the 10 borrowed apples first then 20 of the Eatable/Bankable 160.
In Bucket A I now have 140 of the original Eatable/Bankable apples. Day 2 Bucket B: I am hungry
I take 100 Bankable apples from Bucket A and place them in Bucket B. I now have 40 Bankable apples in Bucket A.
Bucket B now has 250 Eatable. 150 Bankable (remember we borrowed 10 yesterday) and 100 Banked.
I’m having friends over tomorrow and we will eat apples from Bucket C.
BUT if I want to take apples from Bucket B and bank them into Bucket C I am only allowed to take up to 150. This is because 160 is all that I am allowed to - Bank/Borrow from bucket B and 10 of the original 160 were Borrowed to bucket A yesterday.
So, I Bank 150 Bankable apples from Bucket B to Bucket C leaving me with 100 (Banked in) Eatable apples in Bucket B from Bucket A.
I now have 310 Eatable apples in Bucket C for my party tomorrow.
So the spreadsheet:
Bucket A is Column A
Bucket B is Column B
Bucket C is Column C
Row 16 is the number of Eatable apples B16.
Row 17 is where our original number of apples of 160 is B17.
Row 18 is the number of apples eaten B18.
Row 19 is the number of Borrowable In apples A19.
Row 20 is the number of Bankable Out apples B20.
Row 22 is another subtraction. Let’s just say it is for giving apples away.
This one is the problem: Row 23 is the number of Banked in apples A20 from the previous days bucket.
The default value of B17 is 160
B17s default value of 160 goes down when =SUM(160-B18-A19-B20-B22)
Each one of the subtractions makes B17 go down. That is desired.
When A20 has a number value this indicates that you have more/extra apples to eat but it cannot make B17s value go up.
B23 now reflects the new value of A20.
As B18s value increases B23s value decreases to 0 when B18s value is greater than or equal to A20.
This is the formula that is used in B23 =IF(SUM(A20-B18)<=0,"",SUM(A20-B18))
B17s value of =SUM(160-B18-A19-B20-B22) is desired except when B23 has a value greater than 0.
What I need is this:
1. Subtract B18 from the 160 default unless the value of B23 greater than 0.
2. When B23 is greater than 0 Don't subtract B18 from 160 until value on B23 equal to
HOWEVER: During that time we still need =SUM(160-A19-B20-B22) to happen in B17.
When the value of B23 is met in B18 only then can B18s value (Including the value that made B23 go down) be subtracted in B17. BUT the value that caused B23 in B18 to go down must remain in B18 plus whatever amount is also in B18.
I understand this is thicker than mud, but this is what I'm dealing with.
These are the steps for the calculation:
B17 equal to 160 (default)
B18 equal to 0 (default)
B23 changes to 50 (Banked In from yesterdays bucket)
B17 still equal to 160
B22 changes to 30 (transferred 50 apples to tomorrows bucket)
B17 now equal to 130
B18 is increased from 0 to the number of apples eaten to 45
B18 now equal to 45
B23 now equal to 5 (50-45=5)
B17 still equal to 130 (because B23 is >0)
B18 is increased by the number of other apples eaten by 20
B18 now equal to 65
B17 now equal to 115 (because 130-15=115 again not subtracting B23s 50)
B23 now equal to 0
Any help is welcome. I can provide a copy of the spreadsheet if that helps.
