Calculated field - Using Multiple IF statement and PROD - HELP!

[snuzum]

Hi Community

I am struggling with a calculated field that includes multiple IF statements with a PROD calculation - the custom field referenced has 5 options from a drop down field, but I can't seem to get the first "IF" statement calculation to work.

IF(Estimated Project Value for PM Calculation Updated to 2022 Pricing='Project Value 1000 - 9999',(PROD(Total Installation Cost,.14)))

I have tried multiple combinations of " or ' , tried curly brackets, curly brackets with DE: but can seem to get a result. Reports N/A

The result should be a calculation for a currency.

Any help would be appreciated!

Sherri

[skyehansen]

assuming you have recalculated your expression every time you changed it, have you also considered starting with a simpler calculation, such as IF(Estimated Project Value for PM Calculation Updated to 2022 Pricing='Project Value 1000 - 9999',CONCAT("hello world"),"")

(in other words you seem to be concerned about whether your syntax for your custom fields is even correct to start with, so getting squared away with that first, and then adding on a prod statement that maybe calculates two real numbers, and then after that, adding variables.

By the way I don't think you need a paren before the PROD part of your statement.

[snuzum]

Hi Skye,

Thank for the idea - however this doesn't yield a result either - I am wondering if the value from the drop down option Project Value 1000 - 9999 needs to be surrounded by something different? Or the custom field name: Estimated Project Value for PM Calculation Updated to 2022 Pricing requires something different.

I tried the above in both a field that shows a text format and a currency format as i wasn't sure if the format was the problem.

Yes on "refreshing" and recalculating on issue form and Apply and Save on custom form.

[skyehansen]

Without trying to give you an answer, know that you can always start even smaller with a one word field and answer. Here's something I tried:

1) Create a test custom form, and a test dropdown field with options for 1 and 2.

2) Click on Apply

3) create a calculated custom field on the test custom form and populate it with this:

IF(test=2,CONCAT("two"),"")

Add to an object and change the toggle to "2". Once you save, the calculated field populates with "two". So now you know the syntax. Once you know the syntax, start making your changes and work one change in at a time until you fail -- this way you know exactly what is failing.

For example, my next change was to populate one of the dropdown options with Project Value 1000 - 9999. This produced an error when I went to plug it into my calculated field, so I couldn't even save it. I put it in double quotes and that seemed to work ("Project Value 1000 - 9999") -- toggling to that value caused the calculated field to repopulate with "two". So now you know that a workable syntax with this value is double quotes. (single quotes would also work, the important thing is, you now know it works with a really low impact formula, so keep building)

So now, tackle the calculation (the prod part).

For the PROD part, I started with IF(test="Project Value 1000 - 9999",PROD(5,6),"") and got that working (my test form changed to 30), so now I know I have the right syntax there. Then I can trade in for a fieldname -- let's say a number field called "test2". If that one works, then now you can try the Total Installation Cost field.

Anyway, long story short, what I eventually found out on my instance is that your calculation seems fine. None of your fieldnames need "anything extra". They're fine.

You're basically down to IF(YourFieldname="some sort of answer",PROD(YourOtherField,.14),"I would always add something here to test with as well")

In other words, IF (Something, Do something, Otherwise do something else).

[snuzum · Accepted solution]

Hi

Just to circle back I was able to resolve my calculation with some help from @David Cornwell‚ by inserting the 2 exceptions first that were contained in my drop down values and then added the PROD calculation. It ended up much simpler than originally thought.

Here was the final calculation:

IF(Estimated Project Value for PM Calculation Updated to 2022 Pricing=0,268,

IF(Estimated Project Value for PM Calculation Updated to 2022 Pricing=1000000,0,

PROD(Estimated Project Value for PM Calculation Updated to 2022 Pricing,Total Installation Cost)))

Thanks.