Can someone assist me with following please.

Investment value fields showing investment, % increase to date, projected % increase PA, etc.

I need the calculated projected % increase PA to be limited to - 100 % if the investment decreases in value. For example if 1000 dollars becomes 500 after 3 months, it projects as - 200 % PA but I want this to show - 100 % as the investment cannot, of course, lose more than 100 %.

At the same time, growth percentages must be not be limited.

Thanks.

Hello Keith,

It sounds like you might be able to resolve your problem by the use of the Max( ) function.

For example, if you have a formula which calculates the projected increase/loss, you could ensure it never goes below -100 along the lines of the following:

Max(-100, YourFormulaHere)

Which will return -100 whenever your formula result reaches or goes below -100, but will return your formula result if it is above -100.

Thanks Ray for quick reply.

Tried it but all change to -100, i.e. not just those over -100. What am I doing wrong?

Thanks.

Hi Keith,

I just popped it into a calc field in a test db here and it is working fine.

Provided your formula produces a result which is larger than -100, the Max( ) function should return that result.

Based on what you're saying, all I can suggest is that you check the formula and the field data types in case there is something amiss. If you are not able to locate a problem and it still doesn't work, I suggest that you post the formula you are using and a description of the set-up. Then it may be possible to discern why it isn't producing the expected result.

Ray

Unsure what I did wrong but, in the meantime, I applied the If function and this seems to work fine. I will try the Max function again and see if I can find out why it didn't work.

Thanks again.

Keith