Ranking with duplication and gaps
 

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?

Ranking with duplication and gaps
 

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" wrote in message
...
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?

Ranking with duplication and gaps
 

Bernie,

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

Goody

"Bernie Deitrick" wrote:

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" wrote in message
...
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?

Ranking with duplication and gaps
 

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;FALS E}

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" wrote in message
...
Bernie,

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

Goody

"Bernie Deitrick" wrote:

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" wrote in message
...
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?

Ranking with duplication and gaps
 

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" wrote:

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;FALS E}

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" wrote in message
...
Bernie,

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

Goody

"Bernie Deitrick" wrote:

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" wrote in message
...
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?

Ranking with duplication and gaps
 

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


"Goody" wrote in message
...
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" wrote:

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;FALS E}

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" wrote in message
...
Bernie,

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

Goody

"Bernie Deitrick" wrote:

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" wrote in message
...
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?