Ranking with duplication and gaps

  • Thread starter Thread starter Goody
  • Start date Start date
G

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?
 
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
 
Bernie,

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

Goody
 
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
 
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
 
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
 
Back
Top