Link to home
Create AccountLog in
Avatar of Bill Golden
Bill GoldenFlag for United States of America

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: ")
Avatar of byundt
byundt
Flag of United States of America image

What is the logic of your formula except for adding the word Normalized? In other words, how do you know whether to use EBIT or EBITDA?

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"," "&AA112&"-YEAR EBIT AVERAGE FROM BELOW: "," "&AA112&"-YEAR EBITDA AVERAGE FROM BELOW: "),"EBIT",IF(C58="X","NORMALIZED EBIT","EBIT"))
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: ")

Open in new window

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

Open in new window

Avatar of Bill Golden

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 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.
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
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
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
Perfect solution and advice on how to learn more in this area.