Multiple IF Statements - Help Needed

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

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

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.

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

{Contract value} * VALUE({Probability})

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

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.