So I manufacture products and the challenge is I need to work out in a Google Sheets spreadsheet how many of each of my recipes (flower bouquets in this case) I can make with the current stock on hand.
I have a dataset which shows my stock qty (number of each flower) in column B, then I have my products across the top of the page in a row. Product 1 is shown in column C, Product 2 in D etc....
Here is the example. Divide two arrays and return lowest number
As I have hundreds of products and also hundreds of different component parts I need to work out what formula can essentially divide these two arrays and return to me the MIN or lowest number, this is essentially the number of bouquets I can make.
Thanks in advance
I have tried experimenting with SUMPRODUCT as I wondered if you can use this for division as well as multiplication, but I cannot seem to fathom this
Use the following formula in C11:
= ROUNDDOWN(MIN(ARRAYFORMULA(IFERROR( $B2:$B9/C2:C9 ))))
You can then drag the formula to D11.
Alternatively you can use the new bycol function to calculate the complete array:
= BYCOL(ARRAYFORMULA(IFERROR( B2:B9/C2:D9 )), LAMBDA(x, ROUNDDOWN(MIN(x))))