Friday, January 3, 2014

Getting the Max Value with Condition in Excel

Happy new year my friends!! It's time for the first post of 2014!. As usual, I was working at a floor where working with Excel it's very important, so I was working by myself until a friend that was close to me just asked me something that I couldn't answer at that moment.

She needed to get the max value from a huge list in Excel, but this value had to be obtained according to a condition, in this case this was a product. Let's do this with an example, supose that the list is about three products (A, B and C) and the months that they were sold expressed in numbers. Thereby, the task is to know what was the last month each product was sold.




To make it very clear let's start with the condition function, we use "IF" to show that it's required a condition, which is going to be searched on "Product" column from "Data" section and the condition is product "A", if true it's going to return the value associated to "Months" column. I know that I'm comparing ranges but this is because it'll be useful later.




Ok, the result of this is "1", meaning that I got just the first value of the comparison. And this happened just because "IF" function when receives a range of cells only takes the first one to evaluate, having A=A then it returns the range of cells specified but in a cell is only allowed one value, the first.




Well, let's try to embrace that function with another one, "MAX" function.




Doing so we get as result "9", which is the max value in the "Data" table, because it's the range returned by "IF" function, then all the calculation is wrong and this is because we are using ordinary functions to make operations over an array.




Then, the trick to fix this is to edit again the calculation and press Ctrl + Shift + Enter keys at the same time, with this you will notice that the result is "4" rathen than "9" being now the correct answer, also see that the all formula is embraced in "{}" which is the notation to operate functions over arrays.




Now, just copy the formula from product "A" to the other products to get the max value for each one.




Remember, using ordinary functions like "IF" with a range of cells just returns the first value, therefore when "MAX" function receives the result of "IF" it jus take the max value of the list returned by this function. To avoid this, it's necessary to apply array functions with Ctrl + Shift + Enter.