We’ve retired the Spreadsheet Workout web site and brand. We remain available for Excel tips and tricks, but this is not our main focus.
Spreadsheet Workout Retired
Reply
Category Archives: Excel
LARGE() function and rows with same rank
A finesse you can apply with the LARGE() function is to consider the case where you have rows with the same value, e.g:
A B
1 James
3 Fred
2 Ian
1 Graham
3 Nigel
The LARGE(A1:A5,1) function will simply tell you that 3 is the largest value. But you may actually want the top three rows, e.g. to return the set (Fred, Nigel, Ian)
The way to achieve this is to add some unique key value to each ranking value so that they become distinct. In the example above, we could create a new column with value as follows:
=A1+ROW()/1000000
This will then give us:
A B C
1 James 1.000001
3 Fred 3.000002
2 Ian 2.000003
1 Graham 1.000004
3 Nigel 3.000005
If we run the LARGE() function against Column C, we now have our unique values, which we can then feed into a VLOOKUP to return the person’s name.
ADODB Connection Object
Was banging my head against the wall a bit tonight trying to close my ADODB.Connection object down cleanly. My mistake was writing something like the following:
If Not objConnection Is Nothing Then objConnection.Close
It seemed that objConnection was both not nothing, and throwing an error about already being closed.
Solution was to check the State property first, so now I have:
If objConnection.State <> adoStateClosed Then objConnection.Close
New Excel Tricks
1. LARGE()
Used this to find the top 5 of 200 values. Never know you could do this before!
2. SUM(Sheet1:Sheet10!A1)
I vaguely knew you could sum across worksheets before, but cool to rediscover this.