Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
Oct 14, 2022 09:34 PM
I have a forecasting sheet pulled from our CRM. The sheet lists various deals underway, each sitting at one of 8 stages:
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 “ :heavy_check_mark: ” 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.
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))))
Solved! Go to Solution.
Oct 15, 2022 07:41 AM
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.
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
)
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.
IF(
AND(
{Probability} > .5,
{Name of other Field} > 50
),
"Please Assign Start + Finish Date",
"✔️"
)
Oct 15, 2022 07:41 AM
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.
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
)
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.
IF(
AND(
{Probability} > .5,
{Name of other Field} > 50
),
"Please Assign Start + Finish Date",
"✔️"
)
Oct 15, 2022 12:08 PM
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: