Need help writing a complex IF(FIND) formula for a Mood Tracker

[Ciaran_Duffy]

Hi all,

I’m using Airtable to create a mood tracker. I have a form question which asks which of the following 20 emotions I’m feeling, multiple answers = ok.

:green_square: Balanced
:green_square: Calm
:green_square: Cozy
:green_square: Loving
:green_square: Fulfilled
:yellow_square: Energised
:yellow_square: Focused
:yellow_square: Happy
:yellow_square: Inspired
:yellow_square: Motivated
:blue_square: Bored
:blue_square: Drained
:blue_square: Lonely
:blue_square: Sad
:blue_square: Tired
:red_square: Angry
:red_square: Anxious
:red_square: Hungover
:red_square: Restless
:red_square: Stressed
:red_square: Tense

What’d I’d like to achieve then is to have a pie chart look at these emotions and display an ‘average’ with each of the 4 colours. My plan was to use IF(FIND) to create a kind of mood average column in the database to direct the pie chart to, but I’m having a bit of difficulty writing the script.

I was going down the track of:

   IF
   ( FIND(
        'Balanced', Mood
        ),
    FIND(
        'Calm', Mood
        ),
    FIND(
        'Cozy', Mood
        ),
    FIND(
        'Loving', Mood
        ),
        'Green',
        'Not green'
    )

But then I don’t think I can nest FIND objects inside the IF statement like this. Could anybody help me, please? Am I on the right track?

[ScottWorld]

Welcome to the community, @Ciaran_Duffy!

Yes, you can nest, but in Airtable, you are required to keep repeating the IF() function over & over again, like this:

IF(
FIND('Balanced',Mood),'Green',
IF(
FIND('Calm',Mood),'Green',
IF(
FIND('Cozy',Mood),'Green',
IF(
FIND('Loving',Mood),'Green',
'Not Green'
))))

[Ciaran_Duffy]

That set me on the right path! I’ve got it now, thanks Scott!

[Jeremy_Oglesby]

Alternatively, you could use the OR() logical function, like this:

IF(
  OR(
    FIND('Balanced', Mood),
    FIND('Calm', Mood),
    FIND('Cozy', Mood),
    FIND('Loving', Mood)
  ),
  'Green',
  'Not Green'
)

[Justin_Barrett · Accepted solution]

Yet another possibility is to use regular expressions. Because multiple answers can apply, you can concatenate everything together instead of using nested functions:

IF(REGEX_MATCH(Mood, "Balanced|Calm|Cozy|Loving|Fulfilled"), "🟩") &
IF(REGEX_MATCH(Mood, "Energised|Focused|Happy|Inspired|Motivated"), "🟨") &    
IF(REGEX_MATCH(Mood, "Bored|Drained|Lonely|Sad|Tired"), "🟦") &
IF(REGEX_MATCH(Mood, "Angry|Anxious|Hungover|Restless|Stressed|Tense"), "🟥")

[ScottWorld]

Thanks for the refinement, @Jeremy_Oglesby! This is much cleaner than my formula!

[ScottWorld]

Ooh, @Justin_Barrett, that’s cool!