I want to have the sum of E2:E11 equal to E13. Currently, my formula in cell E2 is =IF(SUMIF(D:D,"<="&D2,B:B)<=E$13,B2,"")
This is almost what I want, except that it only uses the whole amount in column B (which is why cell E12 is 131), when I'd want the remaining 9 to be taken from the next highest ranking number.
Lot A - Throwaway column to help identify everything
Quantity B - Total amount that can be used
Cost C - This is what rank 1 D is based off of
Rank 1 D - I want to use the highest ranking numbers first
Sale 1 E - E2:11 should equal Equal E13. E12 is the sum of E2:E11, which shows that the formula won't sell a partial amount.
This is almost what I want, except that it only uses the whole amount in column B (which is why cell E12 is 131), when I'd want the remaining 9 to be taken from the next highest ranking number.
Lot A - Throwaway column to help identify everything
Quantity B - Total amount that can be used
Cost C - This is what rank 1 D is based off of
Rank 1 D - I want to use the highest ranking numbers first
Sale 1 E - E2:11 should equal Equal E13. E12 is the sum of E2:E11, which shows that the formula won't sell a partial amount.
| Lot (A) | Quantity (B) | Cost (C) | Rank 1 (D) | Sale 1 (E) |
1 | 100 | 10 | 6 | |
2 | 125 | 25 | 5 | |
3 | 15 | 35 | 4 | |
4 | 6 | 8 | 8 | |
5 | 98 | 7 | 9 | |
6 | 55 | 99 | 1 | 55 |
7 | 75 | 50 | 3 | 75 |
8 | 5 | 3 | 10 | |
9 | 3 | 10 | 6 | |
10 | 1 | 70 | 2 | 1 |
| Total From above | 131 | |||
| Target Amount | 140 |
