How do you use an array formula to sum up elementwise minimum values?
Suppose you plan to buy several computer parts from either BestBuy or Circuit City. The two stores have different prices for each product. If you buy from one store exclusively, you won’t get the best deals possible. To optimize the value of your hardearned dollar, you want to find the least amount of money necessary to buy one of each part.
99.5% of the time, you’d set up a spreadsheet similar to the one shown below. By using a MIN() function, you create a third column listing the lower price of each item. Then it’s a nobrainer to add up the column for the total.
Occassionally, you may want/need to calculate this number in one single step. Array formulas came to mind and it seems intuitive to write {=SUM(MIN([array 1], [array 2]))}. However, that doesn’t work because MIN([array 1], [array 2]) does not give you an array of elementwise minimum values… instead, it gives you a single minimum value between the two arrays.
To fix this problem, you can employ the IF() function within your array formula. This is short, clean, and easy to understand:
{=SUM(IF([array 1]>[array 2], [array 2], [array 1]))}
Or if you prefer to use the mask/filter approach, your formula could look like this:
{=SUM([array 1]*([array 1]<[array 2]) + [array 2]*([array 1]>=[array 2]))}
The ([array 1]<[array 2]) part creates a mask of 0’s and 1’s, which is used in a similar manner to the typical SUMIFtype of array formulas. The two approaches use the same logic, and the only difference is the appearance.
A problem is always simple when you already have the solution, but believe it or not, I came up with the most convoluted approach and a super long formula when this problem was first presented to me. I’ve included that formula in the sample spreadsheet – see if you can figure out what I was trying to do!

Pingback from Excel Workbook · Where to shop? on August 6, 2008 at 5:21 pm

Thank you for posting this. You just saved my day.
Comments are now closed.
4 comments
Trackback link: http://excel.kingofmath.com/wptrackback.php?p=12