+ Reply to Thread
Results 1 to 3 of 3

MAX function using nested IF formula

  1. #1
    Registered User
    Join Date
    04-10-2009
    Location
    Colorado
    MS-Off Ver
    Excel 2016
    Posts
    30

    MAX function using nested IF formula

    Hi guys,

    Trying to figure out a nested formula using IF and MAX functions. I've already written a formula to identify duplicates in my data set. If the PAT_NAME matches the name above AND also matches the CONTACT_DATE above then I get a 1, else I get a 0.

    From here I want a second formula in column E that looks at the results of Column D, if there is a 1, then I'd like for it to look at the APPT_TIME/ADMIT_TIME column C associated with the dupe names and give me the MAX date/time from column C.

    Hopefully this makes sense, but if not I've attached an example workbook. My ultimate goal is to find the very first APPT_TIME/ADMIT_TIME in column C associated with the duplicate names, since there can be several visits/appointments. I want to delete the extraneous appointments if a patient had multiple visits.

    Thanks,
    kdestef1
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: MAX function using nested IF formula

    Hi, kdestef1!

    If you have 0 ...
    • do you want to get blank? then:
    =IF(D2,MAX(INDEX((A$2:A$34=A2)*(B$2:B$34=B2)*C$2:C$34,)),"")
    • do you want to get the date in C? then:
    =IF(D2,MAX(INDEX((A$2:A$34=A2)*(B$2:B$34=B2)*C$2:C$34,)),C2)

    Blessings!

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,789

    Re: MAX function using nested IF formula

    Quote Originally Posted by kdestef1 View Post
    ...My ultimate goal is to find the very first APPT_TIME/ADMIT_TIME in column C associated with the duplicate names...
    If you want the FIRST appt, shouldnt you be looking for the LOWEST (1st) date, not the LARGEST (last/max) date?

    If so, then I think this ARRAY formula does what you want, with no need for col D...
    =IFERROR(SMALL(IF((A2&B2=A1&B1)*($A$2:$A$34&$B$2:$B$34=A3&B3),$C$2:$C$34),1),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Then copy down and format as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Add a formula to a nested if function
    By JeninQC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-13-2018, 08:07 AM
  2. [SOLVED] Nested If Formula with Match Function
    By CorvusReed in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-01-2015, 06:49 PM
  3. Nested IF function causing formula overflow
    By Vicius in forum Excel Formulas & Functions
    Replies: 51
    Last Post: 01-15-2013, 03:11 AM
  4. Nested formula in IF Function
    By Shaun Gemiver in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2012, 10:10 AM
  5. [SOLVED] Need help with revising nested formula IF function.
    By digibay in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-27-2012, 02:31 PM
  6. Nested IF function, can't figure out the formula
    By gibby82 in forum Excel General
    Replies: 2
    Last Post: 02-01-2012, 01:35 PM
  7. [SOLVED] Help: Nested If/And Function formula to VB code
    By RAP in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2005, 01:05 AM

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