• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

need to link several Ifs

I need to link these if statements but have failed, can an expert out there help please.

=IF(LEFT(L2,2)="CF","SMC","")

=IF(M2="No Dupe",IF(LEFT(K2,3)="FDC","FDC",""),"")

=IF(RIGHT(K2,3)="CHX","CHX","")

=IF(M2="No Dupe",IF(LEFT(K2,2)="GB","GPS",""),"")

=IF(N6="Past",IF(LEFT(K6,1)="M","OverNight",""),"")

Thanks
0
Jagwarman
Asked:
Jagwarman
  • 6
  • 3
  • 2
  • +1
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

Link how? Create just one formula that has all the IF statements? Is the logic of the order the same as in your question? If so, you can just nest the formulas in that order.

But you already use nested IFs, so you know how to do this.

What is the processing logic for each of the "false" parts of your formulas above?

cheers, teylyn
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
What do you mean with "link"? "The first true condition wins", or "concatenate all conditional results"?
The latter would e.g. result in "SMC Overnight".
0
 
JagwarmanAuthor Commented:
they all work separately but when I 'nest' them I am getting False and cannot figure out why

Thanks
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You just use them like this:
=IF(LEFT(L2,2)="CF","SMC","") & " " & IF(M2="No Dupe",IF(LEFT(K2,3)="FDC","FDC",""),"")

Open in new window

0
 
JagwarmanAuthor Commented:
Qlemo

I nested them all together like you said but get FALSE in some scenarios can you help please.

=IF(LEFT(K2,2)="CF","SMC","")&" "&IF(M2="No Dupe",IF(LEFT(K2,3)="FDC","FDC","")&" "&IF(N2="Past",IF(LEFT(K2,1)="M","OverNight","")&" "&IF(M2="No Dupe",IF(LEFT(K2,2)="GB","GPS","")&" "&IF(RIGHT(K2,3)="CHX","CHX",""),"")))

Have attached a file to help.
Nest-formula.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
=IF(LEFT(K2,2)="CF","SMC","")&" "&IF(M2="No Dupe",IF(LEFT(K2,3)="FDC","FDC","")&" "&IF(N2="Past",IF(LEFT(K2,1)="M","OverNight","")&" "&IF(M2="No Dupe",IF(LEFT(K2,2)="GB","GPS","")&" "&IF(RIGHT(K2,3)="CHX","CHX",""),""),""),"")
0
 
JagwarmanAuthor Commented:
the firs 3 in the spreadsheet work but the last two return blank
0
 
Saqib Husain, SyedEngineerCommented:
=TRIM(IF(LEFT(K2,2)="CF","SMC","")&" "&IF(M2="No Dupe",IF(LEFT(K2,3)="FDC","FDC",""),"")&" "&IF(N2="Past",IF(LEFT(K2,1)="M","OverNight",""),"")&" "&IF(M2="No Dupe",IF(LEFT(K2,2)="GB","GPS",""),"")&" "&IF(RIGHT(K2,3)="CHX","CHX",""))
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You always have to make sure you provide values for both the true and false branch of each IF, otherwise you get FALSE.
This means
= IF (A1 = A2, A2, "")

Open in new window

instead of
= IF (A1 = A2, A2)

Open in new window

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You have not replied to my post. I asked for clarification. How can you expect an answer if you do not reply to experts trying to help?

You have half a dozen IF statements where the FALSE part is blank.

You say you want to "link" them.

How? You have not answered that fundamental question.

You need to provide the operational logic. It is not clear if the order of the IF statements as you list them is of any significance.

In all your IF statements, the FALSE is always blank.

It is not possible to create the perfect one-cell formula unless you put into simple words what the processing logic for your data is.

You need to get a piece of paper and write out all the possible combinations and the results for each combination.

Post that, along with a sample file. In the sample file, manually mock up the expected results for a few different scenarios (i.e. type them in).

Post that file.

In general: Use your words. Nobody can guess what is in your cells and why something should be true or false. You need to "describe" that.
0
 
JagwarmanAuthor Commented:
teylyn,  apologies it's been a bank holiday over here for the last 4 days, but Qlemo and Saqib Husain, Syed provided details that I have been working on that has now resolved my problem.

Regards
0
 
JagwarmanAuthor Commented:
Qlemo, thank you for your help that put me on the road to resolving it.
0
 
JagwarmanAuthor Commented:
Saqib Husain, Syed, many thanks for your help with this one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now