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. 
 
Excel spreadsheet with maximum to calculate

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.

Using in Excel function to get a conditional value

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.

Only the first value is considered by function in Excel
 
Well, let's try to embrace that function with another one, "MAX" function.

Using the MAX function in Excel
 
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.

Not correct value as using a simple function in a matrix

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.

Use shortcut in Excel to change function to be applied in matrix

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

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.


Comments

Post a Comment

Popular posts from this blog

I Need to Send an Automatic Email with Lotus Notes

Image
If you don't know, Lotus Notes is an email client used in a lot of companies around the world. Therefore, I use it at work everyday, but someday I needed to send an automatic email to a few people all Fridays at 10:00.   To get this task accomplished I decided to create an agent and avoid remembering the fact of sending the email. As you can image, I did this example on a Spanish version of Lotus Notes 8.5 but I will explain it clearly enough. The first step is getting yourself into the inbox, then select the "Create" menu item and inside of it, click on "Agent...". You will get a dialog box opened, in this we have the possibility of setting the rules for this agent. However, in this case we will only set "Trigger" option to "On schedule" with the "Weekly" frequency. Don't forget to push the "Schedule..." button to program the agent in the right way. After this, select "OK" and close the dialog bo
Read more

Inserting Rows o Columns in Excel Not Permitted

Image
I'm opening an Excel file and when I try to insert a new row or column I get a message error giving me a large text about how this is not possible. I have seen many people facing this message, although this gives the solution, sometimes it's hard to understand what this message is trying to say.     But, what's happening? Let me tell you that you' re using all the rows or columns of the file, yes, believe it or not you are having information (even blank space) at least at the last row or column allowed. So, when you want to add a new one, Excel tries to delete this row or column because it will be out of range with the new element inserted. The solution is going to the last column or row, to do so you can use this shortcut: Ctrl + Down arrow if you want to go to the last row or Ctrl + Right arrow to go to the last column, as shown in the images.         Then, you just have to select the entire row or column and delete, the next step is to save the file to t
Read more

Turn Horizontal Visio Swimlanes into Vertical Ones

Image
In one ocassion, a friend at work called me because she had a problem with Microsoft Visio , she was trying to draw a process flowchat but using swimlanes, this was not a problem until she realized that it would be better to use vertical swimlanes instead of horizontal ones.    But, when she tried to drag a vertical one it just put together to the previous swimlanes created before.  To avoid creating a new visio document and lost your work just go to 'Cross-Functional Flowchart' ribbon and select 'Orientation' and 'Vertical'.     And it's done, now you can work with vertical swimlanes or if you like it you can turn them back to horizontal ones.
Read more