Notifications
Clear all
Topic starter
Looking for assistance in creating a formula that will deduct QTY Sold from oldest purchase first, then from next oldest and so on. I have attached a simple file.
Data table contains Qty on hand.
User Input shows Qty sold.
Result table is where I want the calculations, results must match figures in the table. Deduct from oldest purchase first, then from next oldest.
I'm struggling to workout a formula.
TIA
Posted : 16/09/2025 7:09 am
@kasey
In H3 and copied down, try this:
=ROUND(
MAX(
0,
MIN(
B3,
$E$3 - SUM($B$2:B3) + B3
)
),
7
)
I wrapped it in a ROUND function to 7 decimals to eliminate minute rounding variances that could occur.
Posted : 16/09/2025 3:50 pm
KM reacted
Topic starter
Riny, thank you so much
Posted : 16/09/2025 6:12 pm