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

D

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.
 
L

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)
 
D

Duke Carey

Encase your existing formulas within the MAX() function

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

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
 
D

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.
 
D

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.
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top