Tracker
HOME    TRACKER    Excel

# Getting The Second Highest Value From An Array

## Is there a way to get the second highest value from an array in vba?

Related Forum Messages:
Quick Max :: Possible To Show The Next Highest Rather Than The Highest
If I use =MAX(A1:G1) I get the highest value. Is it possible to show the next highest rather than the highest and if so how?

Converting 3x10 Array To A 1X30 Array To Run A Match Formula
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.

Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?

Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.

And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.

I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.

String Array Values To Array Of User-Defined Types
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:

Filter An Array (the Longer One) Using The Shorter Array As The Criteria
I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method

IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE")

I then copy and paste 'values' and filter out the 'false' to get my final result.

This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.

This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.

First Array Remain Same,second Array Always 11 Cell Added
=CORREL(C1:C10,C12:C21) at H1
=CORREL(C1:C10,C23:C32) at H2
=CORREL(C1:C10,C34:C43) at H3
etc

can i have a macro that first array remain the same, and second array always 11 cells added. drag it down also can

Multiply Each Element In A 6x6 Array By A Similar 6x6 Array
I've tried to multiply each element in a 6x6 array by a similar 6x6 array, both on the same sheet, and it worked.(see Macro2 and attached xls file "Test").Then I got more ambitious and tried to do the multiplication from a standard array in sheet "TestA", with the result on the same sheet, by each array in sheet "TestB" and failed.How do I solve this problem? Pgualb PS:I'm using the R1C1 style.

Sub Macro2()
For y = 29 To 34
For x = 2 To 7
Cells(x, y) = Cells(x, y - 27) * Cells(x, y - 18)
Next x
Next y
End Sub
Sub Teste12()
'Multiplica matriz em TestB por matriz padrão em TestA com _
'resultado na matriz em TestA correspondente à matriz em TestB
'
Dim x, y As Integer
For y = 2 To 7.............

Getting Value From Next-highest 'header' Row
I have a worksheet that contains a number of rows. [Thank you, Captain Obvious!] These rows are logically grouped to represent a series of "items", with each item having one or more rows (with a "header" row at the top). The rows for a particular item represent different types of information about that item, and therefore make use of different formulas based on the row type. Due to the relative complexity of the formulas in these rows, I am creating a series of row "templates" from which I can copy and paste every time I want to create a new item or add rows to an item. As such, I am trying to avoid direct cell references to different rows, as these would easily get messed up during cut and paste. Instead, I am trying to limit myself to various lookup functions (e.g. OFFSET, MATCH, etc...) that will work regardless of where a row is added.

In order to accomplish this I am using a "key" column to group all rows for a particular item together. Until now I have had to type in the key value for every row for a particular item. What I would like to do is to only have to type the key value in the header row, and have a formula populate the key value in all subsequent rows ... until a new header row is reached, at which point the new key value will be used. Take a look at the following example: ....

Highest And Lowest Value
I'd like to record the highest / lowest value in a single cell without it being written over i.e record the highest value and if there is another value lower it wont overwrite it.

I've tried using the =max or =min but whenever a newer value appears in the cell it just follows that without keeping the higher value?

Highest Time
I have 2 columns of times (military) I need to highlite the highest time. Can this be
done with conditional formatting and how can I do that?

Lookup Next Highest Value
I have an excel work book that has distances in column A from A2:A10 like so:

ABCD
15013401400
240
3900
4800
5600
61400
71200
81100
91150
101600

I would like to compare the value inputed into cell C2 against the distances and return the exact match or next highest distance to D2 if there is no exact match.

i.e. If 1340 is put in Cell C2 I would like excel to return 1400 to cell D2 as 1400 is next highest distance from 1340.

Here is the kicker, the distances cannot be sorted and I would prefer not to use VBA but I will if i have to.

Highest Permutation Value
This is for an online sports management game I play. Basically I have calculated each players skills in each position, and I need to find the best combination of the players on the field to give me the best team.

I have a table as below, and I need to find a solution that gives me what the best combination will be....

Highest Letter In A Row
I'm trying to list the latest revision of a drawing in an issue sheet spreadsheet. What I need is to be able to enter a formula that will find the highest alphanumerical value in the columns beyond. Alphabetic characters take precedence over numerical - i.e., drawing issue numbers usually follow a 00, 01, 02, 03, then switch to A, B, C, D,... order.

To try and illustrate what I mean a little better, I have the following table, with dates in DD/MM format. Sorry it's not laid out too well, my HTML isn't great:

Drawing title latest revision 01/01 02/01 03/01 04/01 05/01 06/01
example010001AB
example02000102
example0300

I want the cells in the "latest revision" column to report the highest value. So, I want it to say this:

Drawing title latest revision 01/01 02/01 03/01 04/01 05/01 06/01
example01B0001AB
example0202000102
example030000

I won't have control over the numbers and letters reached (i.e., the drawing revision could get up to Z or something - or the number revision reach 15 before switching to alphabetic revisions) so I'd rather avoid having to set up a crazy IF function with "00", "01", "02", "03", "04", etc etc. But -

Look Up Highest Scores
What is the best way to look up and return the 10 best performers out of a list of say, 40. I have columns representing people's names, their school's name, their age and their total score. The following formula is to add up the 4 highest scores for each school: SUM(LARGE((\$A\$2:\$A\$499=Y2)*(\$C\$2:\$C\$499),{1,2,3,4})) (entered as an array) Now I want to display the 10 best performing schools (who will be determined by calculating their 4 best scores).

Find Highest Value For Each Day
I have a massive amount of data made up of values taken every five minutes over several months. I want to find the highest value in a day for every day. I'm wondering if the best method is to define each day as a dynamic range and then use the max value command to get the highest value?

Retaining Highest Value
I am starting to use formulas and I am constantly amazed at the power of Excel. I am trying to write a spreadsheet and I'm having a problem getting Excel to do what I want; I have 2 column, each cell in Column 1 will have a numeric value changed daily. I want Column 2 to retain the highest value that has been added. i.e. If I type 4 in A1,(for example), on Monday, then B2 will read 4, if I type 5 in A1 the next day, then B2 will change to 5, but if I type a value less than 5 in A1, then I still want B2 to read 5. The value will be input daily for an indefinite period and needs to be user friendly. I can't work out how to achieve this.

Formatting The Highest Number In A Row
I have a few rows in an excel sheet and I want to highlight the highest number(s) in each row.

I've been playing around with conditional formatting, and using the formula:
=\$C23=MAX(\$C\$23:\$C\$55)
in the conditional formatting box and selecting a formatting type, I can automatically highlight the highest number in column C (row 23 to 55). However, this formula only seems to work when I'm selecting columns of data rather than rows. If I want to highlight the highest number in row 23, the formula I'm using is:
=\$C23=MAX(\$C\$23:\$M\$23)
Which I think should highlight the highest number From C23 to M23, but it doesn't - nothing happens.

What am I doing wrong?

How Do I Look At 3 Cells & Add Together The Highest & Lowest Value?
Could any of you Excel bods please help me find the correct formula to enter in order to calculate the following reasonably simple sum:

3 cells with numbers, say, 1, 3 & 7.

I simply need to get my worksheet to look at all three cells and then calculate the result of adding the biggest and smallest number together. i.e. 8 in the example given.

Highest Value In A Column Using Set Criteria
I am tying to find a way that I can get the highest value in a column of data depending on a set criteria, I have used the following =MAX(IF(\$C2:\$C\$109=\$C2,IF(CJ\$1=K\$1,IF(\$D2:\$D\$109=\$D2,K\$2:K\$109,0)))) and it works for the first row, I can drag it across ok, but if I drag it down it just populates the same value as the cell the formula started in even though the IF logical test has changed??

Also is it possible (say the above worked) to add to the formula so that it returns a value from another cell in the same row that it found the highest value? e.g. =MAX(IF(\$C2:\$C\$109=\$C2,IF(CJ\$1=K\$1,IF(\$D2:\$D\$109=\$D2,K\$2:K\$109,0)))) say this returned the value and it was found in K52 it would return me the value in B52

Highlight 2nd Highest Number
I'm using this CF to show me the largest number in a range;

=F10=MAX(F\$10:F\$33)

I now want to also show the second largest number in the same range.

Lookup Second Highest Number
I have a table of information which is sorted in ascending order. I have created a vlookup to find the highest value as close to 1 as possible.

Say it returns the value 0.95.

i now have another cell which i need to find the second highest value from the column of data.

however i dont know how to do it. because the second vlookup function just returns the 0.95 all the time and not the second highest value..

E.G.

Column A
0.70
0.75
0.81
0.84
0.87
0.90
0.95

So in the cell with the first Vlookup i get the value 0.95 returned.

I need then for the second cell to return the next highest value in this case 0.90.

Then third cell third highest

fourth cell fourth highest value etc.

Conditional Format For 2nd And 3rd Highest Value
I am trying to use conditional formatting to highlight the 2nd and 3rd highest value in a given column.

Example:
Step John Sam Hugh
1 3 4 5
2 9 10 23
3 12 11 43

Based on the above, for step 1, Sam has the 2nd highest value and John has the 3rd highest value.

Is there a way to use conditional formatting to highlight this ?

Display Name Of Highest Score
I'm setting up a scoring system for my rounds of golf. I have four people competing, and i want to be able to put up a page where i can display the best golfer. I have the scores and i want a function that display f.ex "Thomas" if cell d28 is the lowest number of four cells.

Identify Highest Value In A Column
is there a way to highlight the highest value in a column.

=MAX(A1:A50) will write the highest value in whatever field the formula is written in, but is there a way for the number itself to be highlighted in its given location within the colum.For example, if A 21 contains the highest value in column A, can that cell be highlighted?

Extracting The Highest Top 5 Values
extract the highest top 5 values from one column in a filtered table with hidden cells. How can I do this without using advance filter or a pivot table in excel 2003 that will show only the visible cell values and not the hidden cell values

Index, Match For Highest Value
1) I need to show the latest PM service date.
2) I need to show the latest "other" repair type service date ( which could be GM, RE, **, or WY type). I just need the latest date for any of those types.
3) Finally, I need the latest Meter reading, regardless of type.

In my "Master" sheet, so far I've only accomplished finding the first work service date and the first meter reading listed for a piece of equipment. I don't know how to go from there.

Note: Some equipment will not have any work orders originally, and some equipment may have a service date but no meter reading because the equipment has no meter to read.

I've color-coded the columns I'm using as references in my calculations in the attachment.

Averaging Only The X Highest Numbers In A Row
Is there a way to average only the X highest number of scores in a row?

What about if there is an additional condition, i.e., if the column

Sum Highest Numbers In A Range ...
I am looking for a formula that will add the 8 highest values in a range of up to 20 cells. The range may have fewer than 8 values in some cases in which case the sum of all those values will be returned.

Thus a row with these values will yield the following output (bold denotes highest 8 values):

8, 28, 45, 13, 17, 19, 21, 30, 47, 8, 15

47+45+30+28+21+19+17+15= 222

Getting Numbers To Go From Highest To Lowest
I have A bunch of numbers going up to 4.0 from 0.0 how can i get it so that it takes the number and the name beside it and buts it in a colume from 1 to ..... complete
ex)
David 1.5
Jon 3.5
Sally 4.0
Susan 3.24
Fred 2.99

Highlite The Highest Value In Certain Cells
how to highlite certain cells.

For instance, I want to highlite the highest value in certain cells (Red).

Example: Find max value E4 and E15
E5 and E16
E6 and E17
And also is there a way to have this happen automatic without selecting a macro. I want it to always be in the sheet1 when I open it.

Formula To Look Up The Highest Value From A Range
i want my formula to look up the highest value from a range (the =max column), then return a name in the leftmost column. What it actually is, is a player of the month for fantasy football. Each week the player gets a score, then each month, a total of four weekly scores. i want to look up who scored the most and return the player name to me. see the attachment.

Extracting 3 Highest Numbers
I came up with a way to extract entire row corresponding to the highest number in L column and to post it in the different sheet.

I have 2 issues to solve though.

1. I need to extract only 13 ranges corresponding to the highest number in L column - ( A through M) - not entire row.

2. I need to do the same with the second and the third highest numbers of the same column L.

This is what I have so far:

Sub extract()
Dim first As Range
Set first = Range("L" & WorksheetFunction.Match(WorksheetFunction.Max(Columns("L")), Columns("L"), 0))
first.EntireRow.Select
Selection.Copy
Sheets("Result").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Pull Top 3 Highest Values
I have a column of numbers that I would love it if Excel could pull out the top 3 or 4 highest values from the column.

Find Highest Value From Range
I have a large spreadsheet of data that contains 3 columns: Columns are Name, Revision, # of items. I want to find the # of items for the highest revision for each name. List looks like this:

Smith, 1, 30
Smith, 2, 36
Smith, 3, 18
Johnson, 1, 125
Johnson, 2, 130
Lopez, 1, 8
Lopez, 2, 12
Lopez, 3, 15
etc…

I'm only interested in the data for the latest Revision. There's over 500 names. A lookup or pivot could be possible?

Look Up A Value And Find The Highest Date Associated With It
I have a spreadsheet with a data sheet and a second sheet. On the second sheet I want to look up the value from A2 and find the highest date associated with this value in the data sheet.

This will be used to look at a number of projects that have multiple dates, and I want the latest date i.e. the furthest into the future. To add a spanner to the works, some dates are recorded as N/A so I obviously want to ignore these. I have attached an example workbook if anyone has a couple of minutes to take a look.

Return Highest Alphanumeric Value
I'm looking to return in cell BG2, the highest alphanumeric value from 10 non consecutive cells (highlighted yellow). When I use the MAX function it works fine should I only have numbers in the 10 cells, but the values in the cells very from 1A to 5Z [all instances of these values are shown in a table on sheet2 with their 'scoring']

Overwrite Highest Number
in cell A1 i have the balance. i would like to record the balance every time it reaches a new high in cell B1. how would this be possible please using excel 2007

Returning The Person With The Highest Value
I have some data as below and I want to return the name of the person with the highest value.

Col A Col B Col C Col D
John Peter Paul Luke
3 4 7 8

Find Highest Value With VLOOKUP
using vlookup I am trying o find the highest value>>>>

e.g

Column A Column B

111036 01/05/09
111036 08/08/09
111036 09/10/09 <<<<

Is vlookup the correct way to go,,, if not could someone point me in the correct direction

Conditional Format Highest Value
I have a row of figures from cell C1:C100 and I want the highest figure in that row to be in bold and red (conditional formating I think)

I cant work out what formula I need to enter in the conditional formating section.

Lookup To Find Highest Value
The following calc works perfectly to find the latest entry in a column of meter readings in one worksheet and return the value to a master worksheet.

Highest Average In A Group
I have a group of 8 numbers and I have to pick the 6 highest of them and make an average out of this 6.
How can I do that, using formulas?

Round Up To The Highest Number
this is a formular that i used to divide

=IF(Z6=TRUE,K6/MID(D6, FIND("1=",D6)+2,LEN(D6)-FIND("1=",D6)-1),K6)

the problem i have is that if i have 14 in cell k6 and 1=10 in d6 the answer i get is 1.40 what i need the formular to do is always round it up to the highest number
ie 2 not 1.4

Ranking - Highest To Lowest
I am trying to do is rank the value in column G from the highest positive value to the lowest and then the highest or largest negative to the lowest or smallest negative in that order. The way it is arranged/sorted in the attachment is exactly how it should be for this example. However, the ranking formula does not work? What it needs to do is show the value in cell H13 as the 6th ranking and not the 13th?

Taking Next Highest Number
I have a dilemma. What I want to do is: If I input a date in a cell I want the adjacent cell to take the next highest number from a list on another worksheet. My example attached

Average Of X Highest Numbers
I want to pick the top five numbers out of a random list of numbers and get its average. What is the best way to go about doing this?

Indexing Top X Highest Of A Range
In the attached sheet, I'm trying to get excel to return the top 5 values in an array, based on the preceding cell. This example is for a Football League, in which the QBs, RBs and WRs all have different salaries. The objective is to return the salaries of the top 5 paid people in each. Don't need to return the name, just the salary amount, so that an average can then be taken to figure future salaries.

Find And Replace Next Highest
is there a way to write a macro that will do a find and replace and increase the find value by 8 and increase the replace value by 2 each time?

Lookup Function - Looking For Next Highest Value
Im just setting up a spreadsheet for structural timber design calculations. One of the requirements for this spreadsheet is to look up a Zx value from a table. Once the Zx value is found it returns a size of timber from another column. I have managed to create the function so that it looks up the Zx value and returns the size. However, if the function cant find the exact Zx value, which will usually be the case. The next lowest Zx value is used. Is there a way i can edit this function so that it returns the next highest Zx value instead of the next lowest. At the moment this is the code i am using.

= LOOKUP(Q92, 'Zxx Table'!E5:E172,'Zxx Table'!C5:C172)

Where Q92 is the value to be looked up. E5:E172 is the column of values to be searched. C5:C172 is the column of values where the result comes from. In addition to this just to further complicate things, can it return say the next 5 highest values? If a lookup function cant be used could you tell me another way that i can achieve this.

Highlight Highest Value In Range
Is it possible to highlight the highest value within a given range in that particular cell ? Example: I have a range of value from cells A1 to A10. The highest value is in cell A3. Can I make cell A3 highlighted in colour red?

Record Highest/Lowest From DDE
I have a cell to receive real time data during a period of time(the period depends on other cells). My problem is I want only the greatest value of the real time data. How can I manage this?