Array Formulas With MIN And IF In #Excel

Here is an example of using the array formula process in Excel to determine the MIN number in a list:

I have a table called Produce with a list of fruit and their quantities. I’ve set up a data validation drop-down list of the fruit, and want a formula to give me the minimum quantity for that fruit from the list:

Array1

The key to this formula is that it is comparing a range of products and a range of quantities, creating a sub-list from that group and determining the minimum. That requires an array formula. An array formula is entered using Ctrl + Shift + Enter (CSE) not just Enter, and is identified by curly brackets around the formula:

Array2

If you run through the steps of the Evaluate Formula process on the Formulas tab of the ribbon, you will see how it goes through and ultimately determines which quantities match the selected fruit, and determines the minimum of those quantities:

Array3

So remember, when comparing multiple ranges to determine a filtered set of data that you want to evaluate, use the array process, not just a standard formula.

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

No comments yet.

Leave a Reply