Skip to main content

Hi everyone,



We have updated our service pricing in October and I’m trying to update the calculation for services rendered after 10/1. In the below code {Direct Time} is {Direct TIme} + {Indirect Time}. Is it possible to use calculations like this within a nested IF statement?



(IF


AND(


{FY}=“22”,


{MM}>=“10”,


({Total Time} * {Rate by FY by MM})+{Pass through Materials},


({Direct Time} * {Rate by FY by MM})+{Pass through Materials},


)


)



Best,



Ben

Hi,



it seems like something not OK in your syntax


assuming you want something like


IF (Statement1 AND Statement2 AND Statement3) then X else Y,


in your text, there are 4 statements and no X and Y


it should be written as (added more linebreaks to highlight statement part):









IF(





AND(


Statement1,


Statement2,


Statement3


),





X,


Y


)

@Alexey_Gusev Thanks, you were correct on the syntax.



However, even with this corrected the calculation does not work. I can return simple text strings, however can’t get it to perform calculation A if the and conditions are met, else B if not.


Ex:


IF(


AND(


{FY}=“22”,


{MM}=“10”


),


“A”,


“B”


)


^^this works


IF(


AND(


{FY}=“22”,


{MM}>=“10”


),


{Total Time} * {Rate by FY by MM}+{Pass through Materials},


{Direct Time} * {Rate by FY by MM}+{Pass through Materials}


)


^^ this does not.





  1. Can anyone confirm if it is possible to do conditional calculations?


  2. If not possible could i do the calculations in different fields and create a conditional to pull one of these values based on the parameters?


And the answer is 1. no, 2. yes.



IF(


AND(


{FY}>=“22”,


{MM}>=“10”


),


{Post 10/2021},{Pre 10/2021})

And the answer is 1. no, 2. yes.



IF(


AND(


{FY}>=“22”,


{MM}>=“10”


),


{Post 10/2021},{Pre 10/2021})


For numeric comparisons, the numbers that you’re comparing against should not be in quotes. Airtable will sometimes auto-convert strings to numbers for mathematical operations, but it’s not something that should be relied upon.





If {FY} and {MM} are numeric fields (i.e. fields that output numeric values), try this instead:



IF(


  AND(


    {FY} = 22,


    {MM} >= 10


  ),


  {Total Time} * {Rate by FY by MM} + {Pass through Materials},


  {Direct Time} * {Rate by FY by MM} + {Pass through Materials}


)


If those fields output strings, use the VALUE() function to convert them to numbers before comparison, like this:



IF(


  AND(


    VALUE({FY}) = 22,


    VALUE({MM}) >= 10


  ),


  {Total Time} * {Rate by FY by MM} + {Pass through Materials},


  {Direct Time} * {Rate by FY by MM} + {Pass through Materials}


)


This could also be simplified a bit. Because all that you’re changing is the first operand in the “inner” formula—{Total Time} vs {Direct Time}—the overall formula could be written like this (using the second VALUE()-driven version as an example):



IF(


  AND(


    VALUE({FY}) = 22,


    VALUE({MM}) >= 10


  ),


  {Total Time}, {Direct Time}


) * {Rate by FY by MM} + {Pass through Materials}

Reply


Terms & Conditions