I have spreadsheet that details the number of items to produce. Some of these items are produced in small and big trays. For example, cinnamon swirls are produced on trays of size 9 or 16 and buns are produced on trays of size 12 and 20. There is an algorithm that decides how many of each item should be produced. Then the numbers are rounded to the number of trays. If there is enough of an item for 1/4 of a tray, it is rounded up. For example, if 23 buns are produced, that would translate to a big tray of 20 and a small tray of 12, so 32 buns total. The recursive algorithm looks like this:

function getRoundedItemAmount(n) {
  if (n > smallTraySize+bigTraySize/4) {
    return bigTraySize + getRoundedItemAmount(n - bigTraySize)
  } else if (n > smallTraySize/4) {
    return smallTraySize + getRoundedItemAmount(n - smallTraySize)
  } else {
    return 0;
  }
}

If they are cinnamon swirls, smallTraySize would be 9 and bigTraySize would be 16. If they are buns, smallTraySize would be 12 and bigTraySize would be 20.

I am wondering if there is any way to convert this algorithm into a sheet formula? I would write the function in appscript and call in the spreadsheet, but the spreadsheets are automatically generated each week and I can’t programmatically attach an appscript function to a new spreadsheet.

Currently there is a cell on the spreadsheet that has the number of the item to be produced. This cell can be edited by the user. I also want the rounded number to appear on the spreadsheet, and update when the production number is changed, hence the desire to use a sheet formula.

I’m honestly not sure if this is possible but I’ll keep digging. Any help is much appreciated!