+ Reply to Thread
Results 1 to 7 of 7

Find the Max and Time then drag formula

  1. 02-04-2013, 10:31 PM #1
    JDCONNER
    JDCONNER is offline
    Registered User
    Join Date
    09-25-2011
    Location
    Wichita Falls,Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Find the Max and Time then drag formula

    BONDS TEST.xlsx

    I'm trying to find the max in column c3:c11 and then give the time in column A that will allow me to drag that formula to find the max and time of c12:c20 etc. The formula I have in column I is =(INDEX(A:A,(ROW(A1)*9)+2)) and in column J is =INDEX(A3:A11,MATCH(MAX(C3:C11),C3:C11,0)). This formula I tried using but it's not right =INDEX(INDEX(A:A,(ROW(A1)*9)-6):INDEX(A:A,(ROW(A1)*9)+2)),MATCH(MAX(INDEX(C:C,(ROW(A1)*9)-6):INDEX(C:C,(ROW(A1)*9)+2)),INDEX(C:C,(ROW(A1)*9)-6):INDEX(C:C,(ROW(A1)*9)+2),0). I attached a picture.

    Thanks
    JdConner
    Attached Images Attached Images
    Last edited by JDCONNER; 02-04-2013 at 10:55 PM.
    Reply With QuoteRegister To Reply
  2. 02-04-2013, 10:38 PM #2
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Find the Max and Time then drag formula

    Welcome to the forum since your new to the forum may i suggest you to attach a real workbook typing data from a picture or paste text from your post into a spreadsheet as a hindrance to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    regards,
    vladimir
    Last edited by vlady; 02-04-2013 at 10:41 PM.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  3. 02-05-2013, 12:05 AM #3
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Find the Max and Time then drag formula

    J3: =MOD(INDEX(A$3:A$7013,MATCH(1,(C$3:C$7013=MAX((INT(A$3:A$7013)=INT(I3))*(C$3:C$7013)))*(INT(A$3:A$7013)=INT(I3)),)),1)

    Array formula: Press Ctrl+Shift+Enter, not just Enter

    Copy down
    Reply With QuoteRegister To Reply
  4. 02-05-2013, 10:37 AM #4
    JDCONNER
    JDCONNER is offline
    Registered User
    Join Date
    09-25-2011
    Location
    Wichita Falls,Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Find the Max and Time then drag formula

    That works Thanks. I have one question, I'm trying to get the low of the day in column D. When I use your formula I get #N/A. This is the formula I used in column K. =MOD(INDEX(A$3:A$7013,MATCH(1,(D$3:D$7013=MIN((INT(A$3:A$7013)=INT(I3))*(D$3:D$7013)))*(INT(A$3:A$7013)=INT(I3)),)),1)

    Thanks
    JD
    Reply With QuoteRegister To Reply
  5. 02-05-2013, 11:40 AM #5
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Find the Max and Time then drag formula

    Quote Originally Posted by JDCONNER View Post
    That works Thanks. I have one question, I'm trying to get the low of the day in column D. When I use your formula I get #N/A. This is the formula I used in column K. =MOD(INDEX(A$3:A$7013,MATCH(1,(D$3:D$7013=MIN((INT(A$3:A$7013)=INT(I3))*(D$3:D$7013)))*(INT(A$3:A$7013)=INT(I3)),)),1)

    Thanks
    JD
    Try it like this for MIN()

    =MOD(INDEX(A$3:A$7013,MATCH(1,(D$3:D$7013=MIN(IF(INT(A$3:A$7013)=INT(I3),D$3:D$7013)))*(INT(A$3:A$7013)=INT(I3)),)),1)

    Ctrl+Shift+Enter, not just Enter
    copy down
    Reply With QuoteRegister To Reply
  6. 02-05-2013, 01:43 PM #6
    JDCONNER
    JDCONNER is offline
    Registered User
    Join Date
    09-25-2011
    Location
    Wichita Falls,Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Find the Max and Time then drag formula

    That works thanks alot.
    Reply With QuoteRegister To Reply
  7. 02-05-2013, 02:30 PM #7
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Find the Max and Time then drag formula

    You're Welcome!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1