英文:
Google Sheets function to divide two arrays and return the lowest number
问题
我制造产品，挑战在于我需要在Google Sheets电子表格中计算当前手头库存可以制作多少我的每个配方（在这种情况下是花束）。
我的数据集显示我的库存数量（每种花的数量）在B列，然后我的产品显示在页面顶部的一行中。产品1显示在C列，产品2在D列等等。
这是一个示例。
将两个数组相除并返回最低数字
由于我有数百种产品以及数百种不同的组件，我需要找出可以将这两个数组相除并返回最小数字的公式，这本质上是我可以制作的花束数量。
提前感谢
我已尝试使用SUMPRODUCT，因为我想知道是否可以将其用于除法以及乘法，但我似乎无法理解这个。
英文:
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
答案1
得分: 1
在C11单元格中使用以下公式：
= ROUNDDOWN(MIN(ARRAYFORMULA(IFERROR( $B2:$B9/C2:C9 ))))
然后，您可以将该公式拖动到D11。
或者，您可以使用新的bycol函数来计算整个数组：
= BYCOL(ARRAYFORMULA(IFERROR( B2:B9/C2:D9 )), LAMBDA(x, ROUNDDOWN(MIN(x)))
英文:
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))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论