Search code examples
excelexcel-formulaexcel-2007

How to calculate values dynamically from Excel table

I have a programming issue in Excel that I don't know how to solve it. I want to create an automatic Delivery Cost Program on Excel that will help me calculating the cost more easily. The input variables are:

  • Quantity (Values for 1, 2-9,10-49,50+ and more)
  • Shipping method

Depending on the Quantity Value and Shipping method, Excel should lookup on the table and return the total shipping cost according the following Table: 

------------------------------------------
 Delivery |        Per shipment fee         
------------------------------------------
          |    1     2-9   10-49     50+
------------------------------------------             
Standard  |  2,99   1,89     1,5    1,1
Expedited |  5,99      2    1,75    1,25
Priority  | 10,99   3,39    2,25    1,35
------------------------------------------

Let me show you with some examples what I want to get:

1- Example: - Quantity: 15 - Delivery: Expedited - Total Cost = 15 * 1,75 = 26,25$

1,75$ is the returned value after looking on the table using the variable Quantity and Shipping Method.

I have tested doing =IF statements but sure that there is an easier way to do it. I'm not very good on Excel programming so any help will be appreciated

Best regards and have a great day!

Solution

  • Assuming that your table has the delivery types in column A in rows 4 through 6 and that the quantities are in row 3 (columns B through E) the following formula should do it for you:

    =INDEX(B4:E6,MATCH(B9,A4:A6,0),MATCH(C9,B3:E3,1)) * Quantity

    Note, that the quantities in row 3 must be a number. So, the numbers should be 1, 2, 10, and 50 and not 1, 2-9, 10-59, 50+. There are two possibilities to achieve that:

    1. Create a helper row and hide it (while only showing the row with the "names" as you wish for.
    2. Change the number format on these cells: for the column containing the 2 the number format should then be "2-9" (custom number format). For the number 10 the number format would be "10-49" and the number format for the last column would be "50+". Like this you see what you wan to see while the cells still contain numbers only (for the upper formula to work correctly).

    enter image description here