Using IF to determine which sum to use
Altair EmployeeHi,
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
Answers
-
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.
0 -
Altair Forum User said:
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.
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
0 -
Altair Forum User said:
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
What results are you getting?
0 -
Altair Forum User said:
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
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])
0 -
Altair Forum User said:
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])
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.
0 -
Altair Forum User said:
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.
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:
0 -
Altair Forum User said:
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.
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?
0 -
Altair Forum User said:
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?
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:
0 -
Altair Forum User said:
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:
I am getting the data type error. But they are ALL numeric fields.
0 -
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.
.
0 -
Altair Forum User said:
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.
.
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.
0 -
Altair Forum User said:
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.
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.
0 -
Altair Forum User said:
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.
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
0 -
Altair Forum User said:
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
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?
0 -
Altair Forum User said:
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?
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])
0 -
Altair Forum User said:
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])
Yep, totally slipped my mind too to try that.
0
