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

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.

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

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)


--
Lotus123
------------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611
View this thread: http://www.excelforum.com/showthread...hreadid=498390

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

Encase your existing formulas within the MAX() function

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

"David Drennan" wrote:

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.

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

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"David Drennan" wrote in message
...
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.

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

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.

"Duke Carey" wrote:

Encase your existing formulas within the MAX() function

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

"David Drennan" wrote:

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.

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

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.

"Lotus123" wrote:


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)


--
Lotus123
------------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611
View this thread: http://www.excelforum.com/showthread...hreadid=498390

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

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.

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"David Drennan" wrote in message
...
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.