# Weighted Forecast

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?

• 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.

• 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!