Stacking 3 formulas in one column

[Mike_Vannelli]

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!

[Julian_Kirkness]

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

[W_Vann_Hall]

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…