Please Help!

I'm creating a CRM, I have 2 columns. One is the Sales stage with column properties (drop-down list) and the other is a probability% column with drop-down options.

I want a formula that will add the percentage from the drop-down options based on the sales stage that's selected.


Example if '1. Not Qualified' is chosen, I want 10% to be automatically added into the Probability% cell.


I'm really struggling, it would be so great if someone could help me with the exact formula I should be using.


Thanks in advance.


Warm regards

Alycia Osborne

General Manager

m: 0402 111 411

e: [email protected]

Tags:

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Alycia Osborne

    Here's the start of your IF formula

    =IF([SALES STAGE]@row="1. Not Qualified", "10%")

    If you help me understand how your other stages correlate with your probabilities, I'll help you build your nested IF statement

    cheers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/22/21 Answer ✓

    Hi @Alycia Osborne,

    Hope you are fine, please try the following formula.

    =IF([SALES STAGE]@row = "Not Qualified", "10%", IF([SALES STAGE]@row = "Investigate", "15%", IF([SALES STAGE]@row = "Intro/Qualify", "30%", IF([SALES STAGE]@row = "Define Needs", "50%", IF([SALES STAGE]@row = "Meet/Demo", "75%", IF([SALES STAGE]@row = "Contract/Offer", "90%", IF([SALES STAGE]@row = "Win", "100%")))))))

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Alycia Osborne

    Here's the start of your IF formula

    =IF([SALES STAGE]@row="1. Not Qualified", "10%")

    If you help me understand how your other stages correlate with your probabilities, I'll help you build your nested IF statement

    cheers

  • Thanks KDM for your reply.


    I tried it but it doesn't work. How do I get the formula to register all of the values in my dropdown lists?


    The only 2 columns I want to automate is the Sales Stage column and the Probability % Column.

    So if a sales rep selects one of the options from the dropdown menu s the probability percentage will automatically change.

    The way I want to have the percentages link with the sales stage: (I changed the dropdown options a little).

    If 1. Not Qualified was selected that would = 10%

    If 2. Investigate was selected that would = 15%

    If 3. Intro/Qualify was selected that would = 30%

    If 4. Define Needs was selected that would = 50%

    If 5. Meet/Demo was selected that would = 75%

    If 6. Contract/Offer was selected that would = 90%

    If Win was selected that would = 100%

    Loss 0%


    Does that help you with the formula?


    Thank you so much, I really appreciate your help 😀

    Warm regards

    Alycia Osborne

    General Manager

    m: 0402 111 411

    e: [email protected]

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/22/21 Answer ✓

    Hi @Alycia Osborne,

    Hope you are fine, please try the following formula.

    =IF([SALES STAGE]@row = "Not Qualified", "10%", IF([SALES STAGE]@row = "Investigate", "15%", IF([SALES STAGE]@row = "Intro/Qualify", "30%", IF([SALES STAGE]@row = "Define Needs", "50%", IF([SALES STAGE]@row = "Meet/Demo", "75%", IF([SALES STAGE]@row = "Contract/Offer", "90%", IF([SALES STAGE]@row = "Win", "100%")))))))

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • THANK YOU SO MUCH! It works perfectly.


    Much appreciated 😃

    Warm regards

    Alycia Osborne

    General Manager

    m: 0402 111 411

    e: [email protected]

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Alycia Osborne 

    Happy to help you any time, you are welcome

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Raising this one again as I'm having a similar issue. Followed your formula to fit my needs, and receive unparseable-

    =IF([Recruitment Status]@row = "Active", "100%", IF([Recruitment Status]@row = "Attempting Contact", "1%", IF([Recruitment Status]@row = "Contacted", "10%", IF([Recruitment Status]@row = "Contract Sent", "85%", IF([Recruitment Status]@row = "Declined", "1%", IF([Recruitment Status]@row = "Pending Credentialing", "90%", IF([Recruitment Status]@row = "Reviewing Rates", "50%", IF([Recruitment Status]@row = “Unqualified”, “1%”))))))))


    Somehow the nesting is the issue because a single IF is no issue in row 1.

    =IF([Recruitment Status]@row = "Active", "100%")


    Any help is greatly appreciated! Thank you!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Scott Prater

    Please try the following formula:

    =IF([Recruitment Status]@row = "Active", "100%", IF(OR([Recruitment Status]@row =
    "Attempting Contact", [Recruitment Status]@row = "Unqualified"), "1%", IF([Recruitment Status]@row
    ="Contacted", "10%", IF([Recruitment Status]@row = "Contract Sent", "85%",
    IF([Recruitment Status]@row = "Declined", "1%", IF([Recruitment Status]@row =
    "Pending Credentialing", "90%", IF([Recruitment Status]@row = "Reviewing Rates", "50%")))))))
    

    the following screenshot shows the result:


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!