9 Replies Latest reply on Apr 24, 2011 6:43 PM by leonhart1981

### Post

Is it possible to set an If function in a calculation field?

Here is what I want to do:

I have the following fields: Unit, Price in PCS, and Prices in PRS

If Unit is PRS or KITS, then do the following calculation: Prices in PRS = Prices in PCS x 2

If this is possible, can anyone show me how to do it?

Thank you!!!

• ###### 1. Re: Help about Calculation if.....

If your "Prices in PRS" field is already defined as a calculation field, you can use the CASE function as follows:

Case ( PatternCount ( Unit ; "PRS" ) ; Prices in PCS * 2 ;

PatternCount ( Unit ; "KITS" ) ; Prices in PCS * 2 ;

"" )

This will look for either PRS or KITS in the Unit field and if true, return twice your Price in PCS field as a result.

• ###### 2. Re: Help about Calculation if.....

It works...

Thank you very much~~

• ###### 3. Re: Help about Calculation if.....

Another way of writing this:

Case ( PatternCount ( Unit ; "PRS" ) ; Prices in PCS * 2 ;

PatternCount ( Unit ; "KITS" ) ; Prices in PCS * 2 ;

"" )

... could be:

Case ( Unit = "PRS" or Unit = "KITS" ; Prices in PCS * 2 )

• ###### 4. Re: Help about Calculation if.....

I see! Will keep it in mind!!

Btw, I have another question about Case function!

I would like to do the following calculation in the following circumstances:

Field: Revenue, Expense, and Profit

When Revenue = 0, and Expense is great than 0, then profit = -100%

When Revenue =0, and Expense is 0, then Profit = 0%

Otherwise, Profit = (Revenue - Expense ) / Revenue

PS: I have set the Profit field displayed in percentage.

• ###### 5. Re: Help about Calculation if.....

Case (
not Revenue and Expense ; -100 ;
not Expense and Revenue ; 100 ;
( Revenue - Expense ) / Revenue
)

• ###### 6. Re: Help about Calculation if.....

Ooops, you are using layout to displayer percent?  Then change calc to:

Case (
not Revenue and Expense ; -1 ;
not Expense and Revenue ; 1 ;
( Revenue - Expense ) / Revenue
)

• ###### 7. Re: Help about Calculation if.....

It seems there is a mistake on the formula above.

When I use your formula, my profit result in ? when revenue and expense are both 0.

But when I make some adjustments, it's fine now. Here is what I did:

Case (not Revenue and Expense ; -1 ; not Expense and not Revenue ; 0 ; (Revenue - Expense) / Revenue )

There is one thing I would like to clarify with you!

On the above formula, does "not Revenue" mean Revenue = 0 ? And "Expense" mean Expense is greater than 0?

Thank you!

• ###### 8. Re: Help about Calculation if.....

Case (
not Revenue and Expense ; -1 ;
not Expense and Revenue ; 1 ;
( Revenue - Expense ) / Revenue
)

This indeed works so I suggest you try it again.  Be sure to include the last parenthesis (grab all in blue).  Your calculation ignores your second requested criteria, i.e. if there is Revenue but no Expense, produce 100%.

Also, your second portion that you added in red isn't needed at all because the calculation will not even evaluate if both those fields are empty.  In fact, if both fields are empty, you will never get the 0 at all.  If you wish to get the 0% then you must uncheck (in the calculation dialog) 'Do not evaluate if all referenced fields are empty.'

Case (not Revenue and Expense ; -1 ; not Expense and not Revenue ; 0 ; (Revenue - Expense) / Revenue )

'not' is a Boolean test.  If the field contains a number (not zero or not empty) then it is boolean true.  So writing 'not Revenue' means that Revenue is empty or zero.  It is faster (resource wise) and easier to use Boolean logic than to write either of the following:

IsEmpty ( Revenue ) ... which would break if it contained a zero
or
Revenue = 0

So in my calculation:

'not Revenue and Expense' means:  Revenue is empty or zero AND Expense has a number
'not Expense and Revenue' means:  Expense is empty or zero AND Revenue has a number

• ###### 9. Re: Help about Calculation if.....

I see! Thank you for your explanation!

I give it a try and it works~

Thank you very much!!!