Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplication | Excel Discussion (Misc queries) | |||
Transpose Column With Gaps to Column With no gaps | Excel Discussion (Misc queries) | |||
Duplication | Excel Discussion (Misc queries) | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
Transpose Column With Gaps to Row With No Gaps? | Excel Discussion (Misc queries) |