Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Multiple IF Statements - Help Needed

Solved
Jump to Solution
1002 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Matt_Engage
5 - Automation Enthusiast
5 - Automation Enthusiast

Please could somebody help me troubleshoot this formula?

I’m building a financial forecast and have a number of new business opportunities to build in. Not all opportunities have the same probability of converting into a sale however so I need to create a weighted value for each opportunity to give me a more realistic view of what my pipeline value is. The calculation needs to be Contract Value x Probability = Weighted Value, however I’m struggling to work out how I incorporate the multiple options into my formula.

I have 6 probability options in total:

  • 100%
  • 75%
  • 50%
  • 30%
  • 10%
  • Lost (0%)

If anyone could offer some advice, that would be much appreciated! Thank you.

Screenshot 2022-01-25 at 16.32.19

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

Try using the VALUE() function to convert your single select into a number:

{Contract value} * VALUE({Probability})

See Solution in Thread

3 Replies 3
JonathanB
8 - Airtable Astronomer
8 - Airtable Astronomer

You might want to try using a percentage field for {Probability} instead of single select.

image

Your formula in {Weighted value} would then just be {Contract value}*{Probability}

If you still need {Probability} to be a single select field (so that users can only enter pre-set options, instead of any figure, you can do this and then just add a formula field that renders {Probability} as a number, and use that for your formula. Airtable might always recognise the content of single select fields as actual numbers that can be used in calculations.

Kamille_Parks
16 - Uranus
16 - Uranus

Try using the VALUE() function to convert your single select into a number:

{Contract value} * VALUE({Probability})
Matt_Engage
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you both for your help! @Kamille_Parks the formula you’d recommended worked first time :grinning_face_with_smiling_eyes: