+ Reply to Thread
Results 1 to 19 of 19

min and max

  1. #1
    Registered User
    Join Date
    03-28-2008
    Posts
    26

    min and max

    I have data in columns b c d, in column be data is spreadout across different lines. Ie data in

    b2
    b34
    b56
    b70
    b89


    then i have data in every row in columns c and d, What I am trying to do is find the max in column c based on column b's entries


    ie

    find max between

    b2
    b34

    find max between
    b70
    b89

    let me just say the # of lines (data) in b are thousands.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Not exactly sure what you are trying to do....

    What kind of data is in column B and how will you be defining the range to look for?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-28-2008
    Posts
    26
    the data is entry and exit points, as far as the range thats the probloem the range length varies and i need to make the max floating to those various ranges. I have 10 spreadsheets of 25000 lines with hundred of entry and exit points in each spreadsheet and dont want to do this manually

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Maybe you can attach a sampling of your data and what you expect as a result? Actual data in cells and how to define the ranges.

  5. #5
    Registered User
    Join Date
    03-28-2008
    Posts
    26
    no i cant do that, what i trying to do is capture the high and low of a stock after an idicator says to get buy it, The indicator is never constant its entry points are based on many values

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    The only guess I can make then is that you have 2 values entered in 2 cells that indicate the start and end points in column B and that you want the high/low from column C between those start/end points...

    =MIN(INDEX(C:C,MATCH(E1,B:B,0)):INDEX(C:C,MATCH(E2,B:B,0)))

    and

    =MAX(INDEX(C:C,MATCH(E1,B:B,0)):INDEX(C:C,MATCH(E2,B:B,0)))

    where E1 and E2 contain the start/end points.

  7. #7
    Registered User
    Join Date
    03-28-2008
    Posts
    26
    The only problem is that the entry point (i belive your formula called it e) data series are always different sizes and dont contain data between them. Here's a sample of the spreadsheet to better explain what im trying to do
    Attached Files Attached Files

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    This is totally different than any previous descriptions I understood....

    Anyways... I still don't fully understand your requirements...but try this formula in M4 copied down and tell me what is right/wrong with it.

    =IF(K4<>0,IF(AND(ISNUMBER(L4),H4>L4),M3+H4-L4,M3),0)

  9. #9
    Registered User
    Join Date
    03-28-2008
    Posts
    26
    ok its close but m7 should change to 80 from 10 h7 - L6. in this example L6 should constant for this data series but the L13 should be the constant for the next series I know how to make a cell constant ie $l$6 but how do you change a constant based on varying data points

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    How about this formula in M4, copied down:

    Please Login or Register  to view this content.
    Note: I am not sure what the result should be if K contains -1 or if L is negative?

  11. #11
    Registered User
    Join Date
    03-28-2008
    Posts
    26
    First let me say I appreciate you help and patience. Secondly I am sorry if I am unable to efficiently communicate what I am trying to do.

    The problem I am experiencing is two fold first L3 needs to be constant only for that series (the next series needs a new constant), and second the formula should consist of only being the max difference from L3 and the data in column H for that same series (data period).

    Once again I am appreciative for all you are doing and have done.

    Mike

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Can you resend the same sheet and enter the expected results in column M manually?

  13. #13
    Registered User
    Join Date
    03-28-2008
    Posts
    26
    try this spreadsheet, Ive tried to explain what I'm looking for in columns next to m and n
    Attached Files Attached Files

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Are you sure you got your cell references in your comments right? Maybe it's just me, but I don't quite understand this whole thing.

  15. #15
    Registered User
    Join Date
    03-28-2008
    Posts
    26
    yes you are right sorry the max column is "H"

  16. #16
    Registered User
    Join Date
    03-28-2008
    Posts
    26
    that is the high for the stock for that 1 second period

  17. #17
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    I usually don't give up on forum questions I have started answering, but I really am confused with this one... I have no idea how you want your system to work and your comments aren't really explaining it well, in my opinion...

    I hope someone else can figure this out for you.... sorry.

  18. #18
    Registered User
    Join Date
    03-28-2008
    Posts
    26
    Thanks for all your help, sorry for the confusion

  19. #19
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Try re-posting the question with a link to this thread... that should get someone else to answer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1