Weighted Forecast

Options

Hi, I'm not great with formulas but need to create a formula for a weighted forecast. In one column I have a $ value. In another column for probability I have single select drop down with options such as "20% - Bidding", "30% - Shortlisted" etc. I need the weighted forecast column to apply an IF calculation that multiples the $ value by the % value of the probability column.

Example: If Probability = "20% - Bidding" multiply the $ by .20. Alternatively if Probability = "30% - Shortlisted" multiply the $ by .30.

Result would be: $1 x .20 = $0.20 of $1 x .30

How do i write this formula?

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Sarah Dawson ,

    You'll need a nested IF. Try:

    =IF(CONTAINS("20%", Probability@row), 0.2* value@row, IF(CONTAINS("30%", Probability@row), 0.3* value@row,......)))

    Substitute my probability and value for your actual coming names. Continue building the nexted IF for each drop down option. End the formula with the same number of ) as you have IFs.

    Help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Sarah Dawson
    Options

    Hi @Mark Cronk i have added:

    =IF(CONTAINS("1% Investigating Lead", [Probability]), 0.01* [Forecast Revenue Amount], IF(CONTAINS("5% Confirmed Lead", [Probability])), 0.05* [Forecast Revenue Amount], IF(CONTAINS("20% Bidding", [Probability]), 0.2* [Forecast Revenue Amount], IF(CONTAINS("30% Shortlisted", [Probability])), 0.3* [Forecast Revenue Amount], IF(CONTAINS("70% Highly Likely", [Probability])), 0.7* [Forecast Revenue Amount], IF(CONTAINS("90% Negotiation", [Probability])), 0.9* [Forecast Revenue Amount] IF(CONTAINS("100% Confirmed", [Probability]), 1* [Forecast Revenue Amount], )))

    but get unparsable.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!