Removing "minus" symbol?

[fiveshorts]

Hi,

Is it possible to retain a negative number's "negative-ness" for the purpose of calculations, but lose the actual "minus" symbol for purpose of display? I'm using conditional formatting to convey positive and negative numbers and in this particular solution, the minus symbols themselves are inappropriate. I realize I can choose different ways to display a negative number, but is it possible to display nothing but the number?

Any help greatly appreciated.

Edited January 3, 2008 by Guest

[comment]

I don't think you can avoid using ONE of the pre-defined formats for a negative number.

But you could use a calculation field for the display = Abs ( number ).

[Lee Smith]

Why not let it be positive, and subtract it in a calculation.

i.e. Field A - Field B = Field C

Lee

[fiveshorts]

Thanks comment and Lee,

I guess I'm just too inexperienced to make that work... the problem I have is that these positive and negative numbers appear in the same field (across many different records). So I don't know how to say "add all the positives and subtract all the negatives", without having them actually be positives and negatives.

At the moment, they're all positive, but their "negative-ness" is being depicted by the field's fill color (red or green) — a conditional format based on another calculation.

It's complicated, I know, but the point is that the database is a transactional one (for stock trading) and the values I'm working are 'technically' positive or negative, but in reality, just represent the difference between two other values...

I'm blowing my own mind just trying to work it out.

Thanks for your continued patience.

Edited January 3, 2008 by Guest

[comment]

OK, now I am officially confused.

I don't know how to say "add all the positives and subtract all the negatives", without having them actually be positives and negatives.

Neither do I. You cannot discard the sign and expect to use it later.

At the moment, they're all positive, but their "negative-ness" is being depicted by the field's fill color (red or green) — a conditional format based on another calculation.

Well, that sounds like it should work - so what exactly is the problem? If there's a possibility that some of these values will be negative, and you don't want to display that, do as I said - use another field for the display ONLY.

[LaRetta]

OK, now I am officially confused.

Ah. That's been my problem all along ... I've always been UNOFFICIALLY confused. Is there a certification one can get to become official? :smile2:

[comment]

You need to be certifiable to get a certification. To apply:

[Søren Dyhr]

in this particular solution, the minus symbols themselves are inappropriate

I think I can guess what this might be about, we're solely in the presentation layer:

European mathematicians however, for the most part, resisted the concept of negative numbers until the 17th century, although Fibonacci allowed negative solutions in financial problems where they could be interpreted as debits (chapter 13 of Liber Abaci, 1202) and later as losses (in Flos).

From: http://en.wikipedia.org/wiki/Negative_and_non-negative_numbers#First_usage_of_negative_numbers

While bearing in mind that the double-sided ledger arrived as early as 1494 ...

From: http://acct.tamu.edu/smith/ethics/pacioli.htm

Bookkeeping first aim was to provide a snapshot to the merchant of his wealth, before entering the next deal. Something negative numbers actually easily could convey, but then is there the taxation/protection money problem, authorities wish to know if expenditures really can be justified ... hence the continuation of the split in two columns.

To make such thing in filemaker could be done in a two cell repeating calc'field:

Let(tt=Case (((Extend (Value) ≠ Abs ( Extend (Value)))+1) = Get ( CalculationRepetitionNumber );Extend (Value) );Sign(tt)*tt)

This way will each number according to it's "sign" be ushered to their respective cell, without the sign shown!

This allows us to Summarize the two columns separately like this:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html#more

--sd

[fiveshorts]

I apologize, I'm not trying to be cryptic, I just assumed folks were too busy to be interested in the specifics of my FM project... that was shortsighted of me, as it make it seem as I was being obtuse.

OK. I'm designing a DB that helps a currency trader track and manage the risk inherent in the trading process.

A trade is executed at a given price (Entry) and the trade is protected by a "stop-loss" — a second price level that represents the point at which he/she would want to exit the trade if it goes against them.

The difference in value between the Entry and the Stop is what defines the risk the trader is carrying at the outset of the trade.

In currencies, a trader can buy or sell, so the stop price can be lower than or higher than the entry price, accordingly. It isn't, strictly speaking a "negative" or a "positive" value.

For instance, in a buy trade:

the entry price might be 1.800

the stop price might be 1.700

= difference of 100 "pips"

= a risk of 100 pips

In a sell trade:

the entry price might be 1.800

the stop price might be 1.900

= difference of 100 "pips"

= a risk of 100 pips

Neither is negative or positive, per se.

The complication arises when the trade is underway and goes in favor of the trader. As price moves up in a buy trade, the trader moves the stop price up to follow it, eventually overtaking the entry price and "locking in" profit. The difference between entry and stop might still be 100 "pips", but the other way:

Again, in a buy trade:

the entry price might be 1.800

the stop price might NOW be 1.900

= still difference of 100 "pips"

= a profit of 100 pips

What was a risk (negative?) relationship between two values in a the context of sell trade is actually a profit relationship between two values in the context of a buy trade.

So, in my infinite wisdom, I decided this dicotomy would be best portrayed in a single field that always showed a positive value (the pips difference, one way or the other), and to depict that value's "risk-ness" or "profit-ness" with a red or green fill color, using conditional formatting.

Works perfectly, until I need to quantify the total risk/profit amount across all trades — as they're all positive numbers, I can't say "add up all the red fills and subtract all the green fills".

Phew. I hope that makes a bit more sense?

Might hidden fields be the answer here, I'm wondering?

[Lee Smith]

maybe something like this.

Case (Buy ≥ Sell; Buy - Sell; Sell - Buy)

HTH

Lee

[Søren Dyhr]

So basicly is to show every difference as a not negative, I kind of gave an approach to it by putting this in the end of my calc:

Sign(tt)*tt

But Abs( is a more obvious a choise!

--sd

[Raybaudi]

... I can't say "add up all the red fills and subtract all the green fills".

... but you can say: "add up all the white text and subtract all the black text", if you can live with white text into red fills and black text into green fills.

[fiveshorts]

Just wanted to thank you all for your time and attention on this matter. Here's how I solved the problem in the end: (I am a relative novice, after all...)

The issue arose from trying to make one field be all things to all people, so after lots of agonizing, I simply created extra fields with the 'correct' values in (positives and negatives) for use in the summaries, but kept them hidden. Then used the "incorrect", visible versions for display against the conditionally-formatted fills.

Now, is this perhaps a representation of what I keep reading as the 'presentation' layer? I sort of made this solution up... No doubt there's a more elegant solution.

I see on this forum there's a "left brain" and "right brain" posting area. I'd like to propose a "middle brain" for the rest of us :-)

I'm a designer (graphical) first and a scripter/math guy waaaaay second. So I have to really stew on these math conundrums. It doesn't come easily.

Thanks again.

[comment]

Now, is this perhaps a representation of what I keep reading as the 'presentation' layer?

Yes.

I sort of made this solution up...

LOL, I'm glad you did - because I only suggested it twice... :

[fiveshorts]

My apologies Comment.

On re-reading the thread, I see you did indeed say so, but I was too dumb to understand what you were saying, obviously. I had to work through it myself to come full circle.

I've only been at this a few weeks, so forgive me.

[comment]

No, it's really for the best - because now you not only know how, but also understand why.