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 :
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.
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
Solved! Go to Solution.
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.
Looks good to me. What happened when you tried?
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
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)
)
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.
You put the parenthesis in the wrong place for the AND() statement.
You have โ* 4.27)โ that needs to be placed โโCompanyโ)โ
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
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.
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).
I see @Markus_Malessa has this well in hand.
User | Count |
---|---|
39 | |
28 | |
23 | |
23 | |
13 |