Formula Help Please!

Oren_Tal · ‎Sep 05, 2022

Hello! I’m hoping someone can help me with a nested if statement. I am trying to list a specific price for a product or combination of products. Airtable keeps telling me that I have an invalid formula, please check formula text. Can anyone see anything specific here that needs to change?

IF
(Foursome=TRUE(), 59.95,
IF(Twosome=TRUE(), 39.95,
IF(AND(IB=“IB”, HH=“HH”), 45.90,
IF(OR(IB=“IB”, HH=“HH”), 22.95,
IF(OR(EP=“EP”, EP2=“EP2”), 11.95,
IF(AND((IB=“”, HH=“”), (AND(EP=“EP”, EP2=“EP2”))), 23.90,
IF(AND((OR(IB=“IB”, HH=“HH”), (OR(EP=“EP”, EP2=“EP2”)))), 34.90
IF(AND((OR(IB=“IB”, HH=“HH”), (AND(EP=“EP”, EP2=“EP2”)))), 46.85, 0))))))))

Thank you in advance for your help!!

kuovonne · ‎Sep 05, 2022

Welcome to the Airtable community!

Formulas can be tricky. Here are couple of helps:

quotes need to be straight quotes " and not curly “ ”
the opening parenthesis of a function needs to immediately follow the function name with no white space. You follow this on most of your IF functions, but not the very first one.

See if these two things help. If not, post back with your result. Sometimes there are multiple issues.

Oren_Tal · ‎Sep 05, 2022

Still getting an error. I looked at the Airtable knowledge base as well and enclosed all field references in curly brackets - although it didn’t seem to matter when I had only the first four lines up from the previous entry above, the formula worked then. Also changed all quotes to single straight quotes (They only come through here as curly).

IF({Foursome}=TRUE(), 59.95,
IF({Twosome}=TRUE(), 39.95,
IF(AND({IB}=‘IB’, {HH}=‘HH’), 45.90,
IF(OR({IB}=‘IB’,{HH}=‘HH’), 22.95,
IF(OR({EP}=‘EP’, {EP2}=‘EP2’), 11.95
IF(AND({IB}=‘’, {HH}=‘’, AND({EP}=‘EP’, {EP2}=‘EP2’)), 23.90,
IF(AND(OR({IB}=‘IB’, {HH}=‘HH’), OR({EP}=‘EP’, {EP2}=‘EP2’)), 34.90
IF(AND(OR({IB}=‘IB’, {HH}=‘HH’), AND({EP}=‘EP’, {EP2}=‘EP2’)), 46.85, 0))))))))

kuovonne · ‎Sep 05, 2022

You can use the “Preformated text” icon in the forum editor to format your formula code to make your formula look better.

It looks like you are missing some commas at the end of your lines, specifically the ones ending in 11.95 and 34.90.

I also recommend splitting your formula across more lines to make the nested conditions easier to read.

IF(
    {Foursome}=TRUE(), 
    59.95,
IF(
    {Twosome}=TRUE(), 
    39.95,
IF(
    AND({IB}='IB', {HH}='HH'), 
    45.90,
IF(
    OR({IB}='IB', {HH}='HH'), 
    22.95,
IF(
    OR({EP}='EP', {EP2}='EP2'), 
    11.95,
IF(
    AND(
        {IB}='', 
        {HH}='', 
        {EP}='EP', 
        {EP2}='EP2'
    ), 
    23.90,
IF(
    AND(
        OR({IB}='IB', {HH}='HH'), 
        OR({EP}='EP', {EP2}='EP2')
    ), 
    34.90,
IF(
    AND(
        OR({IB}='IB', {HH}='HH'), 
        {EP}='EP', 
        {EP2}='EP2'
    ), 
    46.85, 
    0
))))))))

Oren_Tal · ‎Sep 06, 2022

Thank you so much! That helps a lot. One more question though, is the order of IF statements important? Some of my calculations in the sheet are not correct. Specifically, the calculations that include the different combinations. Should I be checking the conditions of the most complicated one first? and then start eliminating products?

kuovonne · ‎Sep 06, 2022

The order is very important!

The IF statements are evaluated in order and the first condition that is true determines the actual value.

This sometimes means that unlikely conditions and complicated conditions need to be placed earlier in the list. But this is not always the case. Thinking through the logic is often the hardest part of writing a formula (versus getting the punctuation right).