+ Reply to Thread
Results 1 to 10 of 10

Parenthesis with complex if statements

  1. #1
    Registered User
    Join Date
    02-19-2022
    Location
    usa
    MS-Off Ver
    2016
    Posts
    4

    Parenthesis with complex if statements

    Can someone describe in simple terms the way if statements, and nested if statements, and if(and statements and max(if statements need parenthesis and how they relate to combining them into other formulas?


    I currently have a complex index(match formula with multiple criteria that I want to incorporate a max(if into and for the life of me I cannot get past the "your formula is missing some rando parenthsis but haha I'm not going to tell you where it is, b****!".

    I am frequently using these long complex formulas and it is a constant battle with figuring out how many parenthesis I need and where they go as there seems to be no rhyme or reason to it.

    I know there is a rhyme or reason to it so can someone explain what it is?
    Last edited by ihateexcel17; 02-19-2022 at 08:01 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,919

    Re: Parenthesis with complex if statements

    Every function requires its own pair of opening/closing parentheses regardless whether it is nested in any other function. If you have an odd number, then you have trouble...

    Many (Most) functions operate on multiple conditions/argument and there is no way for Excel to know what you are up to...
    You'll also notice that each pair is a different color from the others...
    Last edited by protonLeah; 02-19-2022 at 09:12 PM.
    Ben Van Johnson

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,174

    Re: Parenthesis with complex if statements

    Hi,

    If you upload the file and explain how to get the result, there will be another shorter formula.

  4. #4
    Registered User
    Join Date
    02-19-2022
    Location
    usa
    MS-Off Ver
    2016
    Posts
    4

    Re: Parenthesis with complex if statements

    File attached.
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,257

    Re: Parenthesis with complex if statements

    Each will follow this pattern:

    =MAX(IF('disciplinary action data'!A$2:A$10=$A2,IF('disciplinary action data'!C$2:C$10="Absent",'disciplinary action data'!B$2:B$10)))

    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  6. #6
    Registered User
    Join Date
    02-19-2022
    Location
    usa
    MS-Off Ver
    2016
    Posts
    4

    Re: Parenthesis with complex if statements

    Thanks Ali!

    But let me clarify what I was trying to get in one cell.

    The way I labeled the columns in my example is confusing.

    Instead of "Latest Absent DA" or "Latest Communication DA" (so on so forth) I should have written "Latest Absent DA LEVEL" and "Latest Communication DA LEVEL", etc, thus the need for the INDEX(Match.

    I don't want to return the date, I want to return the Level associated with the date.

    So I want something that is Indexing the Level (Verbal, Final, or Written?) and matching it to the employee name, the disciplinary type ("absent" "communication" or "tardy") and the latest date that this DA was received based on the type of DA indicated in the column

    So like for the "Absent Column I think it would be something like:

    INDEX(THE LEVEL COLUMN on the disciplinary action sheet,MATCH(1(EMPLOYEE NAME on disciplinary action sheet=NAME IN 1ST CELL IM REFERENCING) * (DISCIPLINARY ACTION COLUMN on the disciplinary action sheet="Absent") * (MAX(IF(NAMES IN COLUMN A on the disciplinary action sheet=NAME IN FIRST CELL IM REFERENCING,IF(DISCIPLINARY ACTION LEVEL COLUMN on the disciplinary action sheet="Absent", DATES LISTED IN COLUMN on the disciplinary action sheet),0))

    But I cannot get this formula to work when I try to incorporate MAX(IF(IF into the index(match with multiple criteria.

    It's starting to look like a science project.
    Last edited by ihateexcel17; 02-20-2022 at 03:41 PM.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,919

    Re: Parenthesis with complex if statements

    Please Login or Register  to view this content.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,919

    Re: Parenthesis with complex if statements

    Using named ranges:
    Action_Date ='disciplinary action data'!$B$2:$B$10
    Action_Level ='disciplinary action data'!$D$2:$D$10
    Action_Status ='disciplinary action data'!$E$2:$E$10
    Action_Type ='disciplinary action data'!$C$2:$C$10
    Name ='disciplinary action data'!$A$2:$A$10
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-19-2022
    Location
    usa
    MS-Off Ver
    2016
    Posts
    4

    Re: Parenthesis with complex if statements

    Thanks protonLeah this is exactly what I was trying to do.

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,919

    Re: Parenthesis with complex if statements

    You're welcome

+ 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. [SOLVED] Complex If/Then Statements
    By CorvusReed in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-03-2014, 06:36 PM
  2. Replies: 1
    Last Post: 03-30-2012, 01:42 PM
  3. Excel 2007 : Complex IF Statements
    By JonathanKilleen in forum Excel General
    Replies: 1
    Last Post: 11-27-2009, 06:03 AM
  4. Excel 2007 : Complex IF Statements.
    By JonathanKilleen in forum Excel General
    Replies: 1
    Last Post: 11-27-2009, 05:29 AM
  5. complex SUMPRODUCT statements
    By cvillemtnman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-23-2008, 06:18 PM
  6. Complex Vlookup and List Validation and Nested IF statements
    By Bobby in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-09-2006, 01:40 PM
  7. complex if statements in excel
    By Julieeeee in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2005, 05:06 PM

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