Weighted Forecast

Sarah Dawson

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?

Mark Cronk

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

Sarah Dawson

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.