Skip to main content

Hi,


I am new to formulas in Airtable. Basically, I want one field to calculate 3 different formulas, but no matter how I seem to try to “nest” them or anything, I can’t get them to work.


Here is a screenshot of what my table looks like:


I have 3 “Types of Projects”:



  • Apple Videos, Product Videos

  • Mac Vs PC Videos

  • Commercial Videos


I also have a field called “Length”

Basically, I need the following IF formulas all in the one “amount due” field:



  • If “Apple Videos, Product Videos” is chosen, it does the length number multiplied by 0.15 (rounded up to 2 decimal points.

  • If “Mac Vs PC Videos” is chosen, it does the length number multiplied by 0.25 (rounded up to 2 decimal points.

  • If “Commercial Videos” is chosen, it does the length number multiplied by 0.5 (rounded up to 2 decimal points.


Here are the formulas I have (however, for some reason, they all work on their own, except for the “Commercial Videos” one):


IF({Type of Project} = “Apple Videos, Product Videos”) & ROUND({Length} * .15, 2)


IF({Type of Project} = “Mac Vs PC Videos”) & ROUND({Length} * .25, 2)


IF({Type of Project} = “Commercial Videos“) & ROUND({Length} * .50, 2)


All I need is to have them all in the same column, so when I choose the “Type of Project” and then put in a number in the “Length” field, it will populate in the “Amount Due” field.


Thanks!

Hi Mike


Formulae like this are normally done nested - like this:


IF(Type = “Apple Videos / Product Videos”, Length*.15,IF(Type = “Mac vs PC Videos”,Length*.2,IF(Type = “Commercial Videos”,Length*.5,0)))


And here’s a screenshot:



Note, I haven’t put in the rounding - I think it’s unnecessary as I made the format of the formula column currency - you may know better!!


Hope this helps.


Julian

Well, to begin with, you have a problem in the individual formulas: As stated, you’re concatenating a string of either ‘0’ or ‘1’ with the product of {Length} and your per-minute (per-second?) modifier. That ‘&’ should be a comma, like so:


IF({Type of Project} = “Apple Videos, Product Videos”, ROUND({Length} * .15, 2))


That form of the IF() function uses an implied null value for the ‘else’ portion of the function. The previous example with an explicit ‘else’ value would be


IF({Type of Project} = “Apple Videos, Product Videos”, ROUND({Length} * .15, 2),'')


To combine all three IF() functions into a single formula, you simply chain them at the ‘else’ point. (The leading '◌’s are to force indentation; otherwise, the forum will eat leading spaces.):


IF(

◌◌◌{Type of Project} = 'Apple Videos, Product Videos',

◌◌◌ROUND({Length} * .15, 2),

◌◌◌IF(

◌◌◌◌◌◌{Type of Project} = 'Mac Vs PC Videos',

◌◌◌◌◌◌ROUND({Length} * .25, 2),

◌◌◌◌◌◌IF(

◌◌◌◌◌◌◌◌◌{Type of Project} = 'Commercial Videos',

◌◌◌◌◌◌◌◌◌ROUND({Length} * .50, 2),

◌◌◌◌◌◌◌◌◌''

◌◌◌◌◌◌◌◌◌)

◌◌◌◌◌◌)

◌◌◌)


I think that will give you what you want…

Reply


Terms & Conditions