英文:
Formula to calculate price for N apples if per apple price changes based on the count of apples
问题
假设我有一个如下所示的表格。该表格表示，如果知道苹果的数量，则可以知道每篮子苹果的价格。
Count of Apple | Price per Apple
0-10 | 0
11-20 | 1美元
21-30 | 2美元
31-40 | 3美元
41-50 | 4美元
51-60 | 5美元
例如，如果我购买34个苹果，那意味着我需要查看表格中的第4行“31-40 | 3美元”，因为34位于31和40之间。因此，34个苹果的每个苹果价格将为3美元，这意味着34个苹果将花费34 * 3美元 = 102美元。
第二个示例是如果我有4个苹果。在这种情况下，我需要查看第一行“0-10 | 0”，这里一个苹果的价格为0美元，即4个苹果的价格将为4 * 0美元 - 0美元。
现在我需要在电子表格中表示这些信息，并计算N个苹果的总价格的公式。这里是一个示例。 我如何得到这个公式？
我可以通过编写大量的“if”语句来计算这个问题，但因为我的表格可能有超过5-10行，这将变得不可能。
英文:
Imagine I have a table like below. The table says that if the count of apples is known then we know the price per apple in that busket.
Count of Apple | Price per Apple
0-10 | 0
11-20 | 1$
21-30 | 2$
31-40 | 3$
41-50 | 4$
51-60 | 5$
For example if I buy 34 apples, that means that I need to look at the 4th line 31-40 | 3$ in my table as 34 is in between 31 and 40. Therefor, for 34 apple per apple price will be 3$ and that means 34 apples will cost 34 * 3$ = 102$.
Example 2 would be if I have 4 apples. In this case I need to look at the first line 0-10 | 0 and here one apple costs 0$, i.e. 4 apple price will be 4 * 0$ - 0$.
Now it need to have in spreadsheets a table representing the information in and the formula to calculate the total price of N apples. Here is a sample. How do I get the formula?
I can calculate this by writing tones of if statements, but because my table might have more than 5-10 rows, it will be just impossible to do.
答案1
得分: 3
如下所示和由“OP”提到的以回答帖子。
使用 XLOOKUP() --> 在第四个参数中明确设置 1 作为 match_mode。
• 在单元格 H2 中使用的公式
=XLOOKUP(G2,A2:A9,B2:B9,,1)*G2
英文:
As commented above and as mentioned by OP to post as an answer.
Using XLOOKUP() --> put 1 in the match_mode explicitly in the fourth argument.
• Formula used in cell H2
=XLOOKUP(G2,A2:A9,B2:B9,,1)*G2
答案2
得分: 2
So, apart from xlookup(), both of these versions work:
Then you can multiply by the number either in the cell F2:
VLOOKUP(D2,A2:B7,2,1)* D2
or in a separate cell:
=F2*D2
英文:
So, apart from xlookup(), both of these versions work:
Then you can multiply by the number either in the cell F2:
VLOOKUP(D2,A2:B7,2,1)* D2
or in a separate cell:
=F2*D2
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论