Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates of a Day for a month & year cell formulas | Excel Discussion (Misc queries) | |||
How to Copy the value of a cell to any given cell | Excel Discussion (Misc queries) | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |