MAXIF, MINIF functions
Excel doesn’t provide MAXIF and MINIF functions, but you can roll your own with array functions.
=MAX((B1:B9=”Tom”)*(A1:A9)) array entered will return 7, the max for Tom
=MIN(IF(B1:B9=”Tom”,A1:A9,”")) array entered will return 1, the minimum for Tom.
You’ll note that the MIN function is a little less straightforward. The problem with MIN and array formulas is that it almost always returns zero. For instance, if the MIN was written like the MAX, then any rows with ‘Harry’ will return zero and that will be the minimum value in the array. To get around that, an IF function is used to return the values for the name we want and an empty string for any other names. The empty string (like any string) is ignored by the MIN function.
Update: Another application - John comments
I have a column of numbers (16520, 16345, 19456, 16123, 16201, 19325, 19113, 16350, 16787, 16675) and want to use a function to find the highest number in the 16000 group. I think I need to use the MAX and IF Worksheet Functions, but I can’t get it. Any help?
Try this formula
I would think that you’d want to use an If function either way (at least potentially). I say this because, as it is written, the Max formula would not return a proper value if all of Tom’s values were negative. In that case, it would return zero.
That might not be an issue, but in cases when you’re not quite sure, it’s best to be safe.
emmmm. does it not give the highest value , i.e from -1 to - 5
max = -1
min = -5
as expected yes?
Good point Andy. The non-Tom’s would return zero and that would be the max of the whole array if Tom’s were all negative. I never do it, but it would be a good practice to start.
No, Ross. If all of Tom’s values were negative, then the Max function, as written, would return zero.
ok, i think see what your saying now, but is that true, i get a #value! on both formulas? - ahrr - i’m confused
can someone explian how these forumlas work please. see i’m thinking,
MAX((B1:B9=”Tom”) — finds the tom in the 7th row
*(A1:A9)) - - x 1(true) by the 7 in the a col same row.
[ ps. i dont know why it would do this, i'm guessing!]
hence 1x-7 = -7
Am i thick?
why dont the formulas work for me?
Ross, in order for the formulas to work, they must be entered as array formulas. In other words, press to edit the formula, and then press . The formula should now have brackets {} around it that were put there by Excel rather than by you.
In answer to your other question (why it works), we need to break the formula down differently than you did.
The formula: =MAX((B1:B9=”Dick”)*1) (again, when entered as an array formula) will return a 1, which, as you surmised, is how Excel treats the True value.
Therefore, =MAX((B1:B9=”Tom”)*(A1:A9)) will take the largest value of an instance where “Tom” is in Column B multiplied by the value that is in column A. The Max function applies after the multiplication occurs. Therefore, it is saying Max(1*1,2*0,3*0,4*1,5*0,6*0,7*1,2*0,4*0) or Max(1,0,0,4,0,0,7,0,0), which is, of course, 7.
Now you can also see why the formula, as written, does not work properly if all of Tom’s values are negative. Max (-1,0,0,-4,0,0,-7,0,0) is zero.
Brilliant Andy! Thanks very much indeed.
DOH! My message was modified because of HTML.
You need to press F2 to edit the formulas, and then press CTRL+Shift+Enter to create them as array formulas.
I had enclosed those terms in brackets <> and the html took them as tags. Oh well.
- of topic but, i can use HTML tags in maessages - if needed!! - such as
Bold etc.
useful
thanks fellas
It’s intresting that it effected the footer having not colsed the tag correctly.
Any way - sorry
I have used HTML in here successfully. What I meant was that, normally when I refer to array formulas, I put the [Ctrl][Shift][Enter] keys in the angled brackets that you use for HTML. When I did that in this post, it read them as being HTML tags. Therefore, instead of seeing “Press < .F2.>” (without the periods) it just showed up as “Press “.
This page is insanely useful!
BUT I can’t get it to work. Here’s what I have
=MAX((A:A=DATE(2004,7,12))*(M:M))
I have no idea why I would press F2, it does nothing. I did press ctrl-shift-enter to make it an array formula, but as soon as I did I got a #NUM error. Before I made it an array formula I would get the contents of each cell in M:M when the date was specified. Thanks!
Usarian M. Skiff
Alright now I’m REALLY frustrated.
I copied my query data onto a blank page and specified which rows to start and stop in.. ie.
=max((a1:A1000=F$3$)*(M:M))
it worked.
I did the same thing both ON the query page and USING the query page, and I got that #NUM error again!
UMS: Two things about array formulas that you should know. 1. You can’t use whole column references like A:A and M:M. I have no idea why they designed it like that, but they did. 2. All of the “arrays” in your formula must have the same number of rows. So
=max((a1:a1000=$f$3)*(m1:m1000))
Note that they don’t have to be the same rows (1:1000), but they have to have the same number of rows.
Great stuff!!!!!
Just a couple of points…
1. Column references did work for me (Excel 97) but …
2. Only if the array formula was on the same tab as the data. I cannot get this to work across tabs - even when I tried the syntax a1:a1000, etc.
Is there a way around this?
Brett - I don’t think that you can do it across sheets. You could try using a named range, but I’m not sure if that would work either.
UMS - [F2] lets you modify a previously entered formula. If you are typing the formula for the first time, then it would not be needed.
Its really great information
Thanks a lot
Find Maximum two values in a array
i wana to find maximum two numbers in a array through max if formula
MAXIF
COL D COL J
AGENT PROFIT
JOHN 500
JIM 400
JOHN 200
JACK 1000
JOHN 750
I’M TRYING TO DETERMINE WHAT JOHN’S HIGHEST PROFIT WAS. BUT WHEN I USED YOUR EXAMPLE
=MAX((D1:100=”JOHN”)*(J1:J100))
THE RESULT COMES BACK: 100
HOWEVER, WHEN I CLICK ON THE EQUAL SIGN TO EDIT THE FORMULA IT SAYS THE RESULT IS 750. THAT IS CORRECT BUT MY SPREADSHEET CELL DISPLAYS 100.
WHY?
THANKS
JOE NALLIE
800 835-3247
JOE.NALLIE@GMAIL.COM
When I copied your example into my Excel 2003 spreadsheet, I got errors. A few things I ensured to get a good result:
1) define numeric cells as some kind of numeric and A/N cells as general or text
2) make sure numeric entries, especially your “750″, don’t have any leading or trailing blanks, which will make them non-numeric and not evaluated in the MAX function
3) I found myself retyping “JOHN” in the formula and it was after that that the formula worked.
4) Don’t forget the ctrl+shift+enter.
I am trying to create a formula where zero is not taken into account. How would I do that? For example: if I have the numbers 4.5, 3.3, .30, 1.2, 0, 2.2
Now, even though zero is one of the answers, in my case, a zero means I do not have data for that cell. Therefore, I want all the zeros to not be accoutned for so that in this case the answer would be .30.
Any ideas?
Thanks.
=MIN(IF(rng=0,”",rng))
enter with control+shift+enter
how would I go about doing a min(if(……over about 20 or so sheets of info ?….the formula i’m trying to get to work looks like this……
=Min(if(sheet1:sheet20!b4=”text”,sheet1:sheet20!ac8)
this only returns a #REF so idk i’m stumped
Hi There,
Just thought I’d post up something that is probably obvious but might be worth mentioning:
The MAXIF equation given above i.e. ‘{=MIN(IF(B1:B9=”Tom”,A1:A9,””))}’, works fine when the ranges are specified, in this case the condition ranges from ‘B1′ to ‘B9′ and the values range from ‘A1′ to ‘A9′.
However, if you wish to do the MAXIF on an entire column you cannot use ‘B:B’ and ‘A:A’ as the function will not work. To overcome this you can simply put in the range as 1 to the max number of rows a spreadsheet can contain (65536) minus 1. You need to subtract one to prevent excel automatically replacing something like ‘A1:A65535′ with ‘A:A’.
Also, you can overcome the zero problem in the MAXIF and MINIF functions by putting in the ‘else’ argument into the IF statement. For the MAXIF function this should be the lowest number possible in the spreadsheet e.g. -999999999999999999999, and for MINIF it should be the highest number possible.
Here are some examples:
MAXIF: ‘{=MAX(IF(A2:A$65535=”A”,B2:B$65535,-999999999999999999999))}’
MINIF: ‘{=MIN(IF(A2:A$65535=”A”,B2:B$65535,999999999999999999999))}’
This page has saved my voluntary work project for a charity. MASSIVE thanks to all the contributors!
Scanning through all this, I do not quite see the function I need - i.e.,
I need to find the lowest not-zero numeric value in a non-contiguous array of cells all containing numbers or zero values.
Have I missed it. I can’t seem to see my way through to the solution although this MIN IF format seems likely to lead to it.
E.G., each of the cells to be included contains a formula resulting in a number.
E2 result is 1, G2 result is 1, I2 result is zero, K2 result is zero, M2 result is zero, O2 result is .67
In cell P2, I want the function to return the lowest non-zero value in the range - i.e., .67 from cell O2.
Cheers, woggie
Paul: =MIN(IF(E2:O2<>0,E2:O2,”")) array entered (control+shift+enter)
Dick, can this be used with multiple conditions? I tried using the “AND” Function after the “IF” which did not work.
my formula:
=MIN(IF(AND(B2:B313=824,E2:E313=”MOT_HSDPA”),AO2:AO313,”"))
Any ideas?
Die_Another_Day
Well I kluged a nested if statement to make it work…
=MIN(IF(B2:B313=824,IF(E2:E313=”MOT_HSDPA”,AO2:AO313,”"),”"))
Any better ideas?
Die_Another_Day
I need to take this MAXIF a step farther…
Given the example at the top of the page where the max is calculated for “Tom”,
I need to add formula in column C (for each row) that will state that that particular row is the max for that particular person (it will say “Personal Max”). the other rows for column C will be blank.
So the results would have the string “Personal Max” in column C on rows 5,6,7 since they are the highest entries for those people.
I can do it with two formula columns now (one of the maxif array function, the next checks if the number equals the max for that entry). How do I do it in one column? I dont know how to combine my if statement with my array formula.
Chris
Chris:
Array entered in C1 and copied down:
=IF(A1=MAX(IF(B$1:B$9=B1,A$1:A$9)),”Personal Max”,”")
A non-array alternative to the “MAXIF” function:
=SUMPRODUCT(MAX((B1:B9=”Tom”)*A1:A9))
Unfortunately, this method will not work for a “MINIF” if all the values are positive.
Biff
thanks andy. this helped a lot.
{=IF(B1=MAX($B$1:$B$18*(($A$1:$A$18)=A1)),B1,”")}
i have an array formula entered in a particular column. where there is no adjacent data for the array to calculate, the result is an error #N/A. i normally use an IF statement to “hide” any such errors, such that, as an example only, “If(C4=”",”",c4*2)”.
I have tried to combine this style with an array formula and it doesn’t work: (entered with CSE)
“=IF((VLOOKUP(S79,TRIM(CodeFlags),2,0))=”",”",(VLOOKUP(S79,TRIM(CodeFlags),2,0)))”
Has anybody got a fix so that my users do not get errors on their reports?
ajm: VLOOKUP won’t return “” if there’s an error. Try ISNA(VLOOKUP(…))
I once used for non-array MAXIF the following (I call it “Inceremnetal MAXIF”)based on the exampe above:
1. On Cell C1 type =A1
2. On Cell C2 type =MAX(0,A2-SUMIF(B$1:B2,B2,C$1:C2)) and drag on column C
3. On cell D1 type =SUMIF(B:B,B1,C:C) and drag on column D
This will not work on MINIF or MAXIF with negative MAX
There seems to be a bug with certain nested array formulas. To see this, type the following array formula {max(cell(”row”,a1:a4))}. Excel will return 1, rather than 4. Maybe not a bug so much as a distinction in exactly when excel iterates on the array.
Robert,
According to Help the Cell function will return the info for only the top-left cell in a range.
Robert: The Cell function won’t return an array, so it’s only returning the first value.
This is much simpler in a Pivot table with one column Value set for Max and another set for Min. Where row labels Column A.
I’ve been trying to create a ‘Max-If’ formula which would find would find the maximum of a range & return the value of a cell in a separate range.
Num. Let.
12 A
17 B
12 C
22 D
33 E
18 F
16 G
Find the Max of A1:A7 [Num.] which is less than 30, and return the cell in matching row B1:B7 [Let.]
e.g. {=MAX(IF(A1:A7>30,B1:B7,”Not Found”))} or {=IF(MAX(A1:A7>30),B1:B7,”Not Found”))}
but they do not work.
The less-than or greater-than seems to present some issue vs. the equal.
Any help is greatly appreciated.
Sam F
Brilliant, thanks! Saved me ages
Hello, thanks for all this, but is there any way of modifying the original example, so that it will pick cells which have Tom in them, e.g. Tommy, Tomas etc. I tried using the wild card asterisk but it doen’t seem to work.
ANdrew,
Is this array formula what you mean?
=MAX(IF(LEFT(A1:A10,3)=”Tom”,B1:B10))
Thanks Bob, but no, because I didn’t make myself completely clear. I am looking for something that will pick up anything CONTAINING Tom, such as Tommy, but also bottom, 23tomfred, andrewtombob etc etc.
So just use
=MAX(IF(ISNUMBER(SEARCH(”Tom”,A1:A10)),B1:B10))
Great those matrixformulas, when you get to know them.
Thank you all.
Regards, Peter.
Bob, this is basically what I was looking for. I am most grateful, it helps a lot. Just one point - the MAX is fine, but the MIN seems to give zero if there are blank entries. Is there any way round this, for example with an extra IF?
Sorry for the late response ANdrew.
You need to add another condition to outsort the blanks
=MIN(IF((A1:A10″”)*(ISNUMBER(SEARCH(”Tom”,A1:A10))),B1:B10))
If you need to exclude blank cells, you could still use one test.
=MIN(IF(A1:A10<>SUBSTITUTE(A1:A10,”Tom”,”"),B1:B10))
But I don’t see why Bob added a (A1:A10<>”") test. I doubt it would fix Andrew’s problem because I suspect Andrew’s problem is that no record satisfies the criteria, so his formula is equivalent to MIN({FALSE;FALSE;…;FALSE}) for which Excel returns 0. Alternatively, Andrew may have blank cells in his ‘B1:B10′ range, in which case Bob’s second formula does nothing useful. If that’s the case, then try the array formula
=MIN(IF(ISNUMBER(SEARCH(”Tom”,A1:A10)/(B1:B10<>”")),B1:B10))
Many thanks, fzz. I will try the various suggestions and see which fits best to my particular set of data. It’s good to have alternatives.
Hi,
I’m new here so if I’m out of order: sorry.
MAXIF and MINIF sound like PERCENTILEIF (in Dutch PERCENTIELALS). And that’s what I programmed some years ago in VBA. The idea was simple: calculate a percentile score of a population but only if a value would meet some criterium. It took many lines to do the trick (see below).
A working sample can be found on my site, page Example Files, the Excel part. Download Macros.xls and go to sheet VIII, row 85.
Working on this function made me wonder how functions like DSUM etc. can have such complex criteria. Realy amazing.
Kind regards to all, Frans van Zelm
— The code —
'Bereik verwijst naar twee aansluitende(!) kolommen / rijen met criteriumwaarden en rekenwaarden
'Criterum is (cel met) te gebruiken criterium; Variant want kan bereik zijn of waarde of tekst of ...
'Percentage is (cel met) te gebruiken percentage; Variant want kan bereik zijn of waarde
'Functie berekent percentiel van waarden die aan criterium voldoen
'Functie gebruikt door: -
'Functie gebruikt: - WorksheetFunction.Percentile
'Opmerkingen: -
'Nog doen: - identieke functies voor Mediaan e.a. statistische functies
' - meerdere kolommem met waarden, vgl. Som.Als
' - verbeteren zodat functie werkt vgl. DGemiddelde met criteriumbereik
' - kolommen met criteriumwaarden en te berekenen waarden gesplitst
' (dan controleren op gelijkvormigheid bereiken)
Dim myMatrix() As Double 'Matrixvar (onbepaald) voor te berekenen matrix
Dim myBereikTeller As Integer 'Var voor lengte (rijen / kolommen) in bronbereik
Dim myMatrixTeller As Integer 'Var voor lengte (aantal waarden) in te bereken matrix
Dim myCriteriumWaarde As Variant 'Var voor criteriumwaarde; Variant want getal of tekst
If Bereik.Rows.Count >= Bereik.Columns.Count Then
'Als verticaal bereik
For myBereikTeller = 0 To Bereik.Rows.Count - 1
'Voor alle rijen in bronbereik
' Tellen vanaf 0 tot x-1 vanwege matrixvar
If Left(Criterium.Value, 2) = ">=" Then 'Test of criterium begint met vgl.operator >=
If IsNumeric(Right(Criterium, Len(Criterium) - 2)) Then
'Als criteriumwaarde zonder >= is numeriek
myCriteriumWaarde = CDbl(Right(Criterium, Len(Criterium) - 2))
'Criteriumwaarde converteren naar Double
Else 'Anders
myCriteriumWaarde = Right(Criterium, Len(Criterium) - 2)
End If 'Criteriumwaarde initialiseren zonder >=
If Bereik(myBereikTeller + 1, 1).Value >= myCriteriumWaarde _
And (Not IsEmpty(Bereik(myBereikTeller + 1, 2).Value) _
And IsNumeric(Bereik(myBereikTeller + 1, 2).Value)) Then
'Als waarde in eerste kolom >= criterium
' en (tweede kolom niet-leeg en getal)
ReDim Preserve myMatrix(myMatrixTeller) 'Te berekenen matrix vergroten
myMatrix(myMatrixTeller) = Bereik(myBereikTeller + 1, 2).Value
'n-de waarde naar n-de element van te berekenen matrix
myMatrixTeller = myMatrixTeller + 1 'Teller aantal waarden in te berekenen matrix verhogen
End If
ElseIf Left(Criterium, 2) = "<=" Then
If IsNumeric(Right(Criterium, Len(Criterium) - 2)) Then
myCriteriumWaarde = CDbl(Right(Criterium, Len(Criterium) - 2))
Else
myCriteriumWaarde = Right(Criterium, Len(Criterium) - 2)
End If
If Bereik(myBereikTeller + 1, 1).Value <= myCriteriumWaarde _
And (Not IsEmpty(Bereik(myBereikTeller + 1, 2).Value) _
And IsNumeric(Bereik(myBereikTeller + 1, 2).Value)) Then
ReDim Preserve myMatrix(myMatrixTeller)
myMatrix(myMatrixTeller) = Bereik(myBereikTeller + 1, 2).Value
myMatrixTeller = myMatrixTeller + 1
End If
ElseIf Left(Criterium, 2) = "" Then
If IsNumeric(Right(Criterium, Len(Criterium) - 2)) Then
myCriteriumWaarde = CDbl(Right(Criterium, Len(Criterium) - 2))
Else
myCriteriumWaarde = Right(Criterium, Len(Criterium) - 2)
End If
If Bereik(myBereikTeller + 1, 1).Value myCriteriumWaarde _
And (Not IsEmpty(Bereik(myBereikTeller + 1, 2).Value) _
And IsNumeric(Bereik(myBereikTeller + 1, 2).Value)) Then
ReDim Preserve myMatrix(myMatrixTeller)
myMatrix(myMatrixTeller) = Bereik(myBereikTeller + 1, 2).Value
myMatrixTeller = myMatrixTeller + 1
End If
ElseIf Left(Criterium, 1) = ">" Then
If IsNumeric(Right(Criterium, Len(Criterium) - 1)) Then
myCriteriumWaarde = CDbl(Right(Criterium, Len(Criterium) - 1))
Else
myCriteriumWaarde = Right(Criterium, Len(Criterium) - 1)
End If
If Bereik(myBereikTeller + 1, 1).Value myCriteriumWaarde _
And (Not IsEmpty(Bereik(myBereikTeller + 1, 2).Value) _
And IsNumeric(Bereik(myBereikTeller + 1, 2).Value)) Then
ReDim Preserve myMatrix(myMatrixTeller)
myMatrix(myMatrixTeller) = Bereik(myBereikTeller + 1, 2).Value
myMatrixTeller = myMatrixTeller + 1
End If
ElseIf Left(Criterium, 1) = "<" Then
If IsNumeric(Right(Criterium, Len(Criterium) - 1)) Then
myCriteriumWaarde = CDbl(Right(Criterium, Len(Criterium) - 1))
Else
myCriteriumWaarde = Right(Criterium, Len(Criterium) - 1)
End If
If Bereik(myBereikTeller + 1, 1).Value ="" Then
If IsNumeric(Right(Criterium, Len(Criterium) - 2)) Then
myCriteriumWaarde = CDbl(Right(Criterium, Len(Criterium) - 2))
Else
myCriteriumWaarde = Right(Criterium, Len(Criterium) - 2)
End If
If Bereik(1, myBereikTeller + 1).Value >= myCriteriumWaarde _
And (Not IsEmpty(Bereik(2, myBereikTeller + 1).Value) _
And IsNumeric(Bereik(2, myBereikTeller + 1).Value)) Then
ReDim Preserve myMatrix(myMatrixTeller)
myMatrix(myMatrixTeller) = Bereik(2, myBereikTeller + 1).Value
myMatrixTeller = myMatrixTeller + 1
End If
ElseIf Left(Criterium, 2) = "<=" Then
If IsNumeric(Right(Criterium, Len(Criterium) - 2)) Then
myCriteriumWaarde = CDbl(Right(Criterium, Len(Criterium) - 2))
Else
myCriteriumWaarde = Right(Criterium, Len(Criterium) - 2)
End If
If Bereik(1, myBereikTeller + 1).Value <= myCriteriumWaarde _
And (Not IsEmpty(Bereik(2, myBereikTeller + 1).Value) _
And IsNumeric(Bereik(2, myBereikTeller + 1).Value)) Then
ReDim Preserve myMatrix(myMatrixTeller)
myMatrix(myMatrixTeller) = Bereik(2, myBereikTeller + 1).Value
myMatrixTeller = myMatrixTeller + 1
End If
ElseIf Left(Criterium, 2) = "" Then
If IsNumeric(Right(Criterium, Len(Criterium) - 2)) Then
myCriteriumWaarde = CDbl(Right(Criterium, Len(Criterium) - 2))
Else
myCriteriumWaarde = Right(Criterium, Len(Criterium) - 2)
End If
If Bereik(1, myBereikTeller + 1).Value myCriteriumWaarde _
And (Not IsEmpty(Bereik(2, myBereikTeller + 1).Value) _
And IsNumeric(Bereik(2, myBereikTeller + 1).Value)) Then
ReDim Preserve myMatrix(myMatrixTeller)
myMatrix(myMatrixTeller) = Bereik(2, myBereikTeller + 1).Value
myMatrixTeller = myMatrixTeller + 1
End If
ElseIf Left(Criterium, 1) = ">" Then
If IsNumeric(Right(Criterium, Len(Criterium) - 1)) Then
myCriteriumWaarde = CDbl(Right(Criterium, Len(Criterium) - 1))
Else
myCriteriumWaarde = Right(Criterium, Len(Criterium) - 1)
End If
If Bereik(1, myBereikTeller + 1).Value myCriteriumWaarde _
And (Not IsEmpty(Bereik(2, myBereikTeller + 1).Value) _
And IsNumeric(Bereik(2, myBereikTeller + 1).Value)) Then
ReDim Preserve myMatrix(myMatrixTeller)
myMatrix(myMatrixTeller) = Bereik(2, myBereikTeller + 1).Value
myMatrixTeller = myMatrixTeller + 1
End If
ElseIf Left(Criterium, 1) = "<" Then
If IsNumeric(Right(Criterium, Len(Criterium) - 1)) Then
myCriteriumWaarde = CDbl(Right(Criterium, Len(Criterium) - 1))
Else
myCriteriumWaarde = Right(Criterium, Len(Criterium) - 1)
End If
If Bereik(1, myBereikTeller + 1).Value < myCriteriumWaarde _
And (Not IsEmpty(Bereik(2, myBereikTeller + 1).Value) _
And IsNumeric(Bereik(2, myBereikTeller + 1).Value)) Then
ReDim Preserve myMatrix(myMatrixTeller)
myMatrix(myMatrixTeller) = Bereik(2, myBereikTeller + 1).Value
myMatrixTeller = myMatrixTeller + 1
End If
ElseIf Left(Criterium, 1) = "=" Then
If IsNumeric(Right(Criterium, Len(Criterium) - 1)) Then
myCriteriumWaarde = CDbl(Right(Criterium, Len(Criterium) - 1))
Else
myCriteriumWaarde = Right(Criterium, Len(Criterium) - 1)
End If
If Bereik(1, myBereikTeller + 1).Value = myCriteriumWaarde _
And (Not IsEmpty(Bereik(2, myBereikTeller + 1).Value) _
And IsNumeric(Bereik(2, myBereikTeller + 1).Value)) Then
ReDim Preserve myMatrix(myMatrixTeller)
myMatrix(myMatrixTeller) = Bereik(2, myBereikTeller + 1).Value
myMatrixTeller = myMatrixTeller + 1
End If
Else
myCriteriumWaarde = Criterium
If Bereik(1, myBereikTeller + 1).Value = myCriteriumWaarde _
And (Not IsEmpty(Bereik(2, myBereikTeller + 1).Value) _
And IsNumeric(Bereik(2, myBereikTeller + 1).Value)) Then
ReDim Preserve myMatrix(myMatrixTeller)
myMatrix(myMatrixTeller) = Bereik(2, myBereikTeller + 1).Value
myMatrixTeller = myMatrixTeller + 1
End If
End If
Next
End If
PercentielAls = WorksheetFunction.Percentile(myMatrix, Percentage)
'Eindwaarde = percentiel van matrix
End Function