If Formula

Brittanyy

Hi there, I need assistance on this IF formula.

Formula I have: =IF([Company represented by an attorney]@row = "Yes", "Yes" +[Name of Company]@row + " Has signed contract with others. The purpose of the contract was" +[What was the purpose of such contracts?]@row + " has signed contracts with others. ", IF([Company represented by an attorney]@row = "No",+ "Yes"+[Name of Company]@row + " has signed contracts with others. The purpose of that contract was"[What was the purpose of such contracts?]@row+ "No, the Company was NOT represented by an attorney who advised or consulted on the contract at the time of the agreement.", IF([Company represented by an attorney]@row = "Unsure" "Yes" + [Name of Company]@row +" has signed contracts with others. The purpose of that contract was"[What was the purpose of such contracts?]@row + " " +[Name of Company]@row +"is unsure if they were represented by an attorney who advised or consulted on the contract at the time of the agreement.", "")

Output Information:

o  If Company represented by an attorney = yes, then show below:

 Yes, [Name of Company] has signed contracts with others. The purpose of that contract was [What was the purpose of such contracts?]. Yes, the Company was represented by an attorney who advised or consulted on the contract at the time of the agreement.

 

o  If Company represented by an attorney = No, then show below:

 Yes, [Name of Company] has signed contracts with others. The purpose of that contract was [ [What was the purpose of such contracts?]. No, the Company was NOT represented by an attorney who advised or consulted on the contract at the time of the agreement.

 

o  If  If Company represented by an attorney = Unsure, then show below:

 

Yes, [Name of Company] has signed contracts with others. The purpose of that contract was [ [What was the purpose of such contracts?]. [Name of Company] is unsure if they were represented by an attorney who advised or consulted on the contract at the time of the agreement.

 

KPH

Hi @Brittanyy

The easiest way to troubleshoot long formula like this one, and the one from the other day, is to break them down and test each part separately.

Part 1 here is this bit

=IF([Company represented by an attorney]@row = "Yes", "Yes" + [Name of Company]@row + " Has signed contract with others. The purpose of the contract was" + [What was the purpose of such contracts?]@row + " has signed contracts with others. ", "other situation")

✔And you can see this works for the first row and puts "other situation" for the rest.

The only thing you need to change here, is to add a spaces to your text sections such as the "Yes" text at the start to make this "Yes ", and the start of the contract purpose.

Then we can check part 2 which is

= IF([Company represented by an attorney]@row = "No",+ "Yes"+[Name of Company]@row + " has signed contracts with others. The purpose of that contract was"[What was the purpose of such contracts?]@row+ "No, the Company was NOT represented by an attorney who advised or consulted on the contract at the time of the agreement.","other situation")

❌ and we see that part doesn't work, so we can look at just that part and spot there is a + where there should not be one and one missing where it should be. The correct part is as follows (again you might want to add some spaces in the text sections.)

This is correct

=IF([Company represented by an attorney]@row = "No", "Yes" + [Name of Company]@row + " has signed contracts with others. The purpose of that contract was" + [What was the purpose of such contracts?]@row + "No the Company was NOT represented by an attorney who advised or consulted on the contract at the time of the agreement.", "other situation")

✔

We can make those changes in the original formula and see if it works.

❌It does not. So we look at part 3. We can then see it is missing a comma after "Unsure" and the same + as in part 2. The correct part 3 is:

=IF([Company represented by an attorney]@row = "Unsure", "Yes" + [Name of Company]@row + " has signed contracts with others. The purpose of that contract was" + [What was the purpose of such contracts?]@row + " " + [Name of Company]@row + "is unsure if they were represented by an attorney who advised or consulted on the contract at the time of the agreement.", "")

✔

When we put them all back together again and include the three closing parenthesis, it works beautifully*.

=IF([Company represented by an attorney]@row = "Yes", "Yes" + [Name of Company]@row + " Has signed contract with others. The purpose of the contract was" + [What was the purpose of such contracts?]@row + " has signed contracts with others. ", IF([Company represented by an attorney]@row = "No", "Yes" + [Name of Company]@row + " has signed contracts with others. The purpose of that contract was" + [What was the purpose of such contracts?]@row + "No the Company was NOT represented by an attorney who advised or consulted on the contract at the time of the agreement.", IF([Company represented by an attorney]@row = "Unsure", "Yes" + [Name of Company]@row + " has signed contracts with others. The purpose of that contract was" + [What was the purpose of such contracts?]@row + " " + [Name of Company]@row + "is unsure if they were represented by an attorney who advised or consulted on the contract at the time of the agreement.", "")))

KPH

Hi @Brittanyy

The easiest way to troubleshoot long formula like this one, and the one from the other day, is to break them down and test each part separately.

Part 1 here is this bit

=IF([Company represented by an attorney]@row = "Yes", "Yes" + [Name of Company]@row + " Has signed contract with others. The purpose of the contract was" + [What was the purpose of such contracts?]@row + " has signed contracts with others. ", "other situation")

✔And you can see this works for the first row and puts "other situation" for the rest.

The only thing you need to change here, is to add a spaces to your text sections such as the "Yes" text at the start to make this "Yes ", and the start of the contract purpose.

Then we can check part 2 which is

= IF([Company represented by an attorney]@row = "No",+ "Yes"+[Name of Company]@row + " has signed contracts with others. The purpose of that contract was"[What was the purpose of such contracts?]@row+ "No, the Company was NOT represented by an attorney who advised or consulted on the contract at the time of the agreement.","other situation")

❌ and we see that part doesn't work, so we can look at just that part and spot there is a + where there should not be one and one missing where it should be. The correct part is as follows (again you might want to add some spaces in the text sections.)

This is correct

=IF([Company represented by an attorney]@row = "No", "Yes" + [Name of Company]@row + " has signed contracts with others. The purpose of that contract was" + [What was the purpose of such contracts?]@row + "No the Company was NOT represented by an attorney who advised or consulted on the contract at the time of the agreement.", "other situation")

✔

We can make those changes in the original formula and see if it works.

❌It does not. So we look at part 3. We can then see it is missing a comma after "Unsure" and the same + as in part 2. The correct part 3 is:

=IF([Company represented by an attorney]@row = "Unsure", "Yes" + [Name of Company]@row + " has signed contracts with others. The purpose of that contract was" + [What was the purpose of such contracts?]@row + " " + [Name of Company]@row + "is unsure if they were represented by an attorney who advised or consulted on the contract at the time of the agreement.", "")

✔

When we put them all back together again and include the three closing parenthesis, it works beautifully*.

=IF([Company represented by an attorney]@row = "Yes", "Yes" + [Name of Company]@row + " Has signed contract with others. The purpose of the contract was" + [What was the purpose of such contracts?]@row + " has signed contracts with others. ", IF([Company represented by an attorney]@row = "No", "Yes" + [Name of Company]@row + " has signed contracts with others. The purpose of that contract was" + [What was the purpose of such contracts?]@row + "No the Company was NOT represented by an attorney who advised or consulted on the contract at the time of the agreement.", IF([Company represented by an attorney]@row = "Unsure", "Yes" + [Name of Company]@row + " has signed contracts with others. The purpose of that contract was" + [What was the purpose of such contracts?]@row + " " + [Name of Company]@row + "is unsure if they were represented by an attorney who advised or consulted on the contract at the time of the agreement.", "")))

Brittanyy

Thanks @KPH for the long explanation. I'm still new to formula's, so this was helpful and I'll try troubleshooting long formula's this way going forward. Thanks again for your assistance.

KPH

No problem and glad it worked. I thought showing you my workings would be more helpful than just giving the answer. Sometimes looking for these pesky commas and plus signs is like searching for a needle in a haystack. If you can break the formula down, at least you are only looking in piles of straw!