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?
Answers

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.

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
Categories
Check out the Formula Handbook template!