jim may
Well-known Member
- Joined
- Jul 4, 2004
- Messages
- 7,486
The left table (Columns A:E ) is my recent Purchase History of a product. Next, Columns G and H are my day-end Inventory Qtys. I'd like a formula in Column I that
would begin by searching the History table, in the example shown since I want the value as of 5/31/2015, I should first select (from the history table) the first date = to, or less than 5/31/2015, and in this case the 5/28/2015 row. And keep going up accumulating the full extended values, BUT no more that the Qty on Hand. In the # 5 layer I only need 586 of the 600 bought. I'm at a loss as to how to beging this formula. Can anyone assist? TIA, Jim
would begin by searching the History table, in the example shown since I want the value as of 5/31/2015, I should first select (from the history table) the first date = to, or less than 5/31/2015, and in this case the 5/28/2015 row. And keep going up accumulating the full extended values, BUT no more that the Qty on Hand. In the # 5 layer I only need 586 of the 600 bought. I'm at a loss as to how to beging this formula. Can anyone assist? TIA, Jim
Excel 2012 | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
1 | ||||||||||||||||||
2 | From Layer/Series # >> | 5 | 4 | 3 | 2 | 1 | ||||||||||||
3 | Layer or | ** PURCHASING HISTORY ** | ** DAY END ON HAND INVENTORY ** | Qty in Layer/Series >> | 500 | 500 | 600 | 400 | 586 | << Limit total to 2,586 | ||||||||
4 | Series # | Date | Qty | Extension | U/Price | Date | Qty | Value | U/Price Layer/Series >> | $ 4.10 | $ 3.90 | $ 3.90 | $ 3.85 | $ 3.90 | ||||
5 | 1 | 05/10/2015 | 600 | 2,340.00 | $ 3.9000 | 05/31/2015 | 2,586 | $ 10,165.40 | << Need Formula to Produce | 2050 | 1950 | 2340 | 1540 | 2285.4 | $ 10,165.40 | |||
6 | 2 | 05/15/2015 | 400 | 1,540.00 | $ 3.8500 | 06/01/2015 | 2,398 | which I have computed here >> | ||||||||||
7 | 3 | 05/20/2015 | 600 | 2,340.00 | $ 3.9000 | 06/02/2015 | 2,398 | |||||||||||
8 | 4 | 05/25/2015 | 500 | 1,950.00 | $ 3.9000 | 06/03/2015 | 2,236 | |||||||||||
9 | 5 | 05/28/2015 | 500 | 2,050.00 | $ 4.1000 | 06/04/2015 | 2,686 | |||||||||||
10 | 6 | 06/04/2015 | 500 | 1,936.97 | $ 3.8739 | 06/05/2015 | 2,560 | |||||||||||
11 | 7 | 06/07/2015 | 450 | 1,777.12 | $ 3.9492 | 06/06/2015 | 2,530 | |||||||||||
12 | 8 | 06/09/2015 | 30 | 130.00 | $ 4.3333 | 06/07/2015 | 2,860 | |||||||||||
13 | 9 | 06/14/2015 | 320 | 1,643.07 | $ 5.1346 | 06/08/2015 | 2,700 | |||||||||||
14 | 10 | 06/18/2015 | 260 | 950.93 | $ 3.6574 | 06/09/2015 | 2,531 | |||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I5 | =SUM(K5:O5) | |
K3 | =C9 | |
K4 | =E9 | |
K5 | =K3*K4 | |
L3 | =C8 | |
L4 | =E8 | |
L5 | =L3*L4 | |
M3 | =C7 | |
M4 | =E7 | |
M5 | =M3*M4 | |
N3 | =C6 | |
N4 | =E6 | |
N5 | =N3*N4 | |
O3 | =H5-SUM(K3:N3) | |
O4 | =E5 | |
O5 | =O3*O4 | |
P5 | =SUM(K5:O5) |