Need help with IF AND in formula :(

Matej_Mudrik · June 5, 2022, 6:44pm

hello, i am trying to do some basic count using formula (i think its basic).
there are 5 columns (named “name”,“rozmer1_cm”,“rozmer2_cm”,rozmer3_cm" and “vaha_kg”)

what i want to do is formula, which will get those results:
if all “rozmerX” < 50 and vaha_kg * 1000 <250, than its “M1”
have found it to make it this way:

which is working fine.
BUT than i need to do
if all “rozmerX” < 50 and vaha_kg * 1000 >250 and <=500, than its “M2”
there are many other conditions, but i cannot go over the condition “vaha_kg*1000 >250 and <=500”

have read the basics of formula, but i cannot apply it to my case (i know, its stupid).
Thanks for kicking in right direction

kuovonne · June 5, 2022, 8:44pm

There are many ways to write a formula for the conditions you have stated. However, there is no way of knowing which will be easiest to add in other conditions.

In situations where there are multiple possible conditions, I often find it helpful to make a table showing all possible combinations and their resulting value. Then I look for patterns to combine into a formula.

Another option is to build this formula across multiple formula fields. For example, you could formula fields for

if all “rozmerX < 5”
{vaha_kg} * 1000

Then use those formula field when building the final formula fields.

I also recommend using an indent style that shows which parameters belong to which formula. Here is an example.

IF(
    AND(
        {rozmer1_cm} <= 50,
        {rozmer2_cm} <= 50,
        {rozmer3_cm} <= 50
    ),
    IF(
        {vaha_kg} * 1000 <= 250,
        "M1",
    IF(
        {vaha_kg} * 1000 <= 500,
        "M2"
    ))
)

Matej_Mudrik · June 6, 2022, 2:06am

Thanks for this - its nice, understandable, BUT…
BUT if i copy, it works - thats fine. If i add other conditions in this way…it works.
The truth is, i still dont understand those logic of this IF (but thats problem in mine head - time will make it bettter-i hope).
so…the full situation:

the conditions:
there are more options in status_A and status_B, but i need this formula only for those:
if status_A = “A2” and status_B =“jedna”, than it should be “K”
if status_A= “A2” and status_B =“dva”, than it should be “L”
if status_A= “A2” and status_B=“tri” and weight * 1000 <=10, than it should be “O1”
if status_A= “A2” and status_B=“tri” and weight * 1000 >10, than it should be “O2”
if status_A= “A2” and status_B =“ctyri”, than it should be “O”
for all other statuses A and B
if all dimensionX<=50 and weight * 1000 <=250 it should be “M1”
if all dimensionX<=50 and weight * 1000 >250 and <=500 it should be “M2”
if all dimensionX<=50 and weight * 1000 >500 it should be “M”
if all dimensionX>50 and weight * 1000 <=250 it should be “N1”
if all dimensionX>50 and weight * 1000 >250 and <=500 it should be “N2”
if all dimensionX>50 and weight * 1000 >500 it should be “N”

now i dont know how to make formula - waiting for your help.
Thanks again

kuovonne · June 6, 2022, 3:24am

The syntax of an IF statement is fairly straightforward, It takes two or three parameters, separated by commas. The first parameter is the test condition. The second parameter is the value if the condition is true. The third (optional) parameter is the value if the condition is false. I like to write each of the three parameters on their own line, so the complete IF takes five lines. Notice the placement of the commas and the indenting.

IF(
  {condition},
  "This is the value if the condition is true",
  "This is the value if the condition if false"
)

The complexity comes in when you want to combine and nest different functions together. It is possible to nest IF statements inside each other, and to nest other functions inside as well.

One common pattern is the nested IF pattern. One version of the nested IF pattern looks like this. Again, pay close attention to the commas and the indenting.

IF(
    {condition 1},
    "value if condition 1 is true",
IF(
    {condition 2},
    "value if condition 2 is true",
IF(
    {condition 3},
    "value if condition 3 is true",
IF(
    {condition 4},
    "value if condition 4 is true"
))))

You can repeat the basic three part pattern (IF(, condition, and value) as many times as you need. Then the final line has as many closing parenthesis as you have IFs. Each {condition} can be a multi-line function, such as an AND() function.

One way to write your case is with 11 nested IF statements, where each condition is an AND function. It is my previous example written using this pattern.

IF(
    AND(
        {rozmer1_cm} <= 50,
        {rozmer2_cm} <= 50,
        {rozmer3_cm} <= 50,
        {vaha_kg} * 1000 <= 250
    ),
    "M1",
IF(
    AND(
        {rozmer1_cm} <= 50,
        {rozmer2_cm} <= 50,
        {rozmer3_cm} <= 50,
        {vaha_kg} * 1000 > 250,
        {vaha_kg} * 1000 <= 500
    ),
    "M2"
))

There are many other possible ways to write this function, and some of which are more efficient. But this pattern is probably the easiest for you to understand. And having a function that you understand is just as important as having a function that works.

Matej_Mudrik · June 6, 2022, 4:30pm

ok, now i understand how to make nested IF…
but if i copy it and make new conditions :
IF(
AND(
{rozmer1} <= 50,
{rozmer2} <= 50,
{rozmer3} <= 50,
{vaha} * 1000 <= 250
),
“M1”,
IF(
AND(
{rozmer1} <= 50,
{rozmer2} <= 50,
{rozmer3} <= 50,
{vaha} * 1000 > 250,
{vaha} * 1000 <= 500
),
“M2”,
IF(
AND(
{rozmer1} <= 50,
{rozmer2} <= 50,
{rozmer3} <= 50,
{vaha} * 1000 > 500
),
“M”,
IF(
OR(
{rozmer1} > 50,
{rozmer2} > 50,
{rozmer3} > 50,
{vaha} * 1000 <= 250

),
"N1",
IF(
OR(
    {rozmer1} > 50,
    {rozmer2} > 50,
    {rozmer3} > 50,
    {vaha} * 1000 > 250,
    {vaha} * 1000 <= 500
 
),
"N2"

)))))

i end with error, well its not an error, but something is wrong
ok, the M selection is right, but when i want to make the N section -
i make it as
"
IF(
OR(
{rozmer1} > 50,
{rozmer2} > 50,
{rozmer3} > 50,
{vaha} * 1000 <= 250

),
"N1",
IF(
OR(
    {rozmer1} > 50,
    {rozmer2} > 50,
    {rozmer3} > 50,
    {vaha} * 1000 > 250,
    {vaha} * 1000 <= 500
 
),
"N2"

)))))
"
what i want to say (to do airtable) is
if any of the rozmerX >50 and the vaha * 1000 <= 250, than make it N1
thats functional in this case.
But if i give the vaha 0.251 it wont give me N2 (the condition should be
if any of the rozmerX >50 and the vaha * 1000 > 250 and <=500, than make it N2

feel stupid, but i cannot go over this
can you look at it, please, and tell me, what am i doing bad ?
Thanks

kuovonne · June 6, 2022, 4:47pm

You need to use both OR and AND functions for this condition.

IF(
    AND(
        OR(
            {rozmer1} > 50,
            {rozmer2} > 50,
            {rozmer3} > 50
        ),
        {vaha} * 1000 <= 250
    ),
    "N1"
)

I also recommend that you simplify your function by moving some of the logic to a different formula field.

IF(
    AND(
        {rozmer1} >= 50,
        {rozmer2} >= 50,
        {rozmer3} >= 50
    ),
    "All Rozmer Over 50"
IF(
    OR(
        {rozmer1} >= 50,
        {rozmer2} >= 50,
        {rozmer3} >= 50
    ),
    "Some Rozmer Over 50",
    "All Rozmer Under 50"
)))

Then you can can reference this field in your main formula to reduce some of the nesting

Matej_Mudrik · June 6, 2022, 6:07pm

Thanks again for your patience.
I was trying it by myself and i was using (just dont know why and please dont ask me:) ) the build of formula
IF OR rozmer AND vaha - now it seems so stupid to me,
i am writing this only for your fun, it was really stupid by me
(as i say - problem is in mine head)
Thanks again for your patience and have a nice day

kuovonne · June 6, 2022, 6:18pm

Thanks for writing your explanation. Too often people never reply to solutions and the solution provider never knows what happens.

That’s okay. Understanding these concepts can take a while. We have to wrap our heads around to thinking the way a computer think, which can be quite different from how humans think. You were able to explain the logic that you wanted in clear English, which is often very hard by itself. I just translated your English into Airtable formula language.

You have been very polite in your request and you also show initiative in trying to learn/solve things on your own, so it has been a pleasure to help you.

Eventually you will get better at writing complex formulas, and you will be able to re-write this formula to be even more efficient. But having a formula that you understand is more important than having an efficient formula.

system · June 9, 2022, 6:18pm

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.