16 Replies Latest reply: May 4, 2018 5:20 AM by Chris Porthouse

# Using IF to determine which sum to use

Hi,

I'm trying to use an if statement as I would in Excel, but not getting anywhere. This is my formula. They are all numeric fields.

IF(([Total Amount]*[Exchange Rate])=0,[Total Amount],[Total Amount]*[Exchange Rate])

Does anyone have an idea please? I'm using classic.

Thanks

Natalie

• ###### Re: Using IF to determine which sum to use

What version of Monarch are you using?  Are [Total Amount] and [Exchange Rate] both numeric fields?  Is your formula field set to numeric?  The formula you have looks to be correct.

• ###### Re: Using IF to determine which sum to use

Yes they are all numeric, and if I just do the sum [Total Amount] * [Exchange Rate], it works fine.

I'm using version 14.3

• ###### Re: Using IF to determine which sum to use

What results are you getting?

• ###### Re: Using IF to determine which sum to use

The formula is showing as invalid formula so I'm not getting an results. If I just use the Total Amount * Exchange Rate, it gives me the answer.

IF(([Total Amount]*[Exchange Rate])=0,[Total Amount],[Total Amount]*[Exchange Rate])

• ###### Re: Using IF to determine which sum to use

That is what I was wondering, if you are getting the red box around your expression, it is either a problem with the formula (but it looks correct), then it is most likely the data type listed on the general tab.  What do you see above the expression:

In my case I have Expression(Character) which is causing my problem.  I need to go back to the general tab and change the type to numeric.  The problem is if you have that red box around your expression the only way out is to cancel and then go back, fix the type, come back and re-enter the formula.  Normally I will highlight and cut the formula, fix the type, then come back and paste from the clipboard.

• ###### Re: Using IF to determine which sum to use

I’ve tried deleting the field and creating a new one. I get a red box around the formula.

They are definitely ALL numeric fields:

• ###### Re: Using IF to determine which sum to use

Its interesting, because I just tried using an IF statement on something more basic and a character field, and I get the same bits highlighted in red.

I have only recently upgraded to version 14.3… could it be related to that?

• ###### Re: Using IF to determine which sum to use

Probably not a 14.3 specific issue unless the install did not finish correctly.  If you hover over the expression builder box do you see any tooltip/help message that pops up? In my example I see: Formula return type does not match field data type.

The red box is telling you there is either a problem with one of your fields (trying to add a number to a character string), mismatched parenthesis/brackets somewhere (it won't tell you which), bad parameter for one of the formula fields (enter a character when it is expecting a number), or the return type does not match the formula field type.

In my example above, both of my fields [E hours] and [E/D/T Amount] (and their multiples) are numeric, but my formula field is saying the data type is character.

If I change this to numeric

and re-enter the formula, the red box goes away:

• ###### Re: Using IF to determine which sum to use

I am getting the data type error. But they are ALL numeric fields.

• ###### Re: Using IF to determine which sum to use

Hi Natalie,

I just wanted to check that you also verified the data type for the calculated field itself?  You can find it on the general tab of the calculated field.

As Chris mentioned, you'll need to cut the formula to the clip board and replace it with something that works before you can get to the general tab.

If you can "OK" with just a 0 in the formula, then the general tab must already be numeric - and it is a strange problem indeed!

Hopefully, you'll need a simple expression such as "test" (including the quotes).  This would indicate your field is character on the general tab.  In which case, swap it to numeric, then paste you full formula back in.

If this doesn't resolve it, would you be able to supply the model/workspace and sample data?

Regards,

Steve.

.

• ###### Re: Using IF to determine which sum to use

Hi Steve,

As per the screen shots above, the calculated field is called currency amount which is NUMERIC.

If I remove the IF statement, and just do Total Amount * Exchange Rate, then it returns an answer. Its when I add the IF statement (Using exactly the same fields), I get an issue.

I have just deleted the fields and am trying again.

• ###### Re: Using IF to determine which sum to use

OK - Understood - Sorry I missed that point!

The expression looks fine to me as well.

IF(([Total Amount]*[Exchange Rate])=0,[Total Amount],[Total Amount]*[Exchange Rate])

The extra set of brackets around the logical condition should not be required.  I don't think it should cause a problem either, but could you test without it please?

IF([Total Amount]*[Exchange Rate]=0,[Total Amount],[Total Amount]*[Exchange Rate])

An additional couple of tests to see if we can narrow down the section causing the problem and force a True and False response would be

IF(1=1,[Total Amount],[Total Amount]*[Exchange Rate])

IF(1=2,[Total Amount],[Total Amount]*[Exchange Rate])

It would be interesting to see where the red "error" highlights are with these expressions.

• ###### Re: Using IF to determine which sum to use

Hi Steve,

Same issue without the brackets. I actually added them in to see if it made a difference in the first place!

Same issue with your other statements and its the same red highlights.

Worth mentioning, that if I try a IF statement with 3 character fields rather than 3 numeric fields, I also get the same issue.

Natalie

• ###### Re: Using IF to determine which sum to use

So, I have solved the issue (Well sort of!)

My file had the comma as the decimal place. I re-ran the file with updated settings to use the period as the decimal place and it solved the problem.

However, I would like to be able to use IF statements using the comma as for some users, they have no option but to run it with the comma.

What do you think?

• ###### Re: Using IF to determine which sum to use

Ah - of course. Should have spotted that one!

You can use semi colon in place of the comma for all calculated fields.   They will work regardless of the decimal separator.

IF(([Total Amount]*[Exchange Rate])=0;[Total Amount];[Total Amount]*[Exchange Rate])

• ###### Re: Using IF to determine which sum to use

Yep, totally slipped my mind too to try that.