Formula Help, If field = this, multiply other fields

Platform discussions
1

I am looking for help setting up the following:

If Column 1 (drop-down menu called “Line”) is LABEL1, multiply Column 2, Column 3, 0.8,
If Column 1 is LABEL2, multiply Column 2, Column 3,
If Column 1 is LABEL3, multiply Column 2, Column 3, 0.6

It seems pretty straightforward (in my head), but I can’t get it to work.

IF({Line#Labels}=“LABEL1”,MULTIPLY({Column2},{Column3})),
IF({Line#Labels}=“LABEL2”,MULTIPLY(({Column2},{Column3}),0.80)),
IF({Line#Labels}=“LABEL3”,MULTIPLY({Column2},{Column3},0.6))

Any ideas would be appreciated!

2

@MelissaPFA,

Assuming that it’s working if you put just one line, you could do this…

IF({Line#Labels}="LABEL1", MULTIPLY({Column2},{Column3}),0) + IF({Line#Labels}="LABEL2", MULTIPLY(({Column2},{Column3}),0.80),0) + IF({Line#Labels}="LABEL3", MULTIPLY({Column2},{Column3},0.6),0)
4

One line of the equation at a time works, but the formula as a whole is still producing an error.

5

@MelissaPFA @JCorrell

I don’t think the IF formula in Monday allows more than one variable + default. See description:

Checks if a condition is met, if so, returns the first value, o/w returns the other.

That is why it works as separate formulas, but not one string. Try using the SWITCH formula as that allows multiple variables.

6

Can you provide an example? I get why that could work, but I can’t wrap my brain around setting this up without an IF.

7

@MelissaPFA

I’m not great with formatting Monday formulas, but first attempt would look something like this:

SWITCH({Dropdown#Labels},“Label 1”, MULTIPLY({Numbers 1},{Numbers 2}),“Label 2”, MULTIPLY(({Numbers 1},{Numbers 2}),0.80),“Label 3”, MULTIPLY(({Numbers 1},{Numbers 2},0.60),“”)

Set up is, SWITCH({Column}, “variable1”, “result 1”, “variable2”, “result 2”,

Now the only kicker is that SWITCH may not be able to process additional formulas as “Results”. Maybe someone who is more knowledgeable about this can help as I may just take some format tweaking to get it right.

Another option for you might be the General Caster app. General Caster is here. Get rid of formula columns! You can do a lot more formula wise and it will be better for you down the road since the Formula Column in Monday often creates limitations when it doesn’t interact with other features. Here is a list of all the formula options you can use with General Caster, including IFS. Functions | Formula.js. IFS was my first thought for fixing this but Monday doesn’t support that currently.

1 Like
8

Thank you - I will try these options. Yes - I can get it to work beautifully in Excel with IFS!

1 Like
9

@MelissaPFA,

The switch function is a completely valid solution.

If you want to get IF’s to work, try each line separately first. Then put them together. If you get an error, post the formula here. It will be easy to fix.

10

@MelissaPFA,

I rechecked your first formula. You have some extra “()”. Try this:

IF({Line#Labels}="LABEL1", MULTIPLY({Column2},{Column3}),0) + IF({Line#Labels}="LABEL2", MULTIPLY({Column2},{Column3},0.80),0) + IF({Line#Labels}="LABEL3", MULTIPLY({Column2},{Column3},0.6),0)

For the switch, try:

SWITCH({Line#Labels}, "LABEL1",MULTIPLY({Column2},{Column3}), "LABEL2",MULTIPLY({Column2},{Column3},0.80), "LABEL3",MULTIPLY({Column2},{Column3},0.6), "ERROR")
1 Like
11

Almost! Actually both of these seem to work when there are 2 things being multiplied, but not when we add in the third multiplier (0.8 or 0.6).

12

@MelissaPFA,

My bad. I don’t use the MULTIPLY function. I just assumed how it worked.

This will work:

IF({Line#Labels}="LABEL1",{Column2}*{Column3},0) + IF({Line#Labels}="LABEL2",{Column2}*{Column3}*0.80,0) + IF({Line#Labels}="LABEL3", {Column2}*{Column3}*0.6,0)

(or the corresponding syntax for the switch function)

3 Likes
13

You’re a rock star! Thank you so much! The Multiply Function was new for me too, but I’m happy to see it worked without it.

14

You can actually use nested if statements, by adding another If statement to the Else parameter.

For Example

IF({Line#Labels}=“LABEL1”, MULTIPLY({Column2},{Column3}),IF({Line#Labels}=“LABEL2”,MULTIPLY({Column2},{Column3},0.80,0), IF({Line#Labels}=“LABEL3”, MULTIPLY({Column2},{Column3},0.6,0))))

Not sure if the above formula works but just want to demonstrate that you can use ELSE IF to create a nested IF statement if that makes sense

15

This new knowledge base article may also be helpful for future reference :blush: The IF Function

16

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