Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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?
    Thanks in advance

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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 ...
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: 3 high values (2000)

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

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: 3 high values (2000)

    Thanks again

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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.
    Alternatively/aditionally, perhaps if you add the lines:

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

    Paul Edstein
    [MS MVP - Word]

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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 LastLast

Posting Permissions

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