Ranking with duplication and gaps

[Goody]

I have a column of number that are in an irregular sequence (e.g., 1, 1, 3,
6, 1, 7, 7, 7, 9). I need a formula that will determine the highest value in
the column that is less than the value in a given cell in that column. For
example, if I select a cell with the number 6 in it, I need to know what is
the highest number less than 6. I have tried RANK and LARGE, but I can't
figure out how to make them work. Any suggestions?

 

[Bernie Deitrick]

Goody,

Array enter (Enter using Ctrl-Shift-Enter) the formula

=MAX((A2:A100<B3)*A2:A100)

Where A2:A100 have your numbers, and B3 has the number of interest.....

HTH,
Bernie
MS Excel MVP

 

[Goody]

Bernie,

I can see that your solution works, but I don't understand the logic of it.
How does it work?

Goody

 

[Bernie Deitrick]

Goody,

An array formula processes the arrays within its arguments.

Let's look at a case where B3 = 5, and the range is shorter (A2:A10) and has the numbers 2 to 10 in
it.

The first part returns an array of True and False Values, depending on if A2:A10 is less than B5:

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

The second part returns the values in A2 to A10

{2;3;4;5;6;7;8;9;10}

The two arrays are multiplied together

{2;3;4;0;0;0;0;0;0}

and MAX returns the maximum value - 4, in this example.

HTH,
Bernie
MS Excel MVP

 

[Goody]

Bernie,

I have decided to implement this solution in code. I managed to figure out
how to enter the formula as an array using FormulaArray in the macro code,
but now I would like to come up with the same answers without using the
spreadsheet cells. In other words, I would like to arrive at the answer in
the macro, use the answer in a calculation, and store the result in the
spreadsheet. How do I perform the array multiplication in the macro code?

Thanks for your help.
Goody

 

[Bernie Deitrick]

Goody,

You have lots of options.

1) Put the formula in the cell then convert the value
mCell.FormulaArray = "=MAX((A2:A100<B3)*A2:A100)"
Application.CalculateFull
mCell.Value = mCell.Value

2) Use Application.Evaluate
Dim myMax As Long
myMax = Application.Evaluate("MAX((A2:A100<B3)*A2:A100)")
myCell.Value = myMax

3) Step through the cell values... probably the worst option.... etc. etc.



HTH,
Bernie
MS Excel MVP