Excel Tips - Nth Largest Value In A Range

Gday all, I have spent quite abit of time on this prob, done plenty of searching, tried alot of things but can't quite get what I want.

I have a List of unsorted values in a column, named FreightDist, what I'm trying to accomplish is to Sum up the Nth largest Unuique Values, but any duplicated values need to be added.

For example I want to sum up to the 2nd largest, say the 1st largest is 100, and the 2nd is 50, but there are 2 lots of 50 in the list, I need my end result to be 200.

The way I have managed to do it so far is on a table on the side, with the 1st (J3) cell having the following array formula.
=LARGE(IF(MATCH(FreightDist,FreightDist,0)=ROW(FreightDist)-MIN(ROW(FreightDist))+1,FreightDist,""),1)*COUNTIF(FreightDist,LARGE(IF(MATCH(FreightDist,FreightDist,0)=ROW(FreightDist)-MIN(ROW(FreightDist))+1,FreightDist,""),1))

Then I have the Following in J4 cell
=IF(J3+LARGE(IF(MATCH(FreightDist,FreightDist,0)=ROW(FreightDist)-MIN(ROW(FreightDist))+1,FreightDist,""),2)*COUNTIF(FreightDist,LARGE(IF(MATCH(FreightDist,FreightDist,0)=ROW(FreightDist)-MIN(ROW(FreightDist))+1,FreightDist,""),2))=J3,0,J3+LARGE(IF(MATCH(FreightDist,FreightDist,0)=ROW(FreightDist)-MIN(ROW(FreightDist))+1,FreightDist,""),2)*COUNTIF(FreightDist,LARGE(IF(MATCH(FreightDist,FreightDist,0)=ROW(FreightDist)-MIN(ROW(FreightDist))+1,FreightDist,""),2)))
Which checks if the sum of 1st and 2nd = 1st, if true = 0, otherwise add 1st and 2nd. I check this as I don't want the list to repeat the same value.

The problem I have now is the formula is too long to insert an iserror.
I hoping that makes some sort fo sense

Hi!

I am working with a large amount of columns, but the important columns are these; A = date, B = value. For every single date, I have several values.

Example:

Code:

A             B

2001-01-01     4.5
2001-01-01     5.2
2001-01-01     3.2
2001-01-02     1.2
2001-01-02     4.2
2001-01-02     5.0

In order to find the biggest value in B, for every date, I have made a macro that sorts the database, first on column A (oldest to newest date), then on column B (biggest to smallest value), but the more data I work with, the slower Excel gets, and sometimes my laptop is unable to handle this kind of sorting, causing Excel to crash.

Is there a more efficient way to do this? The database is already sorted on column A, but the values in column B are random, so I need some kind of Max()-function that is able to find not only the biggest value a certain date, but also the second biggest, and so on. This is dynamic, sometimes I need to find the second, third, fourth...biggest value if the first value is 'wrong', sometimes not.

Also, I read somewhere that selecting cells was a bad idea, performance wise. Is this true? Is it better to use the Offset-function in order to manipulate and get values, instead of first selecting a cell, and then go with the ActiveCell.Value?

Thank you!

Hi,

I thought this would be a simple answer to find, but I havn't quite
found a solid answer yet..

Question, What is the formula to find the largest value in a Range?

In my case I would like to use a formula that would retrieve the largest
value in M15:M26.

Thanks for any help on this..

Hi all I have 6 rows (A-F) with dollar vallues. I am trying to create a formula on row G that will give me the highest dollar value out of rows A-F.

Thank you!

I am looking for a formula that will select the largest number in a range
and make that number "bold." Is there a formula that will do this?

Thanks in advance for any help.

RBG

What is the easy way to create forms in different sheets.......

In Main Sheet (Sheet1) each row contains some informations about a particular item.
I want to create Forms in separate Sheets and datas for this Form 1 (Sheet2) should be datas of Row#1 of Sheet1 & datas for this Form 2 (Sheet2) should be datas of Row#2 of the Sheet1.......

Hi Guys,
Does anyone know of a web site which offers Excel tips and tricks.
Is there a free book which I can download that will give me tips and tricks.
Thanking you in advance,
Regards

J-O

Hi,

I've been reading and learning a lot from this forum an usually found my answers in Solved Threads, but this time I haven't. So here's my problem :

I've wrote some VBA code to search for the 5 largest values in a given range, but it doesn't work like its supposed to.

Goal : the code searches through a range for the 5 largest values and puts them in the array named Grootste()
Important : The range consist of multiple non-adjacent columns.

Code:

Sub Largest5()

Dim c As Range
Dim c1 As Range
Dim iRange As Range
Dim cChosen As Integer
Dim cellValue As Integer
'Dim Grootste5() As Integer
Dim x As Byte
Dim Teller As Byte

Set iRange = Range("RangePlayer1")
'ReDim Grootste5(5)
Counter2 = 10

For Each c In iRange

    If c.Value Like "#f" Then
        cChosen = Left(c.Value, 1)
    ElseIf c.Value Like "##f" Then
        cChosen = Left(c.Value, 2)
    End If
    
    Counter = 1

    For Each c1 In iRange
    
        If c1.Value Like "#f" Then
            cellValue = Left(c1.Value, 1)
        ElseIf c1.Value Like "##f" Then
            cellValue = Left(c1.Value, 2)
        End If

        If cellValue > cChosen Then
            Counter = Counter + 1
        End If
           
    Next c1

    If Counter < 5 Then
            Grootste5(Counter) = cChosen
    End If

Next c

For x = 1 To 5
        Cells(1, 10 + x) = Grootste5(x)
Next x

End Sub

I hope there anybody out there who can give me a push in the right direction.

thx

Alain from Belgium

It doesn't matter whether I use a macro or a formula, but I need to know the easiest way to examine a row of numbers (actually a few thousand rows), and for each row first determine which two are the largest and smallest numbers in the row, and then determine the degree of difference between them.

I have 11 regions, with 10 to 20 programs in each,each program having 10 to 20 different acct codes. For each acct code I have the costs per work for September through March, each in a different column. I want the average to know how much variation there is in each acct for the year.

I'd appreciate any help I could get.

Hi there

Th MAX & MIN functions return the largest & the smallest values in a
range.

Is there a way to get the 2nd largest and smallest values?

Any help appreciated.

Thanks

Michael