Search code examples
excelgoogle-sheetsspreadsheet

Formula to calculate price for N apples if per apple price changes based on the count of apples

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.

Solution

  • As commented above and as mentioned by OP to post as an answer.

    enter image description here

    Using XLOOKUP() --> put 1 in the match_mode explicitly in the fourth argument.

    enter image description here

    • Formula used in cell H2

    =XLOOKUP(G2,A2:A9,B2:B9,,1)*G2