Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

# Calculations within nested IF AND statements?

Topic Labels: Formulas
405 4
cancel
Showing results for
Did you mean:
4 - Data Explorer

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

4 Replies 4
10 - Mercury

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
)
``````
4 - Data Explorer

@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?
4 - Data Explorer

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}
``````