This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Need help with creating a conditional formula with...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

1
931
2

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

- Contract Negotiation
- Terms Negotiation
- Proposal - Sent
- Proposal - In Progress
- 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 “ :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.

- 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”, “ :heavy_check_mark: ”)

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

Reply

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

- {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
)
```

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

- 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",
"✔️"
)
```

Reply

2 Replies 2

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

- {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
)
```

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

- 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",
"✔️"
)
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

Reply