Skip to main content

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}


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


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

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}

   )

)

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


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



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

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}

)))

Reply


Terms & Conditions