I have a detailed spreadsheet with a list of different products (about 1000 - the sheet 'Products' is a shorter example).

CodePudding user response：

Here is a custom function

function sellPrice() {
  var begin = 3
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var currentRow = sh.getActiveRange().getRow()
  var data=sh.getRange(begin,1,currentRow-begin 1,18).getValues()
  if (data[currentRow-begin][4]!="Sold"){return}
  var product = data[currentRow-begin][10]
  var alreadySold = 0
  for (var i=0;i<data.length;i  ){
    if (data[i][10] == product && data[i][4] == 'Sold' && i < (currentRow-begin)){
      alreadySold  = data[i][2]
    }
  }
  var toBeSold = data[currentRow-begin][2]
  var price = 0
  data.forEach(function(row){
    if (row[10] == product && row[4] == 'Purchased'){
      var qty = Math.max(Math.min(row[2] - alreadySold,toBeSold),0)
      alreadySold -= row[2] - qty
      toBeSold -= qty
      price  = qty * row[13]
    }
  })
  return (price/data[currentRow-begin][2])
}

to use it :

=sellPrice($O$1)

check / uncheck on O1 to re-calculate if necessary