View Question
Q: Excel "Maxif" and "Minif" Function ( Answered ,   0 Comments )
 Question
 Subject: Excel "Maxif" and "Minif" Function Category: Computers > Algorithms Asked by: addisonbr-ga List Price: \$10.00 Posted: 21 May 2003 15:38 PDT Expires: 20 Jun 2003 15:38 PDT Question ID: 207026
 ```Excel has a "sumif" function; I need the equivalent of a "maxif" function (or a flexible workaround) for Excel 2000. I have a selection of securities, with market data from several different exchanges (bids, asks, volumes, etc.). In each row, I want a function that takes the security from that row returns the largest (or smallest) values from among all of the markets. Here is an example - columns A - D represent my data, columns E-G represent the values I would like returned. A B C D E F G Stock Bid Ask Volume MaxBid MaxAsk MaxVol MSFT 23 25 1000 23 24 1500 MSFT 23 24 1500 23 24 1500 MSFT 22 24 1250 23 24 1500 INTC 18 20 2000 19 20 3000 INTC 19 21 3000 19 20 3000 DELL 30 32 1500 30 31 2000 DELL 29 31 2000 30 31 2000 DELL 29 32 1000 30 31 2000 DELL 30 31 500 30 31 2000 The number of markets for each security varies and is not constant from day to day - I get a daily data download from a third party, which makes the manual brute force fixes to my problem useless the next day - so the solution needs to be as hassle-free as any other formula I might plug into my spreadsheet (like sumif for example). I do not want an array formula solution (for a variety of reasons). Can anyone provide me with a "maxif" or "minif" solution that works much the same way that "sumif" does? Maybe a formula trick, or a user-defined function I can throw into VB?``` Request for Question Clarification by gwagner-ga on 21 May 2003 15:59 PDT ```Would the "max" function in connection with "vlookup" do the trick? You'd create another table using the "vlookup" method and then choose the largest one from the new table. Or would that be the array formula solution you don't want? gwagner-ga```
 ```Hello Addisonbr, You want to use something called an "array formula" to do what you ask. You can get an explanation of array formulas by searching the Excel help with the phrase array or array formula A brief explanation on my system is under an entry titled "About Array Formulas and How to Enter Them". You help file may be slightly different - check for similar names if this one does not appear. Note in particular the way you "enter" the formula. There are a few examples under this entry as well - one very similar to the one I show below. The formulas I entered into cell E2 using your sample data was... =MAX(IF(\$A\$2:\$A\$10=\$A2,B\$2:B\$10,0)) though after you enter this as an array formula, it will be shown as {=MAX(IF(\$A\$2:\$A\$10=\$A2,B\$2:B\$10,0))} in the formula bar. Cell E2 shows the value 23 after entry. If you get an error at this point, make sure the formula is typed correctly and that it is entered properly (entered without {}, but displays with {}). Copying and pasting the "formula I entered" into the formula bar when cell E2 is selected and then doing the "array formula entry" should work - let me know if it does not. If you are doing your own examples, one possible problem occurs if the ranges of values don't have the same number of entries (in this case 2:10 or nine) in each range. Don't do this next step until the formula is correct. Select the range E2 through G10 and do a "Fill Down" (Edit -> Fill -> Down) and "Fill Right" (Edit -> Fill -> Right). You will see all the values shown as their proper values. Sounds pretty easy, doesn't it. The reason it works is because of the funny references described below. Let me explain how this works. First the references... - \$A\$2:\$A\$10 is an "absolute reference" to the first column of names. This value will not change when you do the fill down nor fill right. If you click on any of the cells with the formula, you will see this in the formula bar. - \$A2 is an absolute reference to the first column and a relative reference to the current row. Each row will show something like \$A3, \$A4, and so on up to \$A10. This makes the IF function apply to the name in the first column on the current row. - B\$2:B\$10 is a relative reference to column B and absolute reference to rows 2 through 10. If you click on a cell in column F, this will appear as C\$2:C\$10; in column G, this will appear as D\$2:D\$10. This is how each column has a different value passed to the MAX function. - 0 is used as the "smallest value" in this case. Depending on the type of data you are analyzing, make it a large positive number if using MIN and a large negative number if using MAX. Note that if you just select a region with Excel, it is usually has relative references. This does not help you when using the two fill operations and you have to fix the references in the formula bar to "make them right". The IF function is also described in Excel help, but briefly if the first expression (\$A\$2:\$A\$10=\$A2) is true, the first value (B\$2:B\$10) is returned, otherwise the second value (0) is returned. In cell E2, that value is true only for the first three rows of data and false for the remaining items. Since this is an array formula, in cell E2, we get an array result {23,23,22,0,0,0,0,0,0} In other cells, you get similar values based on the relative / absolute references described above. The MAX function works as you expect for the array result. In cell E2, we get a single valued result of 23. Any function like MIN, AVERAGE, and others that operate with array values and return a single value will work in a similar manner. If you know how to define names, you can replace the absolute / relative references with a name - let me know if you want to use an enhancement like that. If any of this is unclear or you want to pursue some related enhancements (such as using names), please use a clarification request. --Maniac``` Clarification of Answer by maniac-ga on 22 May 2003 05:31 PDT ```Hello Addisonbr, I wish I had seen that restriction earlier; I will attempt to read future questions more fully. To aid the researchers reading your questions, please place a restriction like that on a line by itself so it appears more clearly. I also ask you to request a question clarification in cases like this one; it allows the researcher to retract an answer. I apologize for the incorrect answer and to correct it I offer the following: Using Visual Basic, enter the following function Function IfMax(Selector, Match, Value) ' ' IfMax Macro ' Macro created 5/22/2003 by Maniac ' ' Max = Value.Cells(1).Value For N = 1 To Match.Count If (Match.Cells(N).Value = Selector.Value) Then If (Max < Value.Cells(N).Value) Then Max = Value.Cells(N).Value End If End If Next N IfMax = Max End Function In cells E2:G10 change them to read =IfMax(\$A2,\$A\$2:\$A\$10,B\$2:B\$10) This will refer to the function defined above - providing the three parameters required by the function. Let me explain how this function works. The IfMax function is passed three parameters: - the cell to match - the range of cells to match - the range of cells with the value to get the maximum Max is a variable used to keep the current "maximum value". Initially assume the "first" value is the maximum value. The For loop is used to walk through each value in the range of cells to match The first If statement is used to select only those rows where the match is made. The second If statement is used to update the Max value if this cell is larger than the current Max. IfMin is generated in a similar manner to read Function IfMin(Selector, Match, Value) ' ' IfMin Macro ' Macro created 5/22/2003 by Maniac ' ' Min = Value.Cells(1).Value For N = 1 To Match.Count If (Match.Cells(N).Value = Selector.Value) Then If (Min < Value.Cells(N).Value) Then Min = Value.Cells(N).Value End If End If Next N IfMin = Max End Function This basic structure can be changed to generate averages and other similar functions. For example, if you have varying amounts of data to process each day, but the columns remain the same each day it is possible to process the "whole worksheet" with a slight change. I would use something like For N = 1 to ActiveCell.CurrentRegion.Rows.Count to determine the number of rows of data and adjust the value references to process each row in the "current region" (a range bounded by blank rows / columns). If something like this is what you want - please use a clarification request. Please use a request clarification request if you have any difficulty with this answer. --Maniac``` Clarification of Answer by maniac-ga on 22 May 2003 08:52 PDT ```Hello Addisonbr, I noticed a slight error in what I provided earlier. Use the following versions instead for IfMax and IfMin. Use "recalculate now" (Ctrl-=) in Excel if needed to refresh the values in the spreadsheet if you cut / paste these into Visual Basic to replace the other copies. Thanks. --Maniac Function IfMax(Selector, Match, Value) ' ' IfMax Macro ' Macro created 5/22/2003 by Maniac ' ' Max = -100000000 For N = 1 To Match.Count If (Match.Cells(N).Value = Selector.Value) Then If (Max < Value.Cells(N).Value) Then Max = Value.Cells(N).Value End If End If Next N IfMax = Max End Function Function IfMin(Selector, Match, Value) ' ' IfMin Macro ' Macro created 5/22/2003 by Maniac ' ' Min = 100000000 For N = 1 To Match.Count If (Match.Cells(N).Value = Selector.Value) Then If (Min > Value.Cells(N).Value) Then Min = Value.Cells(N).Value End If End If Next N IfMin = Min End Function``` Request for Answer Clarification by addisonbr-ga on 28 May 2003 14:46 PDT ```Maniac - just got back in town to see your further responses. The function code and explanations are exactly what I was looking for; thank you for the update. I'd like to edit the rating for your answer if possible, but I wasn't able to figure out how to do that - if you have a suggestion please let me know.``` Clarification of Answer by maniac-ga on 29 May 2003 04:38 PDT ```Hello Addisonbr, I am glad the revised answer provided what you need. As far as I know, there is no direct way for you to change the rating. The FAQ at http://answers.google.com/answers/faq.html#ratequestion in particular states "...you cannot revise your rating. Therefore, you should only rate an answer after you have completed the "Answer Clarification" process...." It may be possible for one of the support staff to revise the rating for you. Try sending a message to answers-support@google.com or answers-editors@google.com and see what they say. --Maniac``` Request for Answer Clarification by addisonbr-ga on 29 May 2003 20:07 PDT ```To Google Answers team - I'd like to change the rating on this answer to four stars if possible, if the rating itself can't be changed please take this comment into subjective account. Maniac, thanks again.``` Clarification of Answer by maniac-ga on 30 May 2003 04:35 PDT ```Hello Addisonbr, Thank you for the kind words and good luck with your work. --Maniac```
 addisonbr-ga rated this answer: ```Unfortunately, I specifically said "I do not want an array formula solution (for a variety of reasons)"... But 2 stars instead of one because I guess if I actually did want an array formula answer, it's a good explanation.```