I have a formula in the Table Builder that has multiple IF/AND statements, I have a feeling it may have exceeded the character length limitations,it has 303 characters. Is there anyway to compress this or any other method to make it work. The formula works outside of Table Builder but of course you are using cell numbers rather than the cell description? I could reduce the length of column descriptions but just wanted to see if there was another way? Thanks.

This is the formula:

=IF(AND([@[Location Code]]="",[@[Transaction Type]]="EVTYPE31"),"Virtual",IF(AND([@[Location Code]]<>"",[@[Transaction Type]]="EVTYPE31"),"Hybrid",IF(AND([@[Location Code]]<>"",[@[Transaction Type]]<>"EVTYPE31"),"Participation",IF(AND([@[Location Code]]="",[@[Transaction Type]]<>"EVTYPE31"),"Participation"))))

#### 1 comment

• j2associates

Hello Robert,

I believe you can refactor your formula and make it a lot less bulky.

=IF                                                  A
AND(
[@[Location Code]]="",
[@[Transaction Type]]="EVTYPE31"
),
"Virtual",
IF(                                               B
AND(
[@[Location Code]]<>"",
[@[Transaction Type]]="EVTYPE31"
),
"Hybrid",
IF(                                            C
AND(
[@[Location Code]]<>"",
[@[Transaction Type]]<>"EVTYPE31"
),
"Participation",
IF(                                         D
AND(
[@[Location Code]]="",
[@[Transaction Type]]<>"EVTYPE31"
),
"Participation"))))

You have 4 compound IF statements with all of the combinations of Transaction Type and Location Code. Let's combine them differently and see what happens:

=IF(
[@[Transaction Type]]="EVTYPE31",
IF([@[Location Code]]="","Virtual","Hybrid"),
"Participation"
)

In the above case we test Transaction Type = EVTYPE31 and then test the location code for Virtual or Hybrid. Otherwise, if the Transaction Type is not equal to EVTYPE31, it is Participation.

If perhaps the double Participation in your original format was an oversight and they should be separate values, then another IF is embedded in a similar manner. In the following, I changed it to Participation1 and Participation2.

=IF(
[@[Transaction Type]]="EVTYPE31",                                    IF Test
IF([@[Location Code]]="","Virtual","Hybrid"),                           True
IF([@[Location Code]]="","Participation1","Participation2")     False
)

Good luck!

Edited by j2associates