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?

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

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).

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.

Please Login or Register  to view this content.

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.

You're welcome