Using Multiple IF Statements with "AND" and "OR" and multiplication

[mbnumba6]

Hi Team,

I am trying to make an expression that auto calculates a price based on specific categories or columns a customer falls into.There are only 4 combinations. So that means I need an IF statement that looks at 4 different cases to calculate a price.

Example I have the following columns :

1. Type(Enum)
2. Service Lateral (Number)
3. Trenching (Enum)

The column “Type” and "Trenching only has two choices to choose from.

What I am trying to accomplish is something like this:

If (
Type = “Overhead”, Service Lateral Equals = 100 and Trenching = “Company”**
then multiply by 24.88

OR

If(
Type = “Underground”, Service Lateral = 100 and Trenching = “Customer”
Then multiply by 4.27

OR
If(
Type = “Overhead”, Service Lateral = 100 and Trenching = “Company”
Then multiply by 8.38

OR
If(
Type = “Underground”, Service Lateral = 100 and Trenching = “Company”
Then multiply by 20.42))

I looked at a few problems on this board already and where my confusion is coming from is using the AND/OR statements for multiple cases. Not sure where exactly to put AND. I am also unsure if I should use “IF” or “IFS”.

So this expression will take place in another column that is READ ONLY and it will calculate the price based on what was answered throughout the application. If there is another way to do this without IF statements I’m all ears.

Using multiple IFS statements or is there a different way?

I need an expression to check multiple IFS and i cannot figure it out. What I have so far: IFS(( ([Antigen Results] <> “Positive”), ([PCR Results] <> “Positive”), “CLEAR”, ([Antigen Results] = “Positive”), ([PCR Results] = “Positive”), “ACTIVE”), TRUE) I want this expression to check multiple values and give a text value accordingly.

Here’s what I tried below just in case you are interested in how I was thinking:

IF(
AND(
[Type] = “Underground”,
[Trenching] = “Company”),
[Service lateral] * 20.42),

OR

IF(
AND(
[Type] = “Overhead”,
[Trenching] = “Company”),
[Service lateral] * 20.88),

IF(
AND(
blah blah blah))

Thank you in advanced

[Steve (Steve Coile)]

@mbnumba6

Here’s what I tried below just in case you are interested in how I was thinking:

Looks good to me. What happened when you tried?

[mbnumba6]

Thanks for taking the time to respond.

But its saying “IF function is used incorrectly:three inputs should be provided — (condition, if-result, else-result).”

Sorry if the screenshot is confusing you:

But "Types of Services Covered… " is the same as “Types” from my question. I just shortened it for you guys

[Markus_Malessa]

Would this be something where IFS() would be more appropriate rather than IF()?

Either way, your first IF statement is terminated after the TRUE condition, so that is why it’s giving you the 3 arguments required error.

I would propose trying IFS() instead like this:
IFS(
AND([Type] = “Underground”, [Trenching] = “Company”), [Service lateral] * 20.42,
AND([Type] = “Overhead”, [Trenching] = “Company”), [Service lateral] * 20.88,
AND(blah blah blah)
)

IFS()

[mbnumba6]

@Markus_Malessa

IFS(
AND([Type] = “Underground”, [Trenching] = “Company”), [Service lateral] * 20.42,
AND([Type] = “Overhead”, [Trenching] = “Company”), [Service lateral] * 20.88,
AND(blah blah blah)
)

This IFs statement helped a lot. I wasn’t sure if I should go that route or not.
We are close.

I have one more error that I’m trying to resolve.

[Markus_Malessa]

You put the parenthesis in the wrong place for the AND() statement.

You have ‘* 4.27)’ that needs to be placed ‘“Company”)’

[mbnumba6]

You’re the best. Thank you sooooooo. much!!!

One last thing, its only looking at the first statement (no error). So how do we add the “OR” in there to get the other three conditions?

IFS(
AND(
[Type] = “Underground”,
[Trenching] = “Company”),
[Service lateral ____ feet in length from…] * 20.42)

OR(
[Type] = “Underground”,
[Trenching] = “Property Owner”),
[Service lateral ____ feet in length from…] * 4.27

OR(
[Type] = “Overhead”,
[Trenching] = “Property Owner”),
[Service lateral ____ feet in length from…] * 8.38

OR(
[Type] = “Overhead”,
[Trenching] = “Company”),
[Service lateral ____ feet in length from…] * 24.88

EDIT: I played around with the ORs and ANDs and same results

[Markus_Malessa]

There is no need for the OR()s and you are terminating expressions prematurely by putting parenthesis in the wrong place and omitting commas. For simplicity I have fixed your statement for you, but to gain a better understanding you should review the help link I posted above.

@mbnumba6

IFS(
AND(
[Type] = “Underground”,
[Trenching] = “Company”),
[Service lateral ____ feet in length from…] * 20.42,

AND(
[Type] = “Underground”,
[Trenching] = “Property Owner”),
[Service lateral ____ feet in length from…] * 4.27,

AND(
[Type] = “Overhead”,
[Trenching] = “Property Owner”),
[Service lateral ____ feet in length from…] * 8.38,

AND(
[Type] = “Overhead”,
[Trenching] = “Company”),
[Service lateral ____ feet in length from…] * 24.88
)

[Steve (Steve Coile)]

I see @Markus_Malessa has this well in hand.

[mbnumba6]

Appreciate the patience Markus.

Thanks for sticking with me. I finally got it. There was a small error when I directly copied and pasted, but somehow I fixed it by moving the commas around (It accepted when I put a comma behind 24.88).