making a max function for the hole values NOT in the range

**Stoi** · 11-15-2009, 06:19 PM

this function should return a complete numeric value lower then the max number in range and the highist possible value MISSING from the range

for exemple:
if the numbers in the range are 1, 9, 4 and 8 the result shold be 7
7 is lower then 9 (9 is the max value in range), a hole number (i dont want to see fractions) and as high as it gets and still not be one of the numbers in the range

i do not have programing/macro abilities in my excel (atlist non that i know of)
and i need to show the 3 highist number not chosen yet (i will use it 3 time and every time add the result to the range for the next one)

i can artificially add a max value to the range in order to limit it as i like, or the function can get a max value in separate

**rylo** · 11-15-2009, 06:57 PM

Hi

Not sure how you want this to work, but here's a start.

Enter your numbers in the range A1:A4.

Put the function below in a general module

Code:

Function getmaxmissing(rng As Range)
  getmaxmissing = "No missing numbers"
  For i = WorksheetFunction.Max(rng) To WorksheetFunction.Min(rng) Step -1
    If WorksheetFunction.CountIf(rng, i) = 0 Then
      getmaxmissing = i
      Exit Function
    End If
  Next i
End Function

In a cell enter: =getmaxmissing(A1:A4)

HTH

rylo

**Stoi** · 11-15-2009, 07:50 PM

tnx

i just changed the 2nd line to:
getmaxmissing = WorksheetFunction.Min(rng) - 1
and it works butifully ^^

EDIT:

i played a little with your function and ended up with this:

Code:

Function getmaxmissing(rng As Range, Optional rank As Integer = 1)
  For i = WorksheetFunction.Max(rng) To WorksheetFunction.Min(rng) Step -1
    If WorksheetFunction.CountIf(rng, i) = 0 Then
      rank = rank - 1
      If (rank = 0) Then
        getmaxmissing = i
        Exit Function
      End If
    End If
  Next i
  getmaxmissing = WorksheetFunction.Min(rng) - rank
End Function

just in case some more people find it usefull

EDIT:

sorry about that, ive added code tags now

**mikerickson** · 11-15-2009, 08:08 PM

This CSE formula will return the number you want from the range A1:A10. If the numbers in the range might exceed 100, change the argument of INDIRECT.

=MAX(A1:A10) - MATCH(FALSE, ISNUMBER(MATCH(MAX(A1:A10)-ROW(INDIRECT("1:100")), A1:A10 ,0)), 0)

This should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

**Stoi** · 11-16-2009, 03:38 AM

Originally Posted by mikerickson

This CSE formula will return the number you want from the range A1:A10. If the numbers in the range might exceed 100, change the argument of INDIRECT.

=MAX(A1:A10) - MATCH(FALSE, ISNUMBER(MATCH(MAX(A1:A10)-ROW(INDIRECT("1:100")), A1:A10 ,0)), 0)

This should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

can you explain this plz? ive tryed it and gut #N/A, i have no idea why.

useing the VB code make my excel be a bit laggish, so i'll appreciate it if thers a better way to do it

**mikerickson** · 11-16-2009, 10:07 AM

ROW(INDIRECT("1:100")
is the array {1; 2; 3; ...; 100}
so
MAX(A1:A10)-ROW(INDIRECT("1:100"))
is the array of numbers less than MAX(A1:A10) in decending order.
Note that if N is in the descending array and its index in that array is i then i+N=MAX(A1:A10)

MATCH(MAX(A1:A10)-ROW(INDIRECT("1:100")), A1:A10,0)
is an array that returns #N/A if the descending number is not in A1:A10 and its index otherwise.

ISNUMBER(MATCH(MAX(A1:A10)-ROW(INDIRECT("1:100")), A1:A10,0) is the above array, converted in to TRUE/FALSE values, TRUE if the number is in A1:10, FALSE otherwise.

MATCH(FALSE, ISNUMBER(MATCH(MAX(A1:A10)-ROW(INDIRECT("1:100")), A1:A10,0)), 0) returns the index of the first FALSE value in that array.
That is it returns the index of the highest integer in the descending array that is not in A1:A10.

So the final formula
MAX(A1:A10) - MATCH(FALSE, ISNUMBER(MATCH(MAX(A1:A10)-ROW(INDIRECT("1:100")), A1:A10,0)), 0)
is the highest number that is both less than MAX(A1:A10) and not in A1:A10, which is the desired value.

This formula needs to be entered as an array formula. Copy paste the formula from the website into the formula bar of the spreadsheet. With the cursor in the formula editing box, press Ctrl-Shift-Enter (Cmd+Return for Mac)