# Using the LARGE function to adjust the ith largest values

#### kgbrown

##### New Member
Hi everyone,

Excuse the crude post, I am new to VBA (and forums)!
I am trying to write a program to examine the values in a worksheet column and adjust any values that are larger than a specific criterion value (that is also in the sheet). They have to be adjusted in order so that the highest value is still the highest and the second highest is still the second highest etc.

Just as a rough example, with the following set of numbers, I would like the code to adjust the values 57 and 60 because they are above the criterion value of 8. 57 Needs to be adjusted to the next highest value (3) and then 60 needs to be adjusted to the value above that (4).

1
2
2
1
2
57
60

8

I thought the best way to do this was to create a variable to count how many values are above the criterion and use this in conjunction with the LARGE function. This way, each value can be selected and adjusted in reference to the next highest value accordingly. The code is as follows...

Code:
``````Sub Adjust()

Dim c As Range
Dim Cell1 As Range
Dim Cell2 As Range
Dim rng As Range
Dim irow As Integer
Dim icol As Integer

Dim i As Double
Dim j As Variant
Dim k As Variant
Dim l As Variant
Dim m As Double
Dim n As Double
Dim o As Double
Dim p As Double
Dim q As Double

For icol = 1 To 3
m = 1

' Loops through the cells in the column and counts how many are above the criterion value
For irow = 1 To 14
j = irow + 30

Set curCell = Worksheets("Sheet1").Cells(irow, icol)
If Abs(curCell.Value) > Cells(irow, 20).Value Then

m = m + 1

End If

Next irow

' j = a constant so that the values can be pasted below.
'o = a constant  for the large function, allowing the program to determine the next value in the list. It adds 1 to m for use
' with the large function.
'q = The large function, which returns the ith largest value. So if the the value that needs to be adjusted is the third highest in the column,
'       this selects the fourth highest and adds 1.

For irow = 1 To 14

Set Cell1 = Cells(irow, icol)
Set Cell2 = Cells(14, icol)
Set rng = Range(Cell1, Cell2)

j = irow + 30
o = m + 1
p = Application.WorksheetFunction.Large(rng, m)
q = Application.WorksheetFunction.Large(rng, o) + 1

'This is where each cell is selected in turn. If it is the mth largest value, then p is selected (which is+1 above the next highest value.
' If not, the current cell value is selected. These are then pasted into row 30 onwards, using the constant j.
Set curCell = Worksheets("Sheet1").Cells(irow, icol)

If Abs(curCell.Value) = p Then Worksheets("Sheet1").Cells(j, icol).Value = p

Else: Worksheets("Sheet1").Cells(j, icol).Value = (curCell.Value)

End If

m = m - 1

Next irow

Next icol

End Sub``````

There are a couple of issues with this code however.
Firstly, the LARGE function doesn't seem to like having a variable as its second argument. Also, once the if statement detects one of the high values, the alteration applies to all of the following values even if they are not above the criterion (i.e. every value is adjusted once one of the higher values has been encountered).

I hope this makes sense I would really appreciate some help/suggestions of a better way to accomplish the task.

Kyle

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### pgc01

##### MrExcel MVP
Hi Kyle
Welcome to the board

Try, in B1:

=IF(A1<\$A\$9,A1,MAX(IF(\$A\$1:\$A\$7<\$A\$9,\$A\$1:\$A\$7))+SUMPRODUCT(--(\$A\$1:\$A\$7>\$A\$9),--(\$A\$1:\$A\$7<A1))+1)

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

#### kgbrown

##### New Member
That's brilliant!

Thank you very much.
Sorry if its a little cheeky, but sorry I have another question.

Would it be possible to do the same thing but with the a minimum value criterion within the same formula? I should be able to modify it myself to do it for the minimum value separately, but I thought I could quickly ask if it is at all possible to do both at once.

Thanks again, I really appreciate your help,
kgbrown

#### pgc01

##### MrExcel MVP
Hi

I used the same logic for the lower numbers. I hope it's what you have in mind.

With the lower bound in A16 and the upper bound in A17, try in B1:

=IF(AND(A1<=\$A\$17,A1>=\$A\$16),A1,IF(A1>\$A\$17,MAX(IF(\$A\$1:\$A\$12<\$A\$17,\$A\$1:\$A\$12))+SUMPRODUCT(--(\$A\$1:\$A\$12>\$A\$17),--(\$A\$1:\$A\$12<A1))+1,MIN(IF(\$A\$1:\$A\$12>\$A\$16,\$A\$1:\$A\$12))-SUMPRODUCT(--(\$A\$1:\$A\$12<\$A\$16),--(\$A\$1:\$A\$12>A1))-1))

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

#### kgbrown

##### New Member
Thanks so much for your help so far, but the second formula doesn't seem to work. I pasted it in and there was an error (because a "<" is missing), but when I corrected this (and used the numbers you gave as an example), some of the cells are returning VALUE! errors and others are returning FALSE.

Sorry to keep bothering you with this, but you wouldn't believe how long it has been holding me up.

#### pgc01

##### MrExcel MVP
Hi

Sorry, the html ate part of the formula. Try in B1, as array formula:

=IF(AND(A1<=\$A\$17,A1>=\$A\$16),A1,IF(A1>\$A\$17,MAX(IF(\$A\$1:\$A\$12<\$A\$17,\$A\$1:\$A\$12))+SUMPRODUCT(--(\$A\$1:\$A\$12>\$A\$17),--(\$A\$1:\$A\$12<A1))+1,MIN(IF(\$A\$1:\$A\$12>\$A\$16,\$A\$1:\$A\$12))-SUMPRODUCT(--(\$A\$1:\$A\$12<\$A\$16),--(\$A\$1:\$A\$12>A1))-1))