The SUMIF and AVERAGEIF are really helpful functions in excel. But sometimes you may want to show the maximum or the minimum value in a certain range with a MAXIF or a MINIF function. For example, you may want to show the minimum DSCR or the maximum interest rate only over the period after the plant is operational for some periods. You can write a MAXIF and a MINIF very quickly as shown below. I have uploaded a little file that demonstrates how it works. You can download the file by clicking on the button below. I have included this on the website even though it is really easy because I sometimes forget how it works.
The simple code for the MAXIF and MINIF functions is shown below. All you do is make a loop around the array and use the worksheetfunction for finding the MAX or the MIN. You have to initialise the amount in the function and name the result the same name as the function. Note that the Option Base 1 is used because I use loops that begin with 1. For the loop I also use the length of the array that is read in.
Option Base 1 Function maxif(test, values) maxif = -1E+28 For i = 1 To test.Count If test(i) Then maxif = WorksheetFunction.Max(values(i), maxif) End If Next i End Function Function minif(test, values) minif = 1E+28 For i = 1 To test.Count If test(i) Then minif = WorksheetFunction.Min(values(i), minif) End If Next i End Function