Help

Re: Multiple IF() formula but not working

679 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jason_Partridge
4 - Data Explorer
4 - Data Explorer

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

5 Replies 5

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

Jason_Partridge
4 - Data Explorer
4 - Data Explorer

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

Jason_Partridge
4 - Data Explorer
4 - Data Explorer

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…