Nested IF formula from single select to #


#1

Hi all, I am trying to create a weighted pipeline in one of my bases and getting stuck on the formulas.

I have a single select field Opportunity Status and I’ve created a nested IF formula to return a value based on each status:

IF({Opportunity Status}=“Committed”,“1”,IF({Opportunity Status}=“Convincing”,".8",IF({Opportunity Status}=“Cultivating”,".4",IF({Opportunity Status}=“Engaging”,".1",IF({Opportunity Status}=“Received in Full”,“1”,IF({Opportunity Status}=“Received in Part”,“1”,“0”))))))

The formula itself works, but it’s not recognizing the values as numbers and the format option won’t let me change it. I need the field to be recognized as numbers so that I can make a second formula field that multiplies the Opportunity Amount by the result returned by the first field to show the weighted value (e.g., $100,000 at Opportunity Stage=Convincing should be multiplied by .8 and return $80,000.)

Does anyone know how to do this or another work around?


#2

Hi @Maggie_Raible

Good news - there is a super easy fix for this! Remove the quotation marks around your number values inside your IF statements.

Quotation marks tell Airtable to treat these values as text instead of numbers. By removing those quotes, your numbers will be treated as numeric values and you can then apply mathematical formulas to them.

Try the formula:
IF({Opportunity Status}=“Committed”,1,IF({Opportunity Status}=“Convincing”,.8,0)) …ETC

Hope this helps!


#3

Ahhh, an easy answer - thank you so much @Gareth_Pronovost! Super helpful. Those quotes get me every time :slight_smile: