Nested IF, going mad (still mad but issue fixed)

#1

Hi,

I am going around in circles, followed examples and can’t get it right.

i have a column with 3.1, 3.2, 3.3…3.8.

Here is the “IF” I have for 1 value that works, IF(PH=3.1, {Qty Pressed L}*0.5)

can someone please type out the nested IF with PH=3.2 and *0.6 added, I should then be able to add the other values.

thanks
Vince

0 Likes

#2

@Vincent_Wakefield see below.

 IF({PH}=3.1,{QTY Pressed}*0.5,
 IF({PH}=3.2,{QTY Pressed}*0.6))

0 Likes

#3

An alternate way to approach this is with the SWITCH function, which can often be more compact (and easier to edit) than a bunch of nested IFs. With the two levels in the above example, the equivalent formula using SWITCH would be:

SWITCH({PH}, 3.1, {Qty Pressed L}*0.5, 3.2, {Qty Pressed L}*0.6)

SWITCH is great when you want to pick between one of several output values based on the value of a single field (or a formula involving a single field). In this case, the output all depends on what’s in {PH}, which is why it appears as the first argument. The arguments after that are in pairs, with the first in each pair being the value to look for, and the second being the desired output if that value is found.

Also, because the output is always {Qty Pressed L} multiplied by some value, we can simplify this even further:

{Qty Pressed L} * SWITCH({PH}, 3.1, 0.5, 3.2, 0.6)

This version looks at {PH}, and outputs the necessary value to multiply with {Qty Pressed L}, rather than inserting the full calculation as the output.

1 Like

#4

Thanks for the replies. It was really doing my head in.

The SWITCH formula is so much simpler and so easy to add the additional numbers.

All working now.

Thanks again

0 Likes