Bill Golden

asked on

# Compound IF Statement

I am at a loss how to do this. I want to put the word Normalized in front of EBIT or EBITDA when cell K58 contains an X

=IF(C58="X"," "&AA112&"-YEAR EBIT AVERAGE FROM BELOW: "," "&AA112&"-YEAR EBITDA AVERAGE FROM BELOW: ")

=IF(C58="X"," "&AA112&"-YEAR EBIT AVERAGE FROM BELOW: "," "&AA112&"-YEAR EBITDA AVERAGE FROM BELOW: ")

I don't think what you want is as simple as this:

`=IF(C58="X"," "&AA112&"-YEAR Normalized EBIT AVERAGE FROM BELOW: "," "&AA112&"-YEAR Normalized EBITDA AVERAGE FROM BELOW: ")`

I would rewrite that to have the common parts once only:`= " "&AA112&"-YEAR Normalized " & IF(C58="X","EBIT", "EBITDA") & " AVERAGE FROM BELOW: "`

ASKER

I apologize! I did a poor job of explaining what I need. IF C 58 contains an "X" the word EBIT is used, if not, the word EBITDA is used in its place.

In this case, EBIT can be Normalized or not and EBITDA can be Normalized or not.

So, you have four (4) possible outcomes. Problem is I do not understand how to insert another IF statement inside an existing IF statement.

In this case, EBIT can be Normalized or not and EBITDA can be Normalized or not.

So, you have four (4) possible outcomes. Problem is I do not understand how to insert another IF statement inside an existing IF statement.

In that case, your formula would be either of:

=" " & AA112 &"-YEAR " & IF(C58="X","NORMALIZED ","") & IF(K58="X","EBIT","EBITDA") & " AVERAGE FROM BELOW: "

=CONCATENATE(" ",AA112,"-YEAR ",IF(C58="X","NORMALIZED ",""),IF(K58="X","EBIT","EBITDA"), " AVERAGE FROM BELOW: ")

Choose between the formulas according to whichever is easier for you to understand.

=" " & AA112 &"-YEAR " & IF(C58="X","NORMALIZED ","") & IF(K58="X","EBIT","EBITDA"

=CONCATENATE(" ",AA112,"-YEAR ",IF(C58="X","NORMALIZED ",""),IF(K58="X","EBIT","E

Choose between the formulas according to whichever is easier for you to understand.

ASKER

I'm sorry byundt, but neither formula works. The first one does nothing when you put an X in C 58 or K58. The second one puts Normalized EBITDA when you put an X in C58, which is, of course, backwards. Nothing happen when you put an X in K58.

Are you saying that I had the tests for C58 and K58 backwards?

=" " & AA112 &"-YEAR " & IF(K58="X","NORMALIZED ","") & IF(C58="X","EBIT","EBITDA") & " AVERAGE FROM BELOW: "

=CONCATENATE(" ",AA112,"-YEAR ",IF(K58="X","NORMALIZED ",""),IF(C58="X","EBIT","EBITDA"), " AVERAGE FROM BELOW: ")

Or were you saying that the formulas returned nothing at all?

In either event, I am posting a sample workbook.

CompoundIF_Q28931612.xlsx

=" " & AA112 &"-YEAR " & IF(K58="X","NORMALIZED ","") & IF(C58="X","EBIT","EBITDA"

=CONCATENATE(" ",AA112,"-YEAR ",IF(K58="X","NORMALIZED ",""),IF(C58="X","EBIT","E

Or were you saying that the formulas returned nothing at all?

In either event, I am posting a sample workbook.

CompoundIF_Q28931612.xlsx

ASKER

Hold the phone byundt. I am getting your formula to work. I had to reverse C58 with K58 and change K58 to J59 which it should have been to start with. My error!

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

Yes, I had them reversed and K58 should have been J58 to start with. I reversed the text and everything works fine. Once I understood how to add another option "Other Method" I was able to do so. Thank you for giving me an example I could learn from.

I will study the CONCATENATE option to see if I can grasp the logic.

I will study the CONCATENATE option to see if I can grasp the logic.

ASKER

Perfect solution and advice on how to learn more in this area.

In the formula below, I use an IF to test cell K58 for "abc" to see whether it should be EBIT or EBITDA. This nonsensical test is a placeholder for the answer to my question. You need to replace that IF with the answer to my opening question.

That done, you then use the SUBSTITUTE function with another IF to replace EBIT with NORMALIZED EBIT if C58 equals "X".

=SUBSTITUTE(IF(K58="abc","