+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    David Drennan
    Guest

    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.

  2. #2
    Forum Contributor Lotus123's Avatar
    Join Date
    11-07-2005
    Location
    Texas
    MS-Off Ver
    2007
    Posts
    153
    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)

  3. #3
    Duke Carey
    Guest

    RE: 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.


  4. #4
    Bernard Liengme
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    >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.




  5. #5
    David Drennan
    Guest

    RE: 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.


  6. #6
    David Drennan
    Guest

    Re: 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
    >
    >


  7. #7
    David Drennan
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > >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.

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1