Excel formula.

Copper Contributor

excel formula: Would appreciate it if you could assist me. I have a range of quantities with predetermine price. eg. qty 1-5 price is $50/pc, Qty 6-10 price is $45/pc and qty 11-15 price is $30. how to formulate so that when I put in qty as 3, the price will appear as $50?'

2 Replies
This board is for things related to Microsoft Learn and Microsoft Certifications. I would recommend reaching out on the Microsoft Excel Community https://techcommunity.microsoft.com/t5/excel/ct-p/Excel_Cat

@RoseMeme 

 

I can't speak to the appropriateness (or lack thereof) of this Excel question on this board but I can answer your question fairly easily:

 

  1. Create a two column table starting in cell A1.  Column 1 has the numbers 1,6,11 which correspond to the smallest qty numbers in your three price ranges. Column 2 has the three unit prices of 50, 45, and 30.  In other words: Cell A1=1, A2=6, A3=11, B1=50, B2=45, B3=30.
  2. Go to cell C1 and enter the number 3. This cell will be your INPUT cell where you enter the quantity for which you wish to determine the unit price.
  3. In cell D1, enter the following formula: =VLOOKUP(C1, A1:B3, 2, TRUE). This cell will spit out the OUTPUT result of 50. Play around with entering any number in Cell C1 and watch D1 change accordingly.
  4. The formula in plain speak is saying "Look at the table spanning cells A1 to B3.  Look vertically in the first column of this table for the value that is in cell C1.  If you find it, great.  If you do not, then please find the closest match.  When you find that number or closest match, go over to the 2nd column and spit out the number you see."  The use of the word TRUE is what lets it get the closest match.  If this said FALSE, you would not have the intended results.