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

Topic Labels: Formulas
Solved
1221 6
cancel
Showing results for
Did you mean:
4 - Data Explorer

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: 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?

1 Solution

Accepted Solutions
18 - Pluto

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, "Angry|Anxious|Hungover|Restless|Stressed|Tense"), "🟥")
``````

6 Replies 6
18 - Pluto

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'
))))
``````
4 - Data Explorer

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

14 - Jupiter

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'
)
``````
18 - Pluto

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"), "🟨") &