JasmineSilver
New Member
- Joined
- Mar 18, 2017
- Messages
- 4
Hi all,
I have 4 individual numbers, A1-A4 and a range of 4 cells, B1:B4. A1 is supposed to agree to B1, A2 to B2, etc. If A1 is greater than B1, I want the difference to be added to A2 but only if A2 is less than B2, and only until A2 equals B2.
If instead A1 is less than B1, then if A2 is greater than B2, I want the difference from it to be added to A1, but only until A1 equals B1. I hope I'm making sense.
Here's an example:
A1: 400 B1: 500
A2: 650 B2: 500
A3: 500 B3 500
A4: 450 B4: 500
So A1 is under by 100, and I need to pull from the cell below it to make up that 100. If the cell below happens to be less than 100, then I need to pull from the next cell also, until we reach 100. Then A2 must do the same in order to get the value to equal B2, and so on. So A1 gets priority, and once satisfied, A2 gets priority. Finally, I need to know how much A1 pulled from each of the cells below it, how much A2 pulled from each of the cells below it, etc.
If you understand how estimated tax payments work, I'll explain what I'm trying to do. A company must make 4 payments of $500 each (this can vary) every 4 months. So the company should follow range B, but sometimes they will do something like you see in Range A. If they underpaid on A1, then I'm calculating a penalty separately based on how much was underpaid (100). So I need to know when to stop accruing the penalty based on which cells below provided the 100. Usually A2 will be greater than 100, so usually I would stop accruing based on payment 2. But sometimes A2 is under 100, so the remaining portion must be pulled from A3, and perhaps even A4.
In the example above, I need to know which cell provided A1's missing 100, which in this case it should be A2 (but again, even if A2 was 150 instead of 650, the 100 would still get pulled from it because A1 is prioritized until it agrees to B1.) That leaves an extra 50 (650 - 500 - 100) in A2. A3 is fine as is, but A4 needs 50, so I need to know that A4 pulled 50 from A2. I'm incorporating this info into a penalty formula. So for A4, I wouldn't calculate a penalty because while it is less than B4 by 50, that 50 was prepaid with A2.
I have a feeling I'm speaking gibberish, haha. My apologies if I'm not explaining this clearly. Please let me know if there's anything I can clarify. And thank you all.
I have 4 individual numbers, A1-A4 and a range of 4 cells, B1:B4. A1 is supposed to agree to B1, A2 to B2, etc. If A1 is greater than B1, I want the difference to be added to A2 but only if A2 is less than B2, and only until A2 equals B2.
If instead A1 is less than B1, then if A2 is greater than B2, I want the difference from it to be added to A1, but only until A1 equals B1. I hope I'm making sense.
Here's an example:
A1: 400 B1: 500
A2: 650 B2: 500
A3: 500 B3 500
A4: 450 B4: 500
So A1 is under by 100, and I need to pull from the cell below it to make up that 100. If the cell below happens to be less than 100, then I need to pull from the next cell also, until we reach 100. Then A2 must do the same in order to get the value to equal B2, and so on. So A1 gets priority, and once satisfied, A2 gets priority. Finally, I need to know how much A1 pulled from each of the cells below it, how much A2 pulled from each of the cells below it, etc.
If you understand how estimated tax payments work, I'll explain what I'm trying to do. A company must make 4 payments of $500 each (this can vary) every 4 months. So the company should follow range B, but sometimes they will do something like you see in Range A. If they underpaid on A1, then I'm calculating a penalty separately based on how much was underpaid (100). So I need to know when to stop accruing the penalty based on which cells below provided the 100. Usually A2 will be greater than 100, so usually I would stop accruing based on payment 2. But sometimes A2 is under 100, so the remaining portion must be pulled from A3, and perhaps even A4.
In the example above, I need to know which cell provided A1's missing 100, which in this case it should be A2 (but again, even if A2 was 150 instead of 650, the 100 would still get pulled from it because A1 is prioritized until it agrees to B1.) That leaves an extra 50 (650 - 500 - 100) in A2. A3 is fine as is, but A4 needs 50, so I need to know that A4 pulled 50 from A2. I'm incorporating this info into a penalty formula. So for A4, I wouldn't calculate a penalty because while it is less than B4 by 50, that 50 was prepaid with A2.
I have a feeling I'm speaking gibberish, haha. My apologies if I'm not explaining this clearly. Please let me know if there's anything I can clarify. And thank you all.
