SOLVED

Nested IF and MAX statements

%3CLINGO-SUB%20id%3D%22lingo-sub-3333847%22%20slang%3D%22en-US%22%3ENested%20IF%20and%20MAX%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3333847%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20combine%20and%20IF%20and%20MAX%20statement.%20If%20J2%3DMIDPOINT%20I%20want%20the%20answer%20to%20be%20the%20higher%20of%20M2%20or%20H2%2C%20IF%20J2%3DEntry%20I%20want%20the%20answer%20to%20be%20the%20higher%20of%20H2%20or%20L2.%20I%20keep%20getting%20a%20FALSE%20response%20after%20the%20first%20statement.%20I%20realize%20that%20it%20is%20reading%20the%20first%20IF%20of%20the%20formula%20and%20then%20saying%20its%20not%20midpoint%20so%20it%20is%20not%20moving%20to%20the%202nd%20If%20statement.%20How%20do%20I%20change%20the%20formula%20to%20get%20past%20the%20first%20if%20statement%20if%20false%20and%20then%20move%20to%20the%202nd%20IF%20statement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22581%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EH2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EJ2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EL2%3C%2FTD%3E%3CTD%3EM2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAnswer%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2289%22%3E%2448%2C150%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E10%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EMidpoint%3C%2FTD%3E%3CTD%20width%3D%2243%22%3E30%3C%2FTD%3E%3CTD%20width%3D%2261%22%3E%2429%2C300%3C%2FTD%3E%3CTD%20width%3D%2263%22%3E%2436%2C573%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E%2443%2C846%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%2448%2C150%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%2442%2C016%3C%2FTD%3E%3CTD%20width%3D%2287%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EEntry%3C%2FTD%3E%3CTD%20width%3D%2243%22%3E30%3C%2FTD%3E%3CTD%3E%2429%2C300%3C%2FTD%3E%3CTD%3E%2436%2C573%3C%2FTD%3E%3CTD%3E%2443%2C846%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(J2%3D%22midpoint%22%2CMAX(H2%2CM2%2CIF(J2%3D%22entry%22%2CMAX(H2%2CL2))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3333847%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3333870%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20and%20MAX%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3333870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383088%22%20target%3D%22_blank%22%3E%40abarshak%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20closing%20parentheses%20aren't%20placed%20entirely%20correctly.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(J2%3D%22midpoint%22%2CMAX(H2%2CM2)%2CIF(J2%3D%22entry%22%2CMAX(H2%2CL2)%2C%22%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3334000%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20and%20MAX%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3334000%22%20slang%3D%22en-US%22%3EThat%20worked%2C%20thanks.%3C%2FLINGO-BODY%3E
New Contributor

I am trying to combine and IF and MAX statement. If J2=MIDPOINT I want the answer to be the higher of M2 or H2, IF J2=Entry I want the answer to be the higher of H2 or L2. I keep getting a FALSE response after the first statement. I realize that it is reading the first IF of the formula and then saying its not midpoint so it is not moving to the 2nd If statement. How do I change the formula to get past the first if statement if false and then move to the 2nd IF statement.

 

H2 J2 L2M2 Answer
$48,15010Midpoint30$29,300$36,573$43,846$48,150
$42,0163Entry30$29,300$36,573$43,846FALSE

 

=IF(J2="midpoint",MAX(H2,M2,IF(J2="entry",MAX(H2,L2))))

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@abarshak 

The closing parentheses aren't placed entirely correctly.

 

=IF(J2="midpoint",MAX(H2,M2),IF(J2="entry",MAX(H2,L2),""))

That worked, thanks.