Finding the "Next Max" in Excel VBA 4

[merlynsdad]

At the end of a program to determine most correct monitorings in a call site, I am left with a worksheet that has had all rows, except for those of the winner, deleted. The pertinent code is as follows:

intHigh = Application.WorksheetFunction.Max(Range("u6:u1500"))
intNextHigh = Application.WorksheetFunction.Max(Range "u6:u1500")) - 1
Range("u6").Select
Do Until ActiveCell = ""
If ActiveCell < intNextHigh Then
Rows(ActiveCell.Row).Select
Selection.Delete shift:=xlUp
ActiveCell.Offset(0, 20).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

I also need to find the 2nd highest number of calls, which at present will only be 1 off the winning total. So I'm using intNextHigh for that. In the future, though, this may not be off by just one. How can I find the "next max" number, since I can't resort the original column because I've deleted the rest of the rows?

If the square peg won't fit in the round hole, sand off the corners.

 

[yooneek]

You could sort the data based on the column you are deleting from before you delete.




OCD, it’s not obsessive if you can control it…

 

[SkipVought]

hi,

Enter spreadsheet formula as an ARRAY FORMULA
[tt]
=MAX((u6:u1500<MAX(u6:u1500))*(u6:u1500))
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[merlynsdad]

Can I define my variable intHigh as a formula array like I defined it as a Max function? And if so, how? Thanks.

If the square peg won't fit in the round hole, sand off the corners.

 

[N1GHTEYES]

Skip,

That is a nice formula. I must admit I stared at it for a while before figuring out what it was doing. Very cute. Have a star.

Tony

 

[macropod]

IMHO, Skip's formula is unnecessarily circuitous. Try:
=LARGE(u6:u1500,2)

Cheers
Paul Edstein
[MS MVP - Word]

 

[macropod]

Hi merlynsdad,

Try:
intNextHigh = Application.WorksheetFunction.Large(Range("u6:u1500"),2)

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

Yeah, sometimes I take after Rube Goldberg. Have a [purple]STAR[/purple]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

I would normally have used:

={Max(if(data<Max(data),data))}.

I liked Skip's version mainly because it took me so long to figure out exactly how it worked. However, Paul is, of course, correct. Have a star from me too.

When Large() calculates, does it order the whole data set as part of it's internal process, or just search for the Nth item? If it does the former, then presumably it will be slower, particularly for large datasets, than either of the array formulae versions (Skip's or the one above).

Tony

 

[macropod]

Hi Tony,

I imagine the algorithm behind LARGE a similar to that of MAX. The only real difference being that LARGE needs an array to hold as many values as there are in the second parameter and figure out where any new values go in that array. For your purposes, that's only two values, so it's not much of an overhead.

Being a worksheet array formula, I'd expect Skip's solution to be much slower, since I expect it would have to process the entire range twice - once to find the true 'MAX' and a second time to find the next one.

Cheers
Paul Edstein
[MS MVP - Word]

 

[N1GHTEYES]

Hmmm. How does Large() know which is the 2nd largest value unless it has found the actual Max first? Unless, of course, it is sorting the entire array as it goes along (which would be much slower for large datasets).

I think as a minimum it needs two passes through the data doesn't it? In which case, the Large() method and Skip's would take the same time I think.

However, I'm still happy that yours was the better solution.

Tony

 

[macropod]

As I've already explained, all LARGE needs to do in this case is to hold the two largest values it finds as it makes the first pass.

Cheers
Paul Edstein
[MS MVP - Word]

 

[N1GHTEYES]

You are right, of course. I was being a bit slow - sorry.

However, as a matter of interest, for largish sets with random values, the two methods would actually require almost the same number of comparison checks. At least, they would if you checked the current max max first. The reason being that, as the number of checked values grows, the number of new values you need to check before the current maximum is exceeded grows exponentially.

That means that each number would need to be checked against both current values, so the total number of checks would be the same regardless of whether you did the two passes seperately or at the same time.

Of course, if you checked the smaller max first, then your original assertion is true - the Large() method would be about twice as fast as Skip's.

Tony

 

[macropod]

Hi Tony,

The fundamental flaw in your logic is that, with Skip's approach, you'd need yet another test layer for each interval less than the MAX.

Also, you should note that Skip's approach doesn't give the same results as LARGE when values are repeated. Suppose you've got two entries with the maximum value (eg 3,3,2). Skip's formula will return the largest lesser value (ie 2), whereas LARGE will return the '2nd' instance of the maximum (ie 3). A similar scenario exists for values further down the pecking order. LARGE performs rather like the way you'd treat a dead-heat in a race - the two lead finishers would account for the first two places and the next finisher would get 3rd place.

Cheers
Paul Edstein
[MS MVP - Word]

 

[N1GHTEYES]

Having agreed that the Large() method is the better option, I'm not sure there was a "flaw" in the logic I gave.

To do the nested Max method, you'd need to tests every value for max, then every value for next max = a total 2N tests.

Using Large(), assuming your assertion is correct, it could do one of the following:

Testing max max first:

for each thisval
if thisval> bigmax then
smallmax=bigmax
bigmax=thisval
else
if thisval>smallmax then smallmax=thisval
endif
next thisval

Testing smallmax first:
for each thisval
if thisval>smallmax then
if thisval>bigmax then
smallmax=bigmax
bigmax=thisval
else
smallmax=thisval
endif
endif
next thisval

Assuming that the data is a Gaussian random distribution, and that the dataset is large, the latter method makes about half the number of if comparisons as the former. So, if the Large() method works that way, then it will only do ~N checks. In other words, it will be about twice as fast as the nested Max method. Which is what I said.

Tony

 

[N1GHTEYES]

Just to be clear, in the note above I'm just dealing with the principle of the number of checks. To replicate the actual Large() logic (as you pointed out) the tests would have to be >= not >.

Tony