If / And Formula Help

Well, here’s the formula you asked for — but i suspect it’s not really what you need. (Are each of the components really optional? For instance, you would have a variant name that has no main or secondary color, but does have a finish?)

IF(
    {Main Color},
    {Main Color}
    )&
IF(
    {Secondary Color},
    IF(
        {Main Color},
        ', '&{Secondary Color},
        {Secondary Color}
        )
    )&
IF(
    {Finish},
    IF(
        OR(
            {Main Color},
            {Secondary Color}
            ),
        ', '&{Finish},
        {Finish}
        )
    )

I suspect what you’re looking for is more like this:

IF(
    {Main Color},
    {Main Color}&
    IF(
        {Secondary Color},
        ', '&{Secondary Color}
        )&
    IF(
        {Finish},
        ', '&{Finish}
        )
    )

This second formula will give you

Main Color
Main Color, Secondary Color
Main Color, Secondary Color, Finish
Main Color, Finish

IF(
    {Main Color},
    {Main Color}
    )&
IF(
    {Secondary Color},
    IF(
        {Main Color},
        ', '&{Secondary Color},
        {Secondary Color}
        )
    )&
IF(
    {Finish},
    IF(
        OR(
            {Main Color},
            {Secondary Color}
            ),
        ', '&{Finish},
        {Finish}
        )
    )

IF(
    {Main Color},
    {Main Color}&
    IF(
        {Secondary Color},
        ', '&{Secondary Color}
        )&
    IF(
        {Finish},
        ', '&{Finish}
        )
    )

Main Color
Main Color, Secondary Color
Main Color, Secondary Color, Finish
Main Color, Finish

Thanks so much!! Will put it to test in the morning. Yes some products are made of materials that only have finish, not main or secondary colors, (Cast aluminum)

Thanks a million!!

In that case, you might want to try the following. This will give you the four materials supported by the second formula above as well as a finish-only material. (It won’t allow a material with a secondary but no main color; if that’s a valid choice — a manufacturing version of ‘spot color,’ perhaps — you’ll need to use the original formula.)

IF(
    {Main Color},
    {Main Color}&
    IF(
        {Secondary Color},
        ', '&{Secondary Color}
        )
    )&
    IF(
        {Finish},
        IF(
            {Main Color},
            ', '&{Finish},
            {Finish}
            ),
        )

IF(
    {Main Color},
    {Main Color}&
    IF(
        {Secondary Color},
        ', '&{Secondary Color}
        )
    )&
    IF(
        {Finish},
        IF(
            {Main Color},
            ', '&{Finish},
            {Finish}
            ),
        )

Worked great. Thanks SO much for your help.

Hi!
I have a problem really similar to this and though I can see that this has the solution I still manage to tangle myself somewhere in the formula. I weight the pros and cons of writing here vs. starting a new topic and I hope I made the right decision.

I want my primary field to display info from five fields; {Nickname}, {First Name}, {Last Name}, {Maiden Name} and {Title}

The fields {Nickname}, {Maiden Name} and {Title} are conditional and I only want them to display if not blank. No empty spaces in the output.

I would like outputs like below;
Nickname Last Name
Nickname Last Name (neé Maiden Name)
Nickname Last Name (neé Maiden Name) [Title]
First Name Last Name
First Name Last Name (neé Maiden Name)
First Name Last Name (neé Maiden Name) [Title]

I’ve managed to get {Nickname}, {First Name}, {Last Name} and {Title} to work, though likely not in a very elegant way, but get tangled on the {Maiden Name} part. As I’m relatively new to Airtable and this is my first post I would be beyond greatful for help with this.

Hi @momentsgoneby80 - something like this?

IF(Nickname, Nickname, {First Name}) & ' ' & {Last Name} & IF({Maiden Name}, ' (neé ' & {Maiden Name} & ')', '') & IF(Title, ' [' & Title & ']', '')

JB

Thank you so much @JonathanBowen, that worked wonders.
Far more elegant than my attempts and far better - It works!

Now I’m just gonna study that and figure out where and why I tripped myself up so I don’t repeat it at a later date.

I always find it easier to build it up step by step rather than attempting to do this whole thing in one go. So I first did “if there’s a nickname, show the nickname, else show the first name”. Then added last name. Then added “if there’s a maiden name…” and so on.

JB

I know that an IF formula can only delivery a true/false answer, but I was wondering if there is another formula that delivers multiple values if you select multiple answers in a multi-select fields.

I have a {Product Size} multi-select category thats gives you the option to select Option 1, Option 2, Option 3, and/or Option 4. I would like the a {Imprint Size} category that will show the variety of imprints needed.

Option 1 = 10 inches
Option 2 = 15 inches
Option 3 = 20 inches
Option 4 = 10 inches

As you can see some options equal the same amount.

Example: If Option 2 and Option 3 are selected I want my Imprint Size formula category to read “15 inches, 20 inches.”

I have been trying to figure this out for a few hours now and have tried a few different formulas but I can’t figure out how to deliver different answers.

Thanks!

See my reply here:

@JonathanBowen Sorry for the late reply; it’s been one of those weeks.
I’m unfortunately still too new to Airtable, and more so formulas, that I’m not familliar with all the options and what they do. I find that once you get more familliar with code, any code as this applies to more than just AIrtable formulas, you can code from scratch, but until den you’re mostly finding and adapting existing code while learning. At least I’m like that when it comes to code/formulas and I’m just not good enough to get it all right on my own when starting from scratch. I’ll get there though.
I find I do your kind of thinking though when trying to adapt a formula to fit my needs.

I’ve often thought that it’d be great to be one of those that can learn and apply info from simply reading. Unfortuanately, or maybe fortunately dependeing on your outlook, I’m one of these that need to learn through application which then lead me to forums where I read thread upon thread in search of what I need. I have a bunch of testbases to try the different things I come across on my way there. Maybe that’s just how I work.

MGB

This really helped, thank you! Heads up for anyone trying to do something similar but you have simpler needs and just want to combine a Title (if present), First Name, Last Name, and Suffix (if present), the formula I came up with based on Jonathan’s great post is:

IF({Formal Title}, {Formal Title} & ’ ’ & {First Name}, {First Name})
& ’ ’
& {Last Name}
& IF({Suffix}, ', ’ & {Suffix})

So if you have someone named John Smith with no formal title or suffix, it’d just show “John Smith”

Alternatively, if you had someone named Jane Doe who was a Dr. (in title field) with a PhD (in suffix field), it would show “Dr. Jane Doe, PhD”

If you had someone with a title of Mr. then it’d look like “Mr. John Smith” and if alternatively just a suffix would look like “Jane Doe, RN”