Skip to main content
Solved

Nested IF formula from single select to #

  • July 17, 2018
  • 5 replies
  • 37 views
M

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?

Best answer by Gareth_Pronovos

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!

    5 replies

    G

    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!

    M
    • Maggie_Raible
    • Author
    • New Participant
    • July 17, 2018

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

    G
    R
    • Rob_Edwards
    • New Participant
    • October 23, 2019

    Hi! I’m trying to add different outcomes dependent on a single select field (adding days to a date), but I can’t get it to work. I think it’s an issue with brackets. Could anyone please assist?

    IF({Type}=“Form 1”, DATEADD({Notice Sent}, 7, “days”)),IF({Type}=“Form 2”, DATEADD({Notice Sent}, 10, “days”))

    Justin_Barrett
    Forum|alt.badge.img+21

    Hi! I’m trying to add different outcomes dependent on a single select field (adding days to a date), but I can’t get it to work. I think it’s an issue with brackets. Could anyone please assist?

    IF({Type}=“Form 1”, DATEADD({Notice Sent}, 7, “days”)),IF({Type}=“Form 2”, DATEADD({Notice Sent}, 10, “days”))


    Welcome to the community, @Rob_Edwards! :grinning_face_with_big_eyes: You’re correct: it’s an issue with the placement of your parentheses. In short, your version completely closes the first IF function before starting the second. What should happen is that the second should be nested inside the first as the third item in the function format. The basic format is this:

    IF(logical, result_if_true, optional_result_if_false)

    Your second IF should be in that optional third part, which you can get by moving that one parentheses from the middle to the end:

    IF({Type}="Form 1", DATEADD({Notice Sent}, 7, "days"), IF({Type}="Form 2", DATEADD({Notice Sent}, 10, "days")))

    There’s also a slightly easier way to do this using SWITCH, which is designed for just this type of situation where you’re looking at one thing—the contents of {Type} in this case—and switching the output based on what you find there. The basic way of restructuring this using SWITCH would be as follows:

    SWITCH({Type}, "Form 1", DATEADD({Notice Sent}, 7, "days"), "Form 2", DATEADD({Notice Sent}, 10, "days"))

    Even this can be optimized further, though. The only thing that’s really changing is the number of days added. With that in mind, you could nest SWITCH inside a single DATEADD function to change that number:

    DATEADD({Notice Sent}, SWITCH({Type}, "Form 1", 7, "Form 2", 10), "days")
    L
    R
    • Rob_Edwards
    • New Participant
    • October 24, 2019

    Welcome to the community, @Rob_Edwards! :grinning_face_with_big_eyes: You’re correct: it’s an issue with the placement of your parentheses. In short, your version completely closes the first IF function before starting the second. What should happen is that the second should be nested inside the first as the third item in the function format. The basic format is this:

    IF(logical, result_if_true, optional_result_if_false)

    Your second IF should be in that optional third part, which you can get by moving that one parentheses from the middle to the end:

    IF({Type}="Form 1", DATEADD({Notice Sent}, 7, "days"), IF({Type}="Form 2", DATEADD({Notice Sent}, 10, "days")))

    There’s also a slightly easier way to do this using SWITCH, which is designed for just this type of situation where you’re looking at one thing—the contents of {Type} in this case—and switching the output based on what you find there. The basic way of restructuring this using SWITCH would be as follows:

    SWITCH({Type}, "Form 1", DATEADD({Notice Sent}, 7, "days"), "Form 2", DATEADD({Notice Sent}, 10, "days"))

    Even this can be optimized further, though. The only thing that’s really changing is the number of days added. With that in mind, you could nest SWITCH inside a single DATEADD function to change that number:

    DATEADD({Notice Sent}, SWITCH({Type}, "Form 1", 7, "Form 2", 10), "days")

    Amazing Justin! Thank you!

    Terms & ConditionsAccessibility statement