How can I set a minimum value of zero or -100% in a cell?

[David Drennan]

I use Excel to record my investments. I project numbers on the basis of "If
the rate of change so far in this share continues for the rest of the year,
what will be the year-end outcome?" My cell formulae work well, but . . .
(a) How do you restrict the answer cell from being worse than -100% (I
can't lose more than my original investment), and
(b) How do you prevent the £ or $ amount being less than zero?

For the life of me, I cannot find the answer in Excel Help. I have tried
using 'Solver' and 'Data Validation', but having entered the values I want,
my instructions are then ignored.

 

[Lotus123]

This sounds like something that could be handled by the MIN() and MAX()
formulas. You would just add it to your existing formula as such...

=MAX(0,existing formula)
=MAX(-100%, existing formula)

 

[Duke Carey]

Encase your existing formulas within the MAX() function

=max(rate of return formula, -100%)
=max($ amount formula, 0)

 

[Bernard Liengme]

You have not given much detail but here goes.
use =MAX(your_formula,-100%) to limit the loss to -100%
use =MAX(your_formula, 0) to limit money values to 0 minimum

best wishes - good luck with investments

 

[David Drennan]

Dear Duke,

I only put my question in a few hours ago, and already three people,
including yourself, have given me the solution. You can struggle for hours
with such things, so I am very grateful for your help.

Thanks a lot, and may I take the opportunity to wish you a very Happy New
Year at the same time.

 

[David Drennan]

Dear Lotus,

Thanks very much for your input. I knew there ought to be some simple
solution, but you can spend many frustrating hours trying to find the answers
to such questions when you don't know.

Please accept my thanks and best wishes.

 

[David Drennan]

Dear Bernard,

I spent a lot of frustrating time trying to find the answer to this simple
problem, so I am very grateful for your help. I'm just glad there are nice
people like you out there.

Thanks again, and best wishes.