Using FIFO to Determine Sale Price of Fuel

mandyturcotte 

This is a technically far more complicated solution using advanced features of Excel 365.

salesPrice
= LET(
    cumPurchases, SCAN(0,purchaseQuantity, Sumλ),
    cumSales, SCAN(0, quantitySold, Sumλ),
    MAP(cumSales-quantitySold, cumSales,
        LAMBDA(s₀,s₁,
            SUM( 
                MAP(purchasePrice, cumPurchases-purchaseQuantity, cumPurchases,
                    LAMBDA(pr,v₀,v₁,
                        MAX(MIN(v₁,s₁)- MAX(v₀,s₀),0)* pr
                    )
                )
            ) / (s₁-s₀)
        )
    )
 );

The formula accumulates both sales and purchase volumes.  Then it calculates the overlaps and the price of each tranche.  The unit price is obtained by dividing by the sales quantity. 

As the sales quantities are small relative to the purchase quantities, very few sales actually require an interpolated price calculation.