Skip to main content

Hi Everyone,


I’m sure I have done something wrong but new to Airtable…


Trying to do a multiple IF() formula but not working… the simple formula is to generate a rental bond amount depended on two fields… the “Rent Frequency” and the “Rent on Agreement”

(The rental bond has to be equivalent to 4 weeks rent)


What I have been trying is:

IF({Rent Frequency}=“Week”,({Rent on Agreement}*4), IF({Rent Frequency}=“Fortnight”,({Rent on Agreement}*2)), IF({Rent Frequency}=“Month”,{Rent on Agreement}))


Rent Frequency is a single select dropdown for either (Week, Fortnight or Month)


Any guidance would be much appreciated


Thanks

Jason

Hi @Jason_Partridge - you’re nearly there, just a misplaced closing bracket (after “*2”). Try this:


IF(

  {Rent Frequency}='Week',

  ({Rent on Agreement}*4), 

  IF(

    {Rent Frequency}='Fortnight',

    ({Rent on Agreement}*2), 

    IF(

      {Rent Frequency}='Month',

      {Rent on Agreement}

    )

  )

)

JB

Wow, thanks so much @JonathanBowen… you have saved my sanity… I have been over it so many times and cant belive I missed this… thank you


Just on this with future IF() formula’s is there a limit to how many I can nest or include… can you just keep going if needed?


Thanks Again…

Wow, thanks so much @JonathanBowen… you have saved my sanity… I have been over it so many times and cant belive I missed this… thank you


Just on this with future IF() formula’s is there a limit to how many I can nest or include… can you just keep going if needed?


Thanks Again…


There probably is a limit, but I don’t think anyone has ever effectively discovered what it is. I’ve seen some examples of insanely deeply nested functions—easily 50+ levels deep in one case—that work just fine.

@Jason_Partridge you could also try a SWITCH formula which is often easier to understand/debug than a nested IF statement if you have many nested levels:


SWITCH(

  {Rent Frequency}, 

  'Week', {Rent on Agreement} * 4, 

  'Fortnight', {Rent on Agreement} * 2, 

  'Month', {Rent on Agreement}

)

JB

That’s AWESOME works great… so its best to use this one when I have a choice of outcomes…


Thanks again helps me so much…

Reply


Terms & Conditions