Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
negative numbers as a zero
AFTER CREATING A FORMULAR USING 3 CELLS I NEED TO MAKE SURE THAT ANY NEGATIVE
NUMBER IS SHOWN AND CALCULATED AS ZERO WHICH I KNOW HOW TO DO BUT I NEED TO ALSO BE ABLE TO ENTER A MAXIMUM EG =MAX(C4-D4,0) WILL GIVE ME A ZERO NUMBER BUT HOWE DO I CREAT A MAXIMUM i WANT THE ANSWER NOT TO EXCEED 4300 AND THE MINIMUM TO BE 0 THANKYOU |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
negative numbers as a zero
Hi Garry
Try =MIN(MAX(C4-D4,0),4300) -- Regards Roger Govier "GARRYPRINCE" wrote in message ... AFTER CREATING A FORMULAR USING 3 CELLS I NEED TO MAKE SURE THAT ANY NEGATIVE NUMBER IS SHOWN AND CALCULATED AS ZERO WHICH I KNOW HOW TO DO BUT I NEED TO ALSO BE ABLE TO ENTER A MAXIMUM EG =MAX(C4-D4,0) WILL GIVE ME A ZERO NUMBER BUT HOWE DO I CREAT A MAXIMUM i WANT THE ANSWER NOT TO EXCEED 4300 AND THE MINIMUM TO BE 0 THANKYOU |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
negative numbers as a zero
Try =MEDIAN(0,C4-D4,4300) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=545298 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
negative numbers as a zero
Hi daddylonglegs
Nice solution!! -- Regards Roger Govier "daddylonglegs" wrote in message news:daddylonglegs.28c0lm_1148507701.1732@excelfor um-nospam.com... Try =MEDIAN(0,C4-D4,4300) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=545298 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
negative numbers as a zero
Where you currently have =your_formula, instead use
=min(4300,max(0,your_formula)) Evaluate from the inside out: max(0,xx) puts a floor of 0 on the result; then min(4300,yy) applies a ceiling of 4300. "GARRYPRINCE" wrote: AFTER CREATING A FORMULAR USING 3 CELLS I NEED TO MAKE SURE THAT ANY NEGATIVE NUMBER IS SHOWN AND CALCULATED AS ZERO WHICH I KNOW HOW TO DO BUT I NEED TO ALSO BE ABLE TO ENTER A MAXIMUM EG =MAX(C4-D4,0) WILL GIVE ME A ZERO NUMBER BUT HOWE DO I CREAT A MAXIMUM i WANT THE ANSWER NOT TO EXCEED 4300 AND THE MINIMUM TO BE 0 THANKYOU |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
negative numbers as a zero
thanks so much, works a treat, saved me so much work, buy you a drink one
day ,, thanks again, sure shows the value of this type of site, will certainly visit more now i have found it and hopefully may even be able to pass on the little knowledge i have "bpeltzer" wrote: Where you currently have =your_formula, instead use =min(4300,max(0,your_formula)) Evaluate from the inside out: max(0,xx) puts a floor of 0 on the result; then min(4300,yy) applies a ceiling of 4300. "GARRYPRINCE" wrote: AFTER CREATING A FORMULAR USING 3 CELLS I NEED TO MAKE SURE THAT ANY NEGATIVE NUMBER IS SHOWN AND CALCULATED AS ZERO WHICH I KNOW HOW TO DO BUT I NEED TO ALSO BE ABLE TO ENTER A MAXIMUM EG =MAX(C4-D4,0) WILL GIVE ME A ZERO NUMBER BUT HOWE DO I CREAT A MAXIMUM i WANT THE ANSWER NOT TO EXCEED 4300 AND THE MINIMUM TO BE 0 THANKYOU |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"find and replace" negative numbers | Excel Worksheet Functions | |||
How do I replace negative numbers with zero in Excel. | Excel Worksheet Functions | |||
convert negative numbers to positive numbers and vice versa | Excel Worksheet Functions | |||
Add sequences of positive then negative numbers | Excel Discussion (Misc queries) | |||
How to change a series of positive numbers to negative numbers | Excel Worksheet Functions |