Skip to main content

I have a forecasting sheet pulled from our CRM. The sheet lists various deals underway, each sitting at one of 8 stages:





  1. Contract Negotiation


  2. Terms Negotiation


  3. Proposal - Sent


  4. Proposal - In Progress


  5. Evaluating and Planning


    a) Evaluating and Planning


    b) Proposal Sent


    c) Contract Negotiations




Our CRM applies a probability of closing at each stage, but doesn’t place that in any column that I can export, so I have added a formula field to apply that.



(Pic 1)




As part of our forecasting process, our BDs will review the deals and if the CRM assigned probability is different than they think it should be, they can enter that. A percentage field called {Probability per BD}



Lastly, I have a field column to display the CRM % or overwrite it with the BD’s %.



(pic2)





Now…here is where my issue is:



Part of our exercise as well is to ensure that if the deal is over 50% probability of closing, AND over $50K in value, They need to establish what the start and finish date is over the contract is after closing, so we can spread the value of that contract over that period in our forecasting.



I’ve figured that in the interface I am building, I will have a field that displays “Please Assign Start + Finish Date” when those conditions are met or “ ✔ ” otherwise.



Because there are two conditions (Value and 😵 and the Percentage value is itself generated from 2 other fields… I am struggling to figure out how to get my nested IF functions to work.





  • I’ve created one field that if the value is >50K, the value displayed is ‘1’


  • I figure if I have another field that if the % is over 50, too a ‘1’ is displayed.


  • In a third field, I will have a conditional statement IF(A+B=2, “Please Assign Start + Finish Date”, “ ✔ ”)




However… the formulas where our BD enter there percentages, complicates things I believe and the IF function of that second bullet isn’t working



(Pic 3)




Im confident there is a simple overcomplication or error, I just can’t figure out what it is.



Thank you in advance for your assistance.



(I’ve shared the text here below from the three pictures so as to aid anyone that is kind enough to try and help me)



Pic 1 Formula Text:


IF(Stage=“5) Contract Negotiation”,90&“%”,IF(Stage=“4) Terms Negotiation”, 70&“%”,IF(Stage=“c) Contract Negotiations”,90&“%”,IF(Stage=“3) Proposal - Sent”,20&“%”,IF(Stage=“b) Proposal Sent”, 20&“%”,IF(Stage=“2) Proposal - In Progress”, 10&“%”,IF(Stage=“a) Evaluating and Planning”,5&“%”,IF(Stage=“1) Evaluating and Planning”,5&“%”))))))))



Pic 2 Formula Text:


IF({Probability per BD}=0, {Probability per Stage},ROUND((100*{Probability per BD}),0)&“%”)



Pic 3 Formula Text:


IF(Probability<=.5, 0, IF(Probability<=1, 1, IF(Probability<50, 0,IF(Probability >=50, 1))))

Your formulas are not actual percentages, they’re text strings that look like percentages. What you want to do is remove all the “%” and simply format the number results as percentages using the formatting tab. Also, percentages are stored as decimal values, not whole numbers.





  1. {Probability per Stage}




SWITCH(


{Stage},


"5) Contract Negotiation", .90


"4) Terms Negotiation", .70,


"c) Contract Negotiations", .90,


"3) Proposal - Sent", .20


"b) Proposal Sent", .20,


"2) Proposal - In Progress", .10,


"a) Evaluating and Planning", .5


"1) Evaluating and Planning", .5


)




  1. {Probability}




IF(


{Probability per BD}=0, 


{Probability per Stage},


ROUND((100*{Probability per BD}),0)


)


^ You may be able to remove the multiplying by 100 bit/rounding if all your fields are proper percentages.





  1. last formula, simplified.


    This one is confusing because your formula asks “Is Probability less than 1 but more than .5” twice. I imagine this was supposed to be two different fields?




IF(


  AND(


    {Probability} > .5,


    {Name of other Field} > 50


  ),


  "Please Assign Start + Finish Date", 


  "✔️"


)

Your formulas are not actual percentages, they’re text strings that look like percentages. What you want to do is remove all the “%” and simply format the number results as percentages using the formatting tab. Also, percentages are stored as decimal values, not whole numbers.





  1. {Probability per Stage}




SWITCH(


{Stage},


"5) Contract Negotiation", .90


"4) Terms Negotiation", .70,


"c) Contract Negotiations", .90,


"3) Proposal - Sent", .20


"b) Proposal Sent", .20,


"2) Proposal - In Progress", .10,


"a) Evaluating and Planning", .5


"1) Evaluating and Planning", .5


)




  1. {Probability}




IF(


{Probability per BD}=0, 


{Probability per Stage},


ROUND((100*{Probability per BD}),0)


)


^ You may be able to remove the multiplying by 100 bit/rounding if all your fields are proper percentages.





  1. last formula, simplified.


    This one is confusing because your formula asks “Is Probability less than 1 but more than .5” twice. I imagine this was supposed to be two different fields?




IF(


  AND(


    {Probability} > .5,


    {Name of other Field} > 50


  ),


  "Please Assign Start + Finish Date", 


  "✔️"


)

You Rock! Thank you!! I wasnt aware of the SWITCH formula!



Should anyone else find this and it serve them, I will only point out some missing commas in the formula.



Thanks again!!! :facepunch:



Reply


Terms & Conditions