Help with an IF Formula... 😭

Elizabeth_Aucti · ‎May 13, 2021

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}

augmented · ‎May 13, 2021

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})))

Kamille_Parks · ‎May 13, 2021

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}
   )
)

kuovonne · ‎May 13, 2021

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 ").

kuovonne · ‎May 13, 2021

If {Status} can have only the three possible choices, here is another option with nested IFs.

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}
)))