Calculation inside IF formula


#1

Hallo

I am trying to use the following IF formula to manage 2 calculations based on a text field condition: if a field contains “furniture” I want the target field to result in a multiplication between a 2 fields, if it says “Fabric” the multiplication uses another field, see below :

IF(Type=“Furniture”,(amount*{Price 1}),(amount*{Price 2}))

but I am getting an error. Is this sintax wrong or am I using the wrong statement ?

Thanks for any help

Regards

Luca


#2

I thought it was the () in the multiplications (you don’t need them), but works for me with them too.

What happens if you leave just one of the multiplications in the formula? Are amount and price numbers? Leave a screenshot if you can.


#3

Try this:

IF({Type}="Furniture",SUM({Amount}*{Price 1}),IF({Type}="Fabric",SUM({Amount}*{Price 2})))

To work make a Single Select Field named Type with two options, Furniture and Fabric
Make a current or number filed called Amount
Make Two currency or number fields names Price 1 and Price 2

Anything in within { } is a field in your table. Syntax can be tricky until you learn the ‘chunks’ of what each thing is.

IF() is really asking ‘yes or no?’ - it’s a question statement. So by Saying IF({Type}=“Furniture”,yes,no) you’re saying in plain language, "Is the Type Select Field Furniture? Yes or No. If yes then you insert the results or conditions you’d want, if no, you can ask more questions or insert other static answers.


#4

Luca -

You don’t say what type of field {amount}, {Price 1}, and {Price 2} are. My guess from your error is they are non-numeric, somehow: text, a formula field that equates to a text string, lookup, rollup — something along those lines. If one or more of them are non-numeric, you can finx the issue by wrapping the text values with a VALUE() function. (If one or more is coming in as a single-element array — a lookup field, for instance, that isn’t identifiable as a number – you need to append an empty string to it (that is, append ‘&''’ to the field name) and then wrap it with VALUE().


#5

Hallo W_Vann_Hall

Thanks for the support – below field types:

AMOUNT is an integer

Price 1 and Price 2 a currency values

Furniture is a single select field

I tested the formula by Mrmobius, but the formula is using SUM and I need a MULTIPLICATION of the “price X units” instead.

Below is a screenshot to clarify:

This is the formula I am using now:

IF(Type="Furniture",({Amount}{Price 1}),({Amount}{Price 2}))

But I keep getting an error

Thanks again for your feedback

Luca Mangione


#6

In your screenshot it’s a Text, change it to a Number.

You’re missing the multiplier operator: *.


#7

Thanks so much

So stupid of me… Now it’s working

Thanks

Luca Mangione