# IF Formula to apply formula

I have a base that contains records of artwork with record#s that contain letters. D for drawings, P for Paintings and S for sculptures. The prices for all the artwork is based on a formula derived from the dimensions of the art. Drawings and paintings have their specific formula to calculate value. At the moment I have two columns that show the price: one for the paintings with its specific formula and another one for the drawings with a formula of its own. I would love to create a single column with a formula with an IF statement that would apply one price formula if it finds a P in the record number and another one if it finds a D. Is it possible?. I donâ€™t understand if it is possible to write a conditional to IF if it finds something. Any help or suggestions would be greatly appreciated.

Very much possible. Although, SWITCH might scale better here than IF blocks. Youâ€™ll be doing something like this:

``````SWITCH(
the first character of the record number,
if that first character is P,
use this price,
whereas if the character is S,
use this price,
otherwise default to this value
)
``````

You can add as many combinations of â€ścharacter letter, corresponding priceâ€ť as you need if more letters or prices are added over time. Just add them before the default value with a comma at the end.

A potential solution would look like the code below. Youâ€™ll need to replace the field names (the words inside the curly braces) with the field names youâ€™re actually using.

``````SWITCH( LEFT({Record Number},1), "P", {Painting Price), "S", {Sculpture Price} )
``````

That last bit â€śâ€ť will return an empty string if no prices were found but you can replace that with some value as well.

Thank you very much @Zollie!!. Would it be possible to embed formulas inside the curly braces instead. At the moment there are two columns because P and D have different formulas to calculate price. I am looking to combine the two VALUE columns into a single one that detects if it is a P or a D based on a statement and applies the correct formula.

The curly braces are part of the field name. Iâ€™m guessing that what you really meant to ask is whether or not you can replace the field name with another function, and the answer is yes. You donâ€™t have to only return the value of a field. You can perform almost any operations you want to generate the value to be returned for each option.

Also, you canâ€”and probably shouldâ€”omit the closing empty string ("") from the example above. The `SWITCH()` function will default to returning nothing if none of the listed options are found. Because the other return values are numeric, returning an empty string as the fallback is a type mismatch. When building formulas, all options should return the same data type.

3 Likes

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