How to define the lowest value in Excel.

Let’s think you have physical storage. When you purchase goods into your warehouse, you have fixed ordering costs per order. The more you order, the lower the ordering costs gets per piece. On the other hand, you have carrying costs for running a warehouse, the more goods you have in the warehouse, the more it costs.

One way to calculate the economic order quantity is simply to count ordering costs per piece plus carrying costs per piece. Then we check the lowest point of total costs per piece.

Column B is a running number of pieces in stock. C2 is ordering costs, 300. That is divided per piece in stock. It costs 4 units to have one piece in stock. That is multiplied by stock piece in D-column. In E-column I have divided the total costs per piece.

Economic order quantity is the lowest point of total costs per piece. We can define that visually to be 9 pieces.

How can we automate the defining the lowest value ?

One way is to create a graph. Activate the range B3:E20 and select insert – line or area chart.

I selected the first 2-D line chart.

The default graph is quite good. We could still change the title and Y axis scale.

Activate the Y-axis, press right mouse button on top of Y axis and select format axis.

The maximum was changed from 350 to 200.

The graph is now more human readable as the Y axis ends with 200. The total cost is at lowest point, when ordering costs and carrying costs intersect.  The point is nine.

MIN is very simple and common function, here it is very useful to find the lowest value in E-column. We can find the lowest value, 69,33, visually. If the data set were larger, it would get more difficult to see which value is the lowest one.

One way to define economic order quantity per piece is MATCH function. The function consists of three arguments: lookup value, lookup array and match type. In this case, lookup value is E2, the minimum value in E-column. The lookup array is the data range E4:E20 where we have total costs per piece. The match type is 0, we are looking for exact match for E2 in E-column. The function returns the value from B-column. That happened pretty easily. The economic order quantity in our case is nine. Then the warehouse costs are at the lowest point.

When MATCH-function is functioning, you can change some input values, and check if economic order quantity changes.

If ordering cost is 340 and carrying cost 3, then economic order quantity is 11.

I have seen INDEX-MATCH nested functions to have been used for cases like this. What we did is like negative vlookup. When vlookup checks the value in left column and then picks up the value in right column, we did opposite.   We were looking for value in E-column, 69,33. When we found that, we check what is the value in the most left column, B-column. The value there is 9.