Forum Discussion

dscheikey's avatar
dscheikey
Bronze Contributor
Sep 20, 2022

mandyturcotte 

Hello! You could use a function without VBA. XLOOKUP allows you to search for the next largest match. For this, I need an auxiliary column in which the purchased quantities are listed cumulatively. Have a look at the solution in the attached sheet to see if it meets your requirements.

mandyturcotte's avatar
mandyturcotte
Copper Contributor
Sep 20, 2022
It comes close, but ideally I can be more exact with the pricing. With the attached sheet, I price only 22,564 litres at $1.4023 --That's a difference of 125 litres from what I actually purchased at that price. If I'm understanding how this is working, I could be looking at up to a 850 litre (the equipment's max capacity) difference each time the price changes.
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Sep 20, 2022

    mandyturcotte 

    What should the price be when the price changes. A mixed price?

     

    3/16/202212:59:00 PM5206071.4023
    3/16/20221:07:00 PM6082751.3936
    3/16/20223:47:00 PM5462801.3913
    • mandyturcotte's avatar
      mandyturcotte
      Copper Contributor
      Sep 20, 2022
      I think a mixed price is probably the best/easiest way to accomplish it.
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Sep 20, 2022

        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.

Resources