# Thread: 3 high values (2000)

1. ## 3 high values (2000)

Column A, Rows 1-100 contain sequential dates; Column B, Rows 1-100 contain prices of commodities. I am looking for a formula that will look through column B and return (1) the highest three consecutive prices (to be put in cells C1-3), and (2) the three highest prices (to be put in cells D1-3), that may or may not be consecutive. I have tried many different combos, but can't seem to get everything to mesh. Any ideas?

2. ## Re: 3 high values (2000)

The second part's easy enough. Put:
=Large(B\$1:B\$100,row())
in D1 and copy down to D3. If two or more of the top values are repeated, they'll be repeated accordingly. If you want the three largest disrete values, please post back.

I'll need more time to think about the first part, though. Maybe someone else will beat me to it ...

Cheers

3. ## Re: 3 high values (2000)

Works great-thanks. That's the first time I've seen LARGE used in conjunction with ROW(). Would you mind explaining how they operate together?
Thanks again.

4. ## Re: 3 high values (2000)

Hi,

=ROW() simply returns the current row number. Since your formula was going on rows 1-3 and you wanted the largest 3 values, this is simpler and more flexible to use than three individually coded LARGE formulae.

Cheers
PS: I've figured out how to do the 1st part of your problem using an intermediate column to return the sums of each set of three rows, and using the LARGE formula against that, but there may be a way to get the desired result without one, and without resorting to vba - which can do it for sure.

5. ## Re: 3 high values (2000)

This array formula (confirm with ctrl-shift-enter) will give you the lowest value in the sequence with the highest prices, so put this in C3:
<pre>=MAX((B1:B98=(B2:B99-1))*(B1:B98=(B3:B100-2))*(B1:B98))</pre>

In C2 have:
<pre>=C3+1</pre>

In C1 have:
<pre>=C3+2</pre>

Steve

6. ## Re: 3 high values (2000)

The following macro gives the required results for the three highest consecutive values, based on which three values give the highest sum, in the order in which they appear:

Sub ThreeUp()
Range("C1:C3").Clear
Val1 = 0
Val2 = 0
Val3 = 0
CurrentValue = 0
NewValue = 0
For I = 1 To 100
CurrentRow = I
If CurrentRow < 99 Then
Endrow = CurrentRow + 2
ElseIf CurrentRow = 99 Then
Endrow = CurrentRow + 1
ElseIf CurrentRow = 100 Then
Endrow = CurrentRow
End If
NewValue = Application.WorksheetFunction.Sum(Range("B" & CurrentRow & ":B" & Endrow))
If NewValue > CurrentValue Then
Val1 = Range("B" & CurrentRow)
Val2 = Range("B" & CurrentRow + 1) * (CurrentRow < Endrow) ^ 2
Val3 = Range("B" & CurrentRow + 2) * (CurrentRow + 1 < Endrow) ^ 2
CurrentValue = NewValue
End If
Next I
Range("C1").Value = Val1
Range("C2").Value = Val2
Range("C3").Value = Val3
End Sub

Cheers
PS: I haven't been able to get Steve's formula to work - all I get is 0s ...

7. ## Re: 3 high values (2000)

I am afraid I am getting the same results as Macropod. Still trying to figure it out, though!

8. ## Re: 3 high values (2000)

Macropod,
I placed the code in a module in my Personal.xls file, removed the "Option Explicit" statement (causing the program to "hiccup"), and it works fine!
Thanks,
Jeff

Thanks again

10. ## Re: 3 high values (2000)

Macropod,
Here is my line 2: (I would like to keep them as integers so that the program can be adapted to another application I have in mind)
"Dim Val1 As Integer, Val2 As Integer, Val3 As Integer, CurrentValue As Integer, CurrentRow As Integer, Endrow As Integer, NewValue As Integer, I As Integer".
Now I am getting an error message that says there is an overflow at line: "NewValue = Application.WorksheetFunction.Sum(Range("C" & CurrentRow & ":B" & Endrow))"
I haven't the slightest what this means-I am not that astute in VBA-yet!
Any ideas?

11. ## Re: 3 high values (2000)

Hi Jeff,

Glad to hear that it worked as intended. I'd recommend restoring 'Option Explicit', though, and inserting:
DIM Val1 as Currency, Val2 as Currency, Val3 as Currency, CurrentValue as Currency, NewValue as Currency, CurrentRow As Integer, Endrow As Integer, I As Integer
as the second line.

Cheers
PS: I'd also be inclined to attach the macro to the workbook concerned, rather than to personal.xls, so that anyone else using that workbook will have access to the macro also.

12. ## Re: 3 high values (2000)

Hi Jeff,
I'm not sure what's causing this for you - unless you're working with large values (>32,767), in which case change the DIMs for NewValue and CurrentValue to long.

CurrentRow = 0
Endrow = 0
before the 1st IF statement, that will resolve it.

13. ## Re: 3 high values (2000)

I'll try it. One last question: what code can I insert and where to get the output at C1:C3 in an "Accounting"-type format with no decimals anda comma separater? I have tried several disserent ways, but none seem to work.
Thanks again

14. ## Re: 3 high values (2000)

Consecutive numbers are those that are X, X+1, X+2. If you have none of this sequence, you will get a zero.

If you are looking for just numbers that just increase 3 in a row, then the row with the largest series is given by:
<pre>=MAX(IF((B1:B98<(B2:B99))*(B2:B99<(B3:B100)), ROW(B1:B98)))</pre>

Put this in cell D1 (eg)

Then in c1:C3
<pre>=INDEX(\$B\$1:\$B\$100,\$D\$1+2)
=INDEX(\$B\$1:\$B\$100,\$D\$1+1)
=INDEX(\$B\$1:\$B\$100,\$D\$1)</pre>

Steve

15. ## Re: 3 high values (2000)

YOu can do it manually via format -cells - accounting or via code:

<pre> Range("C1:C3").NumberFormat = "_(\$* #,##0_);_(\$* (#,##0);_(\$* ""-""_);_(@_)"</pre>

Steve

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•