Calculation Field

[kshelton]

Hopefully very simple for you FM Experts and thanks in advance.

Mac OS X and FM9

Setting up Calc field

If Field A = Buy

Field Y - Field Z

If Field A = Sell

Field Z - Field Y

If Field A = " " (empty)

Field X Content

The entry is always a figure.

Got confused as If Function seems to give two results.

Thanks again

Keith

[LaRetta]

Hi Keith, since you have multiple conditions, it is easier to use the Case() function which isn't restricted to two conditions like If(). So your calculation (result is number) would be:

Case (

IsEmpty ( Field A ) ; Field X ;

Field A = "Buy" ; Field Y - Field Z ;

Field A = "Sell" ; Field Z - Field Y

)

You didn't say if any remaining records might exist that don't fit into the above three cases. But Case(), just like If(), will leave any remaining fields not addressed (null). If you know this will always catch ALL fields, ie, Field A will always only have either Buy, Sell or be empty, then you can shorten your calculation (remove the final test of Field A) because all remaining records will fit that result. So you could remove the blue portion as:

Case (

IsEmpty ( Field A ) ; Field X ;

Field A = "Buy" ; Field Y - Field Z ;

[color:blue]Field A = "Sell"; Field Z - Field Y

)

... because if the Field A isn't empty and it doesn't have the word Buy then it MUST be Sell. Removing this final test will save an evaluation which is always a good thing.

LaRetta :wink2:

Edited January 25, 2009 by Guest
Corrected typo :^)

[LaRetta]

Hmmmmm, unless there are other possibilities in Field A, I think you might be able to shorten it to:

Case (

NOT IsEmpty ( Field A ) ; Max ( Field Y ; Field Z ) - Min ( Field Y ; Field Z ) ;

Field X

)

IGNORE THIS ONE ... you must act according to the Buy/Sell calc above; if you use this one, you can sell at a minus figure. Not good. :tongue2:

Edited January 25, 2009 by Guest
Never Mind! I posted this without thinking it out.

[kshelton]

LaRetta

Thanks for high speed reply.

Field A only has 3 conditions, Buy, Sell and Empty.

I will now try to carryout your suggestion.

Thanks again

Keith

[kshelton]

LaRetta

Worked first time.

Brilliant.

Thanks again. Enjoy rest of weekend.

Keith

[kshelton]

LaRetta

I have a query. Not important if we can't fix it.

Where Fields Y and Z are both empty or both contain figures, everything is fine as one of these is the final position.

However, there is an interim position where Y has a figure but Z is awaiting its entry. Consequently the calc is now inserting a huge figure as it is taking 0 from Y entry.

Is there a way of entering another condition whereby if Y has an entry but Z is empty then nothing is inserted?

Hope this makes sense.

Thanks

Keith

[LaRetta]

However, there is an interim position where Y has a figure but Z is awaiting its entry. Consequently the calc is now inserting a huge figure as it is taking 0 from Y entry.

I am puzzled by the 'huge figure'. If Field Y = 5 and Field Z is blank then the result should be -5 (if Sell) and 5 (if Buy). Usually a date set to number will produce a strange large figure such as 733432 but I can't imagine how you could get that kind of figure with what you've given us. And, if you are getting a huge number if Field Z is blank, are you also getting an incorrect huge figure if reversed and Field Y is blank? Is there something here we don't know about?

Well lets rearrange it a bit, Keith, so that both numeric fields must have a value before the calculation evaluates:

Let ( eval = Field Y and Field Z ;

 Case (

 Field A = "Buy" and eval ; Field Y - Field Z ;

 Field A = "Sell" and eval ; Field Z - Field Y  ;

 IsEmpty ( Field A ) ;  Field X

 )  // end Case

) // end Let

I sorta feel like I'm playing Pin The Tail On The Donkey and I'm blindfolded standing in front of a duck.

LaRetta

[comment]

I am guessing one of the fields is cost and the other price (or similar), and the "huge figure" is showing the the entire cost/price as profit/loss until the transaction is completed.

[LaRetta]

Hi Michael! That's what it seems like but that is logical and how a subtraction calc should work. I felt Keith was saying the figure was not expected, ie, not the value of Y but rather an unexpected number. I am sure you are correct and I'm being too literal in my concern and desire to clarify. :smile2:

[kshelton]

Sorry LaRetta, should have said that numbers are large so it is only taking 0 from let's say 10000 and inserting 10000.

I might have fixed it but I will take a look at your latest idea and see what's best.

I chuckled at 'I sorta feel like I'm playing Pin The Tail On The Donkey and I'm blindfolded standing in front of a duck.'. Where did the duck come from?

Thanks again

Keith

[LaRetta]

ROFLMAO! The duck, Keith, is the 'huge figure' (unexpected variable) to a literal person such as myself. Most times, answering a post is half-guess, half-luck and half intuition. I always prefer hands-on interaction where I can thoroughly study the situation before responding. FM Forums teaches me to punt but it still worries me that I'm not considering all potential issues involved. :laugh2:

[kshelton]

LaRetta

I'm happier now about that duck!

I am logical and try to be clear but should have been more thorough when mentioning my huge figure.

You have been great and saved me a lot of time. I will let you know when it is all OK or if I have further query.

Keith

[kshelton]

All is now OK.

My thanks to LaRetta and anyone else who replied and/or gave thought to my problem.