βMay 13, 2021 01:53 PM
Hi there, I really try my best to work out these formulas, but I have hit a wall now that Iβm having to potentially combine ANDβs and ORβs with my IFβsβ¦
I currently have 2 different fields (working fine) - but I am hoping to combine them (mainly so I have reduce number of fields and also use the 1 field in a roll up calculation.
Below is βessentiallyβ what I am trying to achieve in one field, if anyone is able to provide some guidance on writing the below out correctlyβ¦
IF {Status}= βPre Liveβ OR βActiveβ
AND
{Sale Type}= βTraditionalβ, {List Price}*{Listing % Forecast after Buyer and Auction}IF {Status}= βPre Liveβ OR βActiveβ
AND
{Sale Type}= βAuctionβ, {Reserve Price}*{Listing % Forecast after Buyer and Auction}IF {Status}= βEscrowβ , {Transaction Commission}*{Sold Price}
βMay 13, 2021 02:39 PM
Try this. Iβm assuming that you may have more than 3 options for {Status}. Otherwise, you can simplify the formula more. I wish that I was better at formatting these answers. :slightly_smiling_face:
IF(AND( OR({Status}= βPre Liveβ,{Status}= βActiveβ),
{Sale Type}= βTraditionalβ),
{List Price} * {Listing % Forecast after Buyer and Auction},
IF(AND( OR({Status}= βPre Liveβ,{Status}= βActiveβ),
{Sale Type}= βAuctionβ),
{Reserve Price} * {Listing % Forecast after Buyer and Auction},
IF({Status}=βEscrowβ,{Transaction Commission}*{Sold Price})))
βMay 13, 2021 02:48 PM
Simplified further, the formula would be:
IF(
OR({Status} = 'Pre Live', {Status} = 'Active'),
SWITCH(
{Sale Type},
'Traditional', {List Price}*{Listing % Forecast after Buyer and Auction},
'Auction', {Reserve Price}*{Listing % Forecast after Buyer and Auction}
),
IF(
{Status} = 'Escrow',
{Transaction Commission}*{Sold Price}
)
)
βMay 13, 2021 04:45 PM
When typing multi-line formulas, put three backticks on a single line before and after your text.
```
insert your mult-line formula here
and continue it here
```
You should also check for curly quotes (β
, β
, β
, and β
) and convert them to straight quotes ('
and "
).
βMay 13, 2021 04:57 PM
If {Status} can have only the three possible choices, here is another option with nested IF
s.
IF(
{Status} = 'Escrow',
{Transaction Commission}*{Sold Price},
IF(
{Sale Type} = 'Auction',
{Reserve Price}*{Listing % Forecast after Buyer and Auction},
IF(
{Sale Type} = 'Traditional',
{List Price}*{Listing % Forecast after Buyer and Auction}
)))